A brief introduction to MySQL stored procedures and transactions

The stored procedure is a series of SQL statements. According to the incoming parameters (or not), through simple calls,
completes more complex functions than a single SQL statement. It is stored on the database server side and only needs After compiling once, you don’t need to compile again if you use it again. Mainly controls the stored process.
A transaction is a series of data change operations as a whole. Once an operation included in the transaction fails or is aborted by the user, the user can control the undoing of all operations in the transaction body and return to the state before the start of the transaction.
The operations in the transaction are a whole, either completed as a whole, or not done at all. This ensures data integrity.
In Mysql, the MyISAM storage engine does not support transactions, but InnoDB does.
Both are very important knowledge in the database.

Stored procedure:

Advantages:

1. Fast execution speed. Especially for more complex logic, it reduces the consumption of network traffic. Another important point is that the stored procedure is only compiled when it is created. There is no need to recompile each time the stored procedure is executed in the future, while general SQL statements are compiled every time they are executed. It is compiled once, so using stored procedures can improve database execution speed. .

2. Improve work efficiency. Writing a program is simple, using the stored procedure calling class, and calling any stored procedure only requires 1-2 lines of code.

3. Standardized program design, easy to upgrade and maintain.

4. Improve system security. You can set that only a certain user has the right to use the specified stored procedure.

Projects with small data volume or that have nothing to do with money can operate normally without stored procedures. The stored procedures of mysql have yet to be actually tested. If it is a formal project, it is recommended that you use SQL Server or Oracle's stored procedures. If you deal with data, the process will be much faster than the program.

Disadvantages:

Part of the program functions are moved into the database, destroying the CVM three-tier structure design

Transaction:

Transaction There can be stored procedures in stored procedures and there can be transactions in stored procedures.

When is it appropriate to use stored procedures?

1. When a business processes multiple tables at the same time, it is more appropriate to use stored procedures.

2. Use stored procedures for complex data processing, such as some report processing.

3. Joint query with multiple conditions and multiple tables, and perform paging processing.

When is it appropriate to use transactions?

Every time a transaction is used, it will take up a certain amount of overhead. Additionally, transactions may lock some table rows. Therefore, unnecessary transactions can lead to performance loss. There is a rule here, only use transactions when the operation requires it. For example, if you are just querying some records from the database, or executing a single query, you don't need an explicit transaction most of the time because the declarations are already encapsulated in the implicit transaction. However, as mentioned earlier, it is very important when updating multiple statements, because transactions can actually speed up the operation. Likewise, if there's a choice between saving a few milliseconds or jeopardizing data integrity, the right answer is to keep the data clean and don't worry about those milliseconds.

In addition, you need to pay attention before using transactions: keep transactions as short as possible. Avoid using SELECT returns within a transaction unless the statement depends on the returned data. If you use a SELECT statement, select only the rows you need, thus not locking too many resources while maintaining the highest possible performance. In the case of architectural word order, remove all SELECT statements from the transaction. This is done because the transaction will lock all operated data rows during processing, which will affect the execution of other concurrent SQL statements.

The above is the difference between MySQL stored procedures and things. I hope it will be helpful to everyone.

Related recommendations:

Detailed introduction to stored procedures

Why use mysql stored procedures? Introduction to mysql stored procedures

Simultaneously operate different database tables, does it support things, mysql solutions

The above is the detailed content of A brief introduction to MySQL stored procedures and transactions. For more information, please follow other related articles on the PHP Chinese website!