How to implement batch update data in MySQL in PHP

This article to share the content is about how PHP batch update mysql data, the content is very detailed, the need for friends can refer to, hope can help you.

In this business involves the updating of two data sheets, then people will not think very simple, immediately on the code

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

Where the data table name is Newhouse_clicks, there are four fields, primary key Id,type (Type-integer) field, clicks (click-int) field, Update_time (integer) field
Is this really no problem? For example, batch update all the real estate under the current city, such as Beijing, to make an analogy 1000 data, then in the business is not to write this

$data = Array (id=>1,id=>2,.......... id=>1000);//Omit data foreach ($data as $key = = $value) {    $sql = "Update Newhouse_clicks set Clicks=6,type=1,update_time=time () where is= $value [' id '] ";}

This involves more than one table * 1000 data, then there will be a great delay it?

As a result, she does write this to cause the server to timeout!

If you are familiar with Java and other languages should know that Java will provide the function of bulk update SQL, then as the world's best language PHP can do it? The answer is can drop!

So let's take a look at the SQL statements for bulk updates.

UPDATE newhouse_clicks    SET clicks = case ID when 1 and then 2 then the 3 then the    endwher E ID in (A/C)

Don't be impatient, let's explain the meaning of this SQL statement:
Update the clicks field in the Newhouse_clicks data table when the id=1 is set to a value of 1000, when id=2 is set to a value of 2000, and when Id=3 is set to a value of 3000

So updating multiple fields can not be done? Of course, post code:

UPDATE newhouse_clicks    SET clicks = case ID when 1 and then 2 then the 3 then the    end,
  type = case ID when        1 then 1 if 2 then 6 when 3 then        8    endwhere ID in (All-in-a-

The meaning of this SQL statement is to update the clicks field in the Newhouse_clicks datasheet when the id=1 is set to a value of 1000, when id=2 is set to a value of 2000, when Id=3 is set to a value of 3000, and the Type field is updated when id= 1 O'Clock Update the Type field to 1 when id=2 is updated when the Type field is 6, and when Id=3 is updated, the Type field is 8.
So, the best language in the world PHP can not spell out the SQL?

The data format returned by the query database $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) {    $sq L. = sprintf ("When%d then%d", $key, $value);} $sql. = "END WHERE ID in ($ids)"; Echo $sql;

Let's see if this is the same as the SQL statement above us!

So, is the data of ours more complicated than this? Sure, look at the problem, we usually take out the data in the database format is not all the same.

The data format returned by the query database $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 about this situation?

<?php//query the data format returned by the database $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 ());    Get all ids $newhouse _clicks_keys = Array_keys ($newhouse _clicks);    Splicing batch update SQL statement $sql = "update Newhouse_clicks SET";        Synthetic SQL statement 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,";     }//Put the last one, remove $sql = substr ($sql, 0, Strrpos ($sql, ', '));    Merge all IDs $ids = Implode (', ', $newhouse _clicks_keys);    Splicing sql $sql. = "WHERE ID in ({$ids})"; echo $sql; 

In fact, write such a large pile, is not to assemble into MySQL statement ah.

Done! Speed is not as smooth as silk!

Older programmers, especially beginners, can easily get into a misunderstanding by taking the data in SQL in the For loop. This writing causes a problem, that is, serious obstruction, in real life there is an example:

For example, you work on the 12 floor, this is the courier to call you to go downstairs to pick up the courier (a total of 12), you go to pick up the courier there are two ways:

1. Get the first express, run back to 12 layers, put it, then go to pick up the next express, put 12 layers after the next to remove a courier.

2. Get all the express delivery to 12 layers at once.

We will certainly choose the second solution, no one will be silly not to pull a few to run 12 times to get the courier.

Computer is the above principle, in the For loop to the resources to fetch data, is similar to the first scenario. Bulk fetching of data is similar to the second scenario. (PS: Do not think that the data in MySQL to have this problem, Redis will also

There is such a problem, otherwise how can have pipeline to take batch data, this point in the interview when I was often in person to ask questions, will definitely become a measure of the level of technical standards.