MySQL fast insert/update a large number 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 ),

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.