MySQL fast insert/update a large number of records

Source: Internet
Author: User
In the recent project, you need to copy several dump files (text format, 1 ~ 2 GB) records are imported into the mysql database. Due to the large data volume (millions or tens of millions of records ),

In the recent project, you need to copy several dump files (text format, 1 ~ 2 GB) records are imported into the mysql database. Due to the large data volume (millions or tens of millions of records ),

In the recent project, you need to copy several dump files (text format, 1 ~ 2 GB) records are imported into the mysql database. Due to the large data volume (millions or tens of millions of records), there are insert records and update records, the insert/update speed is slow.

At the beginning, a single SQL statement was handed over to mysql for execution. It would take up to one and a half hours to test it. So I tried to improve it.

(1) Insert multiple records at a time using SQL statements. Instance:

Insert into tbl_name (a, B, c) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9 );

For more information, see SQL Syntax:

In the program, concatenate SQL statements cyclically and then discard them to myql_query.

Note that the longer the SQL statement, the better. If it is too long, it may exceed the mysql limit (according to my colleagues, the mysql limit is 63 MB ).

In my actual test, I restricted the SQL length to 10 MB:

For (...)
{
M_strSQL + = ...;

If (m_strSQL.length ()> 10485760) // 10 M
{
M_pMysqlStatement-> execute (m_strSQL.substr (0, m_strSQL.length ()-1 ));
M_strSQL = "insert into location (locationid, name, alternatename) VALUES ";
}

}

Test results:

8267787 records, which took 754 seconds (databases on different machines in the LAN), basically met the requirements.

(2) For update records, because the update syntax does not support updating multiple records at a time, you cannot use VALUES (, 3) similar to "insert into tbl_name (a, B, c ), (, 6), (, 9); "format to update. Only Multiple SQL statements can be merged for updates:

Update location set versions ages = 'zh 'where locationid = '20160301'; update location set postalcode = '20160301' where locationid = '20160301 ';

However, the problem occurs:

(I) multi-query support must be enabled when connecting to the database. Multiple SQL statements can be executed at a time. See:

(II) you also need to handle the resultset release issue, otherwise mysql will report the error "Commands out of sync; you can't run this command now"

(III) for the update statement, although no resultset is returned, it still needs to be released. But for unknown reasons (maybe the SQL statement is too long ?), Releasing the resultset is time-consuming, and the final result is not worth the candle.

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.