mysql batch update and batch update multiple records of different value implementation methods

Batch update

mysql update statement is very simple, update 1 data a certain field, 1 like this:

UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';

If the same 1 field is updated to the same 1 value, mysql is also very simple. You can modify where:

 UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');
 

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

So if you update multiple data to different values, a lot of people might write something like this:

 
foreach ($display_order as $id => $ordinal) { 
    $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id"; 
    mysql_query($sql); 
}

That is, the update record of loop 1 and 1. One record update1 times, which is poor performance and easy to block.

Can you do a batch update with one sql statement? mysql does not provide a direct way to implement bulk updates, but it can be done with a bit of finesse.

 
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)

The case when trick is used to implement bulk updates.
Here's an example:

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)

Update the display_order field so that display_order has a value of 3 if id=1, display_order has a value of 4 if id=2, and display_order has a value of 5 if id=3.
That is, the conditional is written at 1.
The where section here does not affect the execution of the code, but it does improve the efficiency of sql execution. Make sure that the sql statement executes only the number of rows that need to be modified, where there are only three data updates, and the where clause ensures that only three rows of data are executed.

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

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)

At this point, one mysql statement has been completed to update multiple records.
However, in order to apply it in business, we need to combine the server-side language. Here, php is taken as an example to construct this mysql statement:

$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;

In this example, there are eight records to update. The code is also easy to understand, have you learned it

Performance analysis

When I used tens of thousands of records to use mysql batch update, I found that the performance of update was poor when I used the original batch update. There are three ways to summarize what I have seen online:

1. Batch update, 1 record update1, poor performance

update test_tbl set dr='2' where id=1;

2.replace into or insert into... on duplicate key update

replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');

Or use

insert into test_tbl (id,dr) values  (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);

3. Create a temporary table, update the temporary table first, and then update from the temporary table

The code is copied 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 is the performance test results of update 100,000 data from the above method:

Detailed update

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 are concerned, replace into was used to perform better.

The difference between replace into and insert into on duplicate key update is:
The replace into operation is essentially an delete and insert operation for duplicate records. If the updated fields are not all set the missing fields to the default values
insert into is an update duplicate record that does not change other fields.