[MySQL] update batch update of large amounts of data

Article directory

    • Update fields, multiple operations
      • To update a certain field of a piece of data, it is usually written like this:
      • Updating the same field to the same value is also very simple in mysql. Just modify where:
      • To update multiple pieces of data to different values, many people may write like this:
    • So can batch updates be implemented with one SQL statement?
      • use case when then
      • If you update multiple values, you only need to modify it slightly:
      • Taking PHP as an example, construct the mysql statement:
    • Performance analysis
      • 1. Batch update, one record is updated once, the performance is very poor
      • 2.replace into or insert into …on duplicate key update
      • 3. Create a temporary table, update the temporary table first, and then update from the temporary table
    • The following are the performance test results of updating 100,000 pieces of data using the above method:
      • Judging from the test results, the performance of using replace into was better at the time of the test.
    • PHP splicing converts two-dimensional arrays into CASE WHEN THEN batch update conditions

Update fields, multiple operations

The mysql update statement is very simple,

Update a certain field of a piece of data, usually written like this:

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

Updating the same field to the same value is also very simple in mysql, just 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

Update multiple pieces of data to different values, many people may write like this:

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

That is, looping through the update records one by one. One record is updated once, which has poor performance and can easily cause blocking.

So can batch updates be implemented with one SQL statement?

MySQL does not provide a direct method to implement batch updates, but it can be achieved with a few tricks.

Use case when then

case when then 

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 little trick of case when is used here to achieve batch updates.
for 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)

The meaning of this sql is to update the display_order field.
If id=1, the value of display_order is 3,
If id=2, the value of display_order is 4,
If id=3 then the value of display_order is 5.
That is, conditional statements are written together.
The where part here does not affect the execution of the code, but will improve the efficiency of sql execution. Ensure that the sql statement only executes the number of rows that need to be modified. Only 3 rows of data are updated here, and the where clause ensures that only 3 rows of data are executed.

If you update multiple values, you only need to make slight modifications:

UPDATE categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title=CASEid
        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.

But to use it in business, it needs to be combined with server-side language

Take PHP as an example to construct the 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 8 records to update. The code is also easy to understand. Have you learned it?

Performance analysis

When I used mysql to batch update tens of thousands of records, I found that the performance of the original batch update was very poor. To summarize what I saw online, there are three methods:

1. Batch update, one record is updated once, the performance is very poor

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, first update the temporary table, and then update from the temporary table

code show as below

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 on the temporary table.

The following are the performance test results of updating 100,000 pieces of data using the above method:

Update item by item

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

Judging from the test results, the performance of using replace into was better at the time of the test.

  • The difference between replace into and insert into on duplicate key update is:
  • The essence of the replace into operation is to delete the duplicate records first and then insert them. If the updated fields are incomplete, the missing fields will be set to default values.
  • insert into only updates duplicate records and does not change other fields.

PHP splicing converts two-dimensional arrays into CASE WHEN THEN batch update conditions

/**
 * Convert a two-dimensional array into batch update conditions for CASE WHEN THEN
 * @param $data array two-dimensional array
 * @param $field string column name
 * @return string sql statement
 */
private function parseUpdate($data, $field,$table)
{<!-- -->
    $sql = " update {<!-- -->$table} set ";
    $keys = array_keys(current($data));
    foreach ($keys as $column) {<!-- -->
        $sql .= sprintf("`%s` = CASE `%s` \\
", $column, $field);
        foreach ($data as $line) {<!-- -->
            $sql .= sprintf("WHEN '%s' THEN '%s' \\
", $line[$field], $line[$column]);
        }
        $sql .= "END,";
    }
    $fanhui = implode(',',array_column($data,'id'));
    return rtrim($sql, ',')." where id in ({<!-- -->$fanhui})";
}

Reference link: https://www.cnblogs.com/yszr/p/11475832.html