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 _order as $id + $ordinal) {     $sql = "UPDATE categories SET Display_order = $ordinal WHERE id = $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     SET field = case ID if 1 Then ' value ' when        2 Then ' value ' is        3 Then ' value '    ENDW Here ID in (A/n);

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 (id,data) VALUES (1, ' 2 '), (2, ' 3 '),... (x, ' Y ') On DUPLICATE KEY UPDATE data=values (data);

REPLACE intoThe 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:

Create temporary table tmp (ID int (4) primary 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