The MySQL stored procedures has become one of the preferred tools for complex data calculations, playing an important role. But nevertheless, MySQL stored procedures also cause some inconvenience. As an example, many of its functions are problematic, difficult to delete or migrate; and there are some databases with MySQL stored procedures pretty weak. These problems sometimes seriously affect the efficiency of database developers.
Photo credits: canjoena
MySQL stored procedures can be beneficial
MySQL stored procedures promote agility by helping with speed. They also facilitate code reuse. and encapsulation (two pillars of software development). At the same time, they can become a security ally, since they have the ability to protect against SQL injection attacks and can grant and revoke permissions on a stored procedure individually.
But nevertheless, to avoid its inconveniences, you need to be clear about where and how to use MySQL stored procedures:
- Access control. Can be used to allow arbitrary SQL clauses in development and UAT environmentsand ban them in production environments and systems. Thus, any clause that reaches the system or production could be reviewed by the developers and DBA.
- Scalable logic. MySQL stored procedures are also useful in situations where the application server cannot be scaled (neither in cloud environments nor on premises). If there is enough capacity on the backend of the database, business logic could be carried over there.
- Data aggregation. Another situation where can be used when an application needs data from multiple queries or views to be transposed or aggregated. Having this type of procedure makes it possible to download from the application to the database.
In summary, it could be argued that stored Procedures MySQL is excellent for speeding up certain database operations. In practice, it should only be noted that, at an architectural level, It would be better to leave all the logic in the business layer of the application and use MySQL stored procedures specifically to boost performance. when benchmarking indicates warranted.
Situations in which to avoid resorting to MySQL stored procedures
There are times when it is preferable not to use stored procedures. MySQL. These are the following:
- When viewed as the only defense mechanism against SQL injection.
- If it is intended to extend its use beyond data collection until entering the territory of a complex logic. Something to avoid because it could be considered business logic and, therefore, would have to stay somewhere else.
- When databases are the only place that contains storage processes, these should be treated in the same way as the C source code # about Java.
There are probably more benefits than disadvantages to using MySQL stored procedures in big data projects., But when you don't know for sure how far you can go with them, it is better not to risk yourself to avoid consequences.
