MySQL batch update multiple records (and different values) implementation method

The MySQL UPDATE statement is simple, updating one field of multiple data with the same value, which is generally the case:UPDATE table_name SET field = ' value ' WHERE condition;To update multiple data to different values, you can:

foreach ($display _orderas$id$ordinal) {     $sql$ Ordinal$id";      mysql_query ($sql);}

such a strip, although poor performance, but also easy to block. You can also use some SQL tips:

UPDATE table_name       Case ID          1 Then ' value '        2 and ' value '        3 Then ' value '    ENDWHERE ID in ( (a);

However, the performance is still poor in the case of large records, and we consider using:

REPLACE into table_name (id,data) VALUES (1, ' 2 '), (2, ' 3 '),... (x, ' Y '); INSERT into table_name (IDKEY UPDATE data=values (data);

REPLACE into The essence of the operation is to DELETE the duplicate record first after the INSERT, if the updated field does not set the missing field to the default value; INSERT into only duplicate records are updated, and no other fields are changed.

If this performance is still insufficient, we can also speed up by creating a temporary table:

key, Dr varchar),INSERT INTO TMP values  (0, ' gone '), (1, ' xx '),... (M, ' yy '); update test_tbl, TMP set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

Create temporary tables, update temporary tables, and update from temporary tables.

MySQL batch update multiple records (and different values) implementation method