How to implement batch update of data in mysql in PHP

The content of this article is about how PHP updates data in MYSQL in batches. The content is very detailed. Friends in need can refer to it. I hope it can help you.

This business involves updating two data tables, so you will think that it is very simple, and you will immediately upload the code

$sql = "update newhouse_clicks set clicks=6,type=1,update_time=time() where is=$value['id']";

The data table is named newhouse_clicks and has four fields. Primary key id, type (type - integer) field, clicks (clicks - integer) field, update_time (integer) field
Is there really no problem with this? For example, if you want to batch update all real estate properties in the current city, such as Beijing, for example, if there are 1,000 pieces of data, then in the business, should you write like this

$data = array(id=>1,id=>2,..........id=>1000);//省略数据
foreach($data as $key=>$value) {
    $sql = "update newhouse_clicks set clicks=6,type=1,update_time=time() where is=$value['id']";
}

This will involve multiple tables * 1,000 pieces of data , then will there be a big delay?

The result is yes, her writing like this did cause the server to time out!

If you are familiar with languages ​​​​such as JAVA, you should know that JAVA provides the function of batch updating SQL internally. So, as the best language in the world, can PHP do it? The answer is yes!

Then let’s learn the sql statement for batch update.

UPDATE newhouse_clicks
    SET clicks = CASE id
        WHEN 1 THEN 1000
        WHEN 2 THEN 2000
        WHEN 3 THEN 3000
    END
WHERE id IN (1,2,3)

Be patient, let’s explain the meaning of this sql statement in detail:
Update the clicks field in the newhouse_clicks data table. When id=1, the value is set to 1000, and when id=2, it is set. The value is 2000. When id=3, set the value to 3000

. So is it possible to update multiple fields? Of course, you can, post the code:

UPDATE newhouse_clicks
    SET clicks = CASE id
        WHEN 1 THEN 1000
        WHEN 2 THEN 2000
        WHEN 3 THEN 3000
    END,
    type = CASE id
        WHEN 1 THEN 1
        WHEN 2 THEN 6
        WHEN 3 THEN 8
    END
WHERE id IN (1,2,3)

The meaning of this SQL statement is to update the clicks field in the newhouse_clicks data table. When id=1, the value is set to 1000, when id=2, the value is set to 2000, when When id=3, set the value to 3000 and update the type field. When id=1, update the type field to 1. When id=2, update the type field to 6. When id=3, update the type field to 8.
So, can’t PHP, the best language in the world, be able to spell out sql?

//查询数据库返回的数据格式
$newhouse_clicks = array(
=> 2,
=> 3,
=> 8,
=> 9,
  );
$ids = implode(',', array_keys($newhouse_clicks ));
$sql = "UPDATE newhouse_clicks SET clicks = CASE id ";
foreach ( $newhouse_clicks as $key => $value) {
    $sql .= sprintf("WHEN %d THEN %d ", $key, $value);
}
$sql .= "END WHERE id IN ($ids)";
echo $sql;

Let’s check if it is the same as our sql statement above!

So, is our real data more complicated than this? Sure, depending on the question, is the format of the data we usually take out from the database like this?

//查询数据库返回的数据格式
$newhouse_clicks = array(
=> array('clicks'=>1,'type'=>1,'update_time'=>time()),
=> array('clicks'=>2,'type'=>2,'update_time'=>time()),
=> array('clicks'=>3,'type'=>3,'update_time'=>time()),
=> array('clicks'=>4,'type'=>4,'update_time'=>time()),
);
?>

So, what to do in this situation?

<?php
//查询数据库返回的数据格式
$newhouse_clicks = array(
=> array(&#39;clicks&#39;=>1,&#39;type&#39;=>1,&#39;update_time&#39;=>time()),
=> array(&#39;clicks&#39;=>2,&#39;type&#39;=>2,&#39;update_time&#39;=>time()),
=> array(&#39;clicks&#39;=>3,&#39;type&#39;=>3,&#39;update_time&#39;=>time()),
=> array(&#39;clicks&#39;=>4,&#39;type&#39;=>4,&#39;update_time&#39;=>time()),
);
    //获取所有的id
    $newhouse_clicks_keys = array_keys($newhouse_clicks);
    //拼接批量更新sql语句
    $sql = "UPDATE newhouse_clicks SET ";
    //合成sql语句
    foreach ($newhouse_clicks[1] as $key => $value) {
        $sql .= "{$key} = CASE id ";
        foreach ($newhouse_clicks as $newhouse_clicks_key=>$newhouse_clicks_value) {
 
            $sql .= sprintf("WHEN %d THEN %d ", $newhouse_clicks_key, $newhouse_clicks_value[$key]);
        }
        $sql .= "END, ";
    }
    //把最后一个,去掉
    $sql = substr($sql, 0, strrpos($sql,&#39;,&#39;)); 
    //合并所有id
    $ids = implode(&#39;,&#39;, $newhouse_clicks_keys);
    //拼接sql
    $sql .= " WHERE ID IN ({$ids})";
    echo $sql;

Actually, I wrote so many words just to assemble them into mysql statements.

Done! Isn’t the speed as smooth as silk!

                                                                                                                                                                                                            It is easy for many programmers, especially beginners, to fall into a misunderstanding and put the data fetching in SQL into a for loop. Writing like this leads to a problem, which is serious congestion. There is such an example in real life:

For example, you are working on the 12th floor, and the courier calls you and asks you to go downstairs to pick up the express. (12 pieces in total), you have two ways to pick up the express:

1. Get the first express, run back to the 12th floor, put it away, then go and pick up the next express, put it away 12 After layering, go on to pick up a piece of express delivery.

2. Take all the express delivery to the 12th floor at one time.

                                                                                                                                                                                                                                             out out out out out out of 20-year-old going out -  but everyone will definitely choose the second option.

The computer is based on the above principle. Retrieving data from resources in a for loop is similar to the first solution. Fetching data in batches is similar to the second solution. (PS: Don’t think that there is such a problem when fetching data in mysql. Redis also

will have this kind of problem. Otherwise, how can there be a pipeline to fetch batch data? This is something I often do during interviews. When asked in an interview question, it will definitely become the standard for measuring a person's technical level)

Related recommendations:

What is the principle of mvc in php? Introduction to the PHP mvc implementation principle (with code)

How to upload files to Qiniu in PHP method code

The above is the detailed content of How to implement batch update of data in mysql in PHP. For more information, please follow other related articles on the PHP Chinese website!