PHP batch update

This article mainly introduces the batch update of PHP, which has certain reference value. Now I share it with you. Friends in need can refer to it

Mysql update statement is very simple. Update a certain piece of data. A field is generally written like this:

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

If you update the same field to the same value, mysql is also very simple, just modify where:

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

Note here,other_valuesis a comma-separated string, such as: 1,2,3

1 Conventional solution
If you 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); }

is to loop through the update records one by one.

One recordupdateonce, which has poor performance and can easily cause blocking.

2 Efficient Solution
So can batch updates be implemented with one SQL statement?

2.1CASE WHEN
mysql does not provide a direct method to implement batch updates, but it can be achieved with some tricks.

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 trickcase whenis 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 thedisplay_orderfield:

If id=1, the value of display_order is 3 ,
If id=2, the value of display_order is 4,
If id=3, the value of display_order is 5.
is to write the conditional statements together.

The where part here does not affect the execution of the code, but it will improve the efficiency of sql execution.

Ensure that the sql statement only executes the number of rows that need to be modified. There are only 3 rows of data to be updated, and the where clause ensures that only 3 rows of data are executed.

3.2 Updating multiple values
If you want to 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 = 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, you have completed one mysql statement to update multiple records.

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

3.3 Encapsulate it into a PHP function
In PHP, we encapsulate this function into a function and call it directly later.

To improve usability, we consider handling more comprehensive situations.

The following data needs to be updated. We need to update the contents of theposttable based on theidandparent_idfields.

Among them, the value ofidwill change, and the value ofparent_idwill be the same.

$data = [ ['id' => 1, 'parent_id' => 100, 'title' => 'A', 'sort' => 1], ['id' => 2, 'parent_id' => 100, 'title' => 'A', 'sort' => 3], ['id' => 3, 'parent_id' => 100, 'title' => 'A', 'sort' => 5], ['id' => 4, 'parent_id' => 100, 'title' => 'B', 'sort' => 7], ['id' => 5, 'parent_id' => 101, 'title' => 'A', 'sort' => 9], ];

For example, we want the records withparent_idto be 100 andtitleto be A to be updated in batches based on different IDs:

echo batchUpdate($data, 'id', ['parent_id' => 100, 'title' => 'A']);

Among them, batchUpdate( ) The PHP code implemented is as follows:

/** * 批量更新函数 * @param $data array 待更新的数据，二维数组格式 * @param array $params array 值相同的条件，键值对应的一维数组 * @param string $field string 值不同的条件，默认为id * @return bool|string */ function batchUpdate($data, $field, $params = []) { if (!is_array($data) || !$field || !is_array($params)) { return false; } $updates = parseUpdate($data, $field); $where = parseParams($params); // 获取所有键名为$field列的值，值两边加上单引号，保存在$fields数组中 // array_column()函数需要PHP5.5.0+，如果小于这个版本，可以自己实现， // 参考地址：http://php.net/manual/zh/function.array-column.php#118831 $fields = array_column($data, $field); $fields = implode(',', array_map(function($value) { return "'".$value."'"; }, $fields)); $sql = sprintf("UPDATE `%s` SET %s WHERE `%s` IN (%s) %s", 'post', $updates, $field, $fields, $where); return $sql; } /** * 将二维数组转换成CASE WHEN THEN的批量更新条件 * @param $data array 二维数组 * @param $field string 列名 * @return string sql语句 */ function parseUpdate($data, $field) { $sql = ''; $keys = array_keys(current($data)); foreach ($keys as $column) { $sql .= sprintf("`%s` = CASE `%s` \n", $column, $field); foreach ($data as $line) { $sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], $line[$column]); } $sql .= "END,"; } return rtrim($sql, ','); } /** * 解析where条件 * @param $params * @return array|string */ function parseParams($params) { $where = []; foreach ($params as $key => $value) { $where[] = sprintf("`%s` = '%s'", $key, $value); } return $where ? ' AND ' . implode(' AND ', $where) : ''; }

Get such a batch update SQL statement:

UPDATE `post` SET `id` = CASE `id` WHEN '1' THEN '1' WHEN '2' THEN '2' WHEN '3' THEN '3' WHEN '4' THEN '4' WHEN '5' THEN '5' END,`parent_id` = CASE `id` WHEN '1' THEN '100' WHEN '2' THEN '100' WHEN '3' THEN '100' WHEN '4' THEN '100' WHEN '5' THEN '101' END,`title` = CASE `id` WHEN '1' THEN 'A' WHEN '2' THEN 'A' WHEN '3' THEN 'A' WHEN '4' THEN 'B' WHEN '5' THEN 'A' END,`sort` = CASE `id` WHEN '1' THEN '1' WHEN '2' THEN '3' WHEN '3' THEN '5' WHEN '4' THEN '7' WHEN '5' THEN '9' END WHERE `id` IN ('1','2','3','4','5') AND `parent_id` = '100' AND `title` = 'A'

The generated SQL lists all situations.

However, because there are WHERE conditions, only the records with IDs 1, 2, and 3 are updated.

If you only need to update a certain column, and other conditions are not limited, then the incoming $data can be simpler:

$data = [ ['id' => 1, 'sort' => 1], ['id' => 2, 'sort' => 3], ['id' => 3, 'sort' => 5], ]; echo batchUpdate($data, 'id');

If such a data format is passed in, you can modify theidFor records from 1 to 3, changesortto 1, 3, and 5 respectively.

Get the SQL statement:

UPDATE `post` SET `id` = CASE `id` WHEN '1' THEN '1' WHEN '2' THEN '2' WHEN '3' THEN '3' END,`sort` = CASE `id` WHEN '1' THEN '1' WHEN '2' THEN '3' WHEN '3' THEN '5' END WHERE `id` IN ('1','2','3')

This situation is simpler and more efficient.

The above is the entire content of this article. I hope it will be helpful to everyone's study. For more related content, please pay attention to the PHP Chinese website!

Related recommendations:

How to quickly generate modern forms with PHP

Algorithm of winning probability between PHP and JS

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