About MySQL compound-statement SQL

In the MySQL compound-statement SQL background, sending multiple statements to MySQL can reduce the number of network interactions. In case of transactions, the lifetime of transactions on a single thread can be shortened. Writing business logic into a stored procedure is a form, but considering this, binding the business logic to the server is not the best choice. Some people mentioned the hope that MySQL can support compound-statement SQL similar to Oracle. In fact, MySQL is supported in MySQL 5.0. When the connection parameter of c api is added to mysql_real_connect, CLIENT_MULTI_STATEMENTS can be separated by semicolons and multiple SQL statements can be sent to the Server together. In fact, in the client that comes with mysql, this bit has been set for the connection parameter. Mysql_real_connect (& mysql, host, user, password, database, opt_mysql_port, opt_mysql_unix_port, connect_flag | CLIENT_MULTI_STATEMENTS) MySQL client usage, but even so, users enter multiple SQL statements differentiated by semicolons on the client, in fact, it is sent in order after the client is split. That is to say, a statement like this is mysql> insert into test123 values (1, 'A'); insert into test123 values (2, 'A'); in fact, the first insert is completed first, send the second response. The reason is that the mysql client uses ";" as the statement terminator when parsing user input. Redefines the statement Terminator. In fact, to implement the syntax of multiple statements, we used it when creating the stored procedure. Delimiter; begin; insert into test123 values (1, 'aaaaa'); insert into test123 values (1, 'aaaaa'); insert into test123 values (1, 'aaaaa'); commit; in this way, the second line is sent to the server. It indicates that the compound-statement method is about 20% higher than the single-thread performance under the stress of a Single-thread test in the preceding scenario, because the number of network interactions is reduced. Under the multi-thread pressure, whether or not the performance is improved depends on the mutex between the transactions of each thread. The benefit of performance improvement is that the transaction lifetime of a single thread is shortened.