MySQL batch update and batch update different values for multiple records

Batch Update

The MySQL UPDATE statement is simple, updating a field of data that is generally written like this:

The code is as follows
UPDATE mytable SET myfield = ' value ' WHERE Other_field = ' other_value ';

If you update the same value in the same field, MySQL is also simple, modify the Where to:

The code is as follows
UPDATE mytable SET myfield = ' value ' WHERE Other_field in (' other_values ');

Note here that ' other_values ' is a comma (,) delimited string, such as: 1,2,3

If you update more than one data to a different value, it may be written by many people:

The code is as follows
foreach ($display _order as $id => $ordinal) { $sql = "UPDATE categories SET Display_order = $ordinal WHERE id = $id"; mysql_query ($sql); }

That is, an update record for a loop. A record update once, this performance is very poor, but also easy to cause congestion.

So can a SQL statement to achieve batch update? MySQL does not provide a straightforward way to implement batch updates, but it can be accomplished with a little bit of skill.

The code is as follows
UPDATE mytable SET myfield = case ID When 1 THEN ' value ' When 2 THEN ' value ' When 3 THEN ' value ' End WHERE ID in (1,2,3)

This is a small trick to implement batch updates using the case.

For example:

The code is as follows
UPDATE Categories SET Display_order = case ID When 1 THEN 3 When 2 THEN 4 When 3 THEN 5 End WHERE ID in (1,2,3)

The meaning of this SQL is to update the Display_order field, if the id=1 display_order value is 3, if id=2 the Display_order value is 4, if id=3 the value of Display_order is 5.

That is, the conditional statement is written together.
The where section here does not affect the execution of the code, but increases the efficiency of SQL execution. Make sure that the SQL statement executes only the number of rows that need to be modified, where only 3 of the data is updated, and the WHERE clause ensures that only 3 rows of data are executed.

If you update multiple values, you need to modify them only slightly:

The code is as follows
UPDATE Categories SET Display_order = case ID When 1 THEN 3 When 2 THEN 4 When 3 THEN 5 End, title = case ID When 1 THEN ' New Title 1 ' When 2 THEN ' New Title 2 ' When 3 THEN ' New Title 3 ' End WHERE ID in (1,2,3)

Here, a MySQL statement has been completed to update more than one record.

But to use in the business, you need to combine the server language, here in PHP, for example, constructs this MySQL statement:

The code is as follows
$display _order = Array ( 1 => 4, 2 => 1, 3 => 2, 4 => 3, 5 => 9, 6 => 5, 7 => 8, 8 => 9 ); $ids = Implode (', ', Array_keys ($display _order)); $sql = "UPDATE categories SET display_order = case ID"; foreach ($display _order as $id => $ordinal) { $sql. = sprintf ("When%d THEN%d", $id, $ordinal); } $sql. = "End WHERE ID in ($ids)"; Echo $sql;

For this example, there are 8 records to update. The code is also easy to understand, have you learned?

Performance analysis

When I use the tens of thousands of records using MySQL batch update, found that the most original batch update found performance is poor, the summary of the online see the following three ways:

1. batch update, one record update once, poor performance

The code is as follows
Update test_tbl set dr= ' 2 ' where id=1;

2.replace into or INSERT INTO ... on duplicate key update

The code is as follows
Replace into TEST_TBL (ID,DR) VALUES (1, ' 2 '), (2, ' 3 '),... (x, ' Y ');

or use

The code is as follows
Insert into TEST_TBL (ID,DR) VALUES (1, ' 2 '), (2, ' 3 '),... (x, ' Y ') on duplicate key update dr=values (DR);

3. Create temporary tables, update temporary tables first, and then update from temporary tables

The code is as follows
Create temporary table tmp (ID int (4) Primary key,dr varchar (50)); 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;

Note: This method requires the user to have create permissions for the temporary table.

The following are the performance test results for the above method update 100,000 data:

Update by article

Real 0m15.557s

User 0m1.684s
SYS 0m1.372s

Replace into

Real 0m1.394s
User 0m0.060s
SYS 0m0.012s

INSERT into on duplicate key update

Real 0m1.474s
User 0m0.052s
SYS 0m0.008s

Create temporary table and update:

Real 0m0.643s
User 0m0.064s
SYS 0m0.004s

As far as the test results were, it was better to use replace into.

The difference between replace into and insert into on duplicate key update is:

Replace into operation is the essence of the duplicate records first delete after the insert, if the updated field is not the whole assembly of the missing field to the default value
Insert INTO is an update-only duplicate record and does not change other fields.