In my entire small career as a web developer, I used the database only to insert, modify, delete, and query information. Well maximum I used nested selects, operator LIKE. I made all other manipulations in the backend code.

But MySQL has built-in functions, the ability to write your own procedures and functions .

Dear developers, please explain to me:

1) Where, in what situations are at least the built-in functions applicable, and why should they be used?

2) Is this my competence (backend developer)?

  • It is considered that stored procedures run faster (at least, you can reduce the number of RTTs). In practice, I can not say that they are strongly needed. Sometimes it is necessary to perform some operation on the value in the table (eg check each record for two regular sessions at once), and in this case it will be easier to go through my function for each one, but personally I usually approach these problems from the other side. - etki
  • 2
    Functions in the database can, for example, allow organizing additional data protection when the application does not have access to certain tables at all, and all work is reduced to calls to procedures. In addition, there are several applications in my old job that have both client Win applications and a web interface and at some time there was terminal access via RS232 ports. This is all written in different languages ​​as independent modules. Here there is almost all business logic in the database, so as not to repeat it in different versions on different platforms, i.e. essentially the backend part is the DB - Mike

1 answer 1

As already written in the comments, they work faster and allow you to organize a rather cunning logic of working on data with your scripts. There is no ubiquitous practice (sometimes justified) when DBMS engineers describe all database operations in the form of stored procedures and provide their backend to developers as an API. Accordingly, the backend does not even fit into the database, does not answer for the correctness of each individual request, and generally focuses on other tasks, being engaged in the business logic of the application and almost without thinking about the data storage layer. So the developer knows that in order to obtain data on sales for the month, he pulls procedure X , and to initiate the closure of the financial period, he pulls procedure Y.

At the same time, stored procedures (in particular, MySql ) are very difficult to debug, plus they still contain part of business logic, i.e. what happens in your program becomes far from obvious, based on the application code. The very hell begins if a part of the same aspect of business logic is displayed both in the code and in the stored procedure, which can sometimes be observed in projects with a large legacy code. When some developer wrote stored procedures, then left the command, none of the other team members could write them, and later they continued to write the project, not using procedures, putting their logic into the applications, but not doing the normal code. Time passes, a new team is recruited for the project, and it needs to duplicate changes in business logic in several different places of the application plus in the stored procedure.

At present, backend developers, as a rule, are seated with various ORMs and this often ends their interaction with the base. If you have a separate person on the project who is ready to write and maintain stored procedures, providing you with only the described interface - with proper organization this can be beneficial for the development. Trying to do nothing to start using stored procedures in a combat project that previously did without them, I would not advise.