Shulou(Shulou.com)05/31 Report--

Today, I would like to share with you what are the relevant knowledge points about the shortcomings of mysql stored procedures, which are detailed in content and clear in logic. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article.

The disadvantages of mysql stored procedures: 1, the construction 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, because stored procedures bind the application to the database, so using stored procedures to encapsulate business logic will limit the portability of applications.

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

Introduction to stored procedures:

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

1, the stored procedure is compiled only when it is created, and there is no need to recompile every time the stored procedure is executed, while the general SQL statement is compiled every time it is executed, so the use of stored procedure can improve the execution speed of the database.

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

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

4. High security, it can be set that only a certain user has the right to use the specified stored procedure.

Advantages and disadvantages of MySQL stored procedures

Advantages of MySQL stored procedures

Typically, stored procedures help improve the performance of your application. When created and the stored procedure is compiled, it is stored in the database. However, the stored procedures implemented by MySQL are slightly different. MySQL stored procedures are compiled on demand. After compiling the stored procedure, MySQL places it in the cache. MySQL maintains its own stored procedure cache for each connection. If the application uses the stored procedure multiple times in a single connection, the compiled version is used, 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 programs are reusable and transparent to any application. Stored procedures expose database interfaces to all applications so that developers do not have to develop features that are already supported in stored procedures.

The stored program is secure. 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 the database.

Disadvantages of MySQL stored procedures

If you use a large number of stored procedures, the memory usage of each connection that uses these stored procedures will be greatly increased. In addition, if you overuse a large number of logical operations in stored procedures, CPU usage increases 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: because stored procedures bind the application to the database, using stored procedures to encapsulate business logic limits the portability of the application.

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

These are all the contents of the article "what are the shortcomings of mysql stored procedures?" Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.