MySQL multiline insert _ MySQL

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.