What are the disadvantages of mysql stored procedures

Disadvantages of mysql stored procedures: 1. The structure of stored procedures makes it more difficult to develop stored procedures with complex business logic; 2. It is difficult to debug stored procedures; 3. It is not easy to develop and maintain stored procedures; 4. Since stored procedures bind the application to the database, using stored procedures to encapsulate business logic will limit the portability of the application.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Introduction to stored procedures:

Stored Procedure (Stored Procedure) is a set of SQL statements that are used to complete specific functions. They are compiled and stored in the database. The user executes a stored procedure by specifying its name and giving parameters (if the stored procedure has parameters).

1. Stored procedures are only compiled when they are created. There is no need to recompile each time the stored procedure is executed in the future. Generally, SQL statements are compiled once every time they are executed, so using stored procedures can improve the execution speed of the database. .

2. When performing complex operations on the database (such as Update, Insert, Select, Delete on multiple tables), this complex operation can be encapsulated in a stored procedure and combined with the transaction processing provided by the database. use.

3. Stored procedures can be reused, which can reduce the workload of database developers.

4. High security, you can set that only a certain user has the right to use the specified stored process.

Advantages and disadvantages of MySQL stored procedures

Advantages of MySQL stored procedures

• Usually stored procedures help improve application performance. When created, the stored procedure is compiled and stored in the database. However, MySQL implements stored procedures slightly differently. MySQL stored procedures are compiled on demand. After compiling the stored procedure, MySQL places it in the cache. MySQL maintains its own cache of stored procedures for each connection. If the application uses the stored procedure multiple times in a single connection, use the compiled version, otherwise the stored procedure works like a query.

• Stored procedures help reduce traffic between the application and the database server because the application does not have to send multiple lengthy SQL statements, but only the name and parameters of the stored procedure .

• Stored procedures are reusable and transparent to any application. Stored procedures expose the database interface to all applications so that developers do not have to develop functionality that is already supported in stored procedures.

• Stored programs are safe. The database administrator can grant appropriate permissions to applications that access stored procedures in the database without providing any permissions to the underlying database tables.

In addition to these advantages, stored procedures have their own disadvantages that should be noted before using them in a database.

Disadvantages of MySQL stored procedures

• If you use a large number of stored procedures, the memory usage of each connection using these stored procedures will greatly increase. Also, if you overuse a lot of logical operations in your stored procedures, the CPU usage will also increase because the database server is not designed for logical operations.

• The construction of stored procedures makes it more difficult to develop stored procedures with complex business logic.

• It is difficult to debug stored procedures. Only a few database management systems allow you to debug stored procedures. Unfortunately, MySQL does not provide the ability to debug stored procedures.

• Developing and maintaining stored procedures is not easy.

• Poor portability: Since stored procedures bind the application to the database, using stored procedures to encapsulate business logic will limit the portability of the application.
  

• Recompilation problem, because the back-end code is compiled before running, if the object with the reference relationship changes, the affected stored procedures and packages will need to be recompiled Compile (but it can also be set to automatically compile at runtime).

[Related recommendations: mysql video tutorial]

The above is the detailed content of What are the disadvantages of mysql stored procedures. For more information, please follow other related articles on the PHP Chinese website!