Tips for batch data update in mysql

Sometimes you need to manually repair or update data during development. Generally, you can write a main function in your project to read or modify the data, however, you need to connect to the Internet from the local device during execution,

If the data volume is large. If the number is hundreds of thousands or more, the performance will be very poor. At this time, it will be much better to write SQL statements in the database for direct execution.

In the innodb engine of the mysql database, General Data addition, deletion, and modification all have built-in transactions, and there is a default timeout limit, which seems to be 50 s, if the data volume is large and many connection queries time out,

In this case, you may want to create a temporary table and index the temporary table, and then add or modify the data to join the temporary table. The performance will be much faster at this time. For example:

Drop temporary table if exists tmp;

Create temporary table tmp
Select B. UserId, B. field1, B. field2, B. field3 from
(Select Max (AutoId) as AutoId
From T_Table2 where UserId
Group by UserId) a left join T_Table1 B on a. AutoId = B. AutoId;
Alter table tmp add index 'index _ userid' ('userid' ASC );

Insert into T_Table3 ('userid', 'field1', 'field2', 'field3', 'createtime', 'createip', 'modifytime', 'modifyip ')
Select a. UserId, a. field1, a. field2, a. field3, now (), '192. 0.0.1 ', now (), '192. 0.0.1'
From tmp;

Drop temporary table tmp;