MySQL multiline insert _ MySQL

MySQL multi-row insert

Because mysql autocommit is enabled by default, and many production environments are set to write disks when the transaction is committed, the io overhead produced by the commit is very large. In a busy oltp system, this may be a major performance bottleneck. Therefore, it is very important to reduce the number of submissions. try to use batch submission instead of a single commit.

Mysql's insert statement can support multiple rows at a time. This method is not supported in other databases.

For example, Oracle.

Before the test starts

Mysql> show status like '% commit % ';
+ ---------------- + ------- +
| Variable_name | Value |
+ ---------------- + ------- +
| Com_commit | 0 |
| Com_xa_commit | 0 |
| Handler_commit | 0 |
+ ---------------- + ------- +
3 rows in set (0.00 sec)

Use multi-row insert.
Mysql> insert into t1 ()
-> Values (1 ),
-> (2 );
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

The database is only submitted once.
Mysql> show status like '% commit % ';
+ ---------------- + ------- +
| Variable_name | Value |
+ ---------------- + ------- +
| Com_commit | 0 |
| Com_xa_commit | 0 |
| Handler_commit | 1 |
+ ---------------- + ------- +
3 rows in set (0.00 sec)

We can see that batch insert can be optimized in this way.

SQL> insert into t1 ()
2 values (1 ),
3 (2 );

Insert into t1 ()
Values (1 ),
(2)

ORA-00933: SQL command not properly ended

Oracle databases do not support this direct insert multi-row method.