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_values is 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 recordupdate once, which has poor performance and can easily cause blocking.

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

2.1 CASE 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 trick 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, 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 the post table based on the id and parent_id fields.

Among them, the value of id will change, and the value of parent_id will 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 with parent_id to be 100 and title to 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 the idFor records from 1 to 3, change sort to 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!