前言

近期在刷新生产环境数据库的时候，需要更新表中的字段，如果对每条数据结果都执行一次update语句，占用的数据库资源就会很多，而且速度慢，显得也不高大上。

因为项目是Laravel框架，Laravel有批量插入的方法，却没有批量更新的方法，没办法只能自己实现。

准备

mysql case…when的用法

MySQL 的 case when 的语法有两种：

1. 简单函数

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END： 枚举这个字段所有可能的值

select id,status '状态值', case status
when 10 then '未开始'
when 20 then '配送中'
when 30 then '已完成'
when 40 then '已取消'
End '状态'
from table

输出结果：

2. 搜索函数

CASE WHEN [expr] THEN [result1]…ELSE [default] END

CASE WHEN [expr] THEN [result1]…ELSE [default] END：搜索函数可以写判断，并且搜索函数只会返回第一个符合条件的值，其他case被忽略

select id,lessee_id '租户ID', case 
when lessee_id <=1 then '自用系统'
when lessee_id >1  then '租用系统'
End '系统分类'
from waybill_base_info

case…when实现数据库的批量更新

更新单列的值

UPDATE base_info SET
    city_id = CASE id
        WHEN 1 THEN 100010
        WHEN 2 THEN 100011
        WHEN 3 THEN 100012
    END
WHERE id IN (1,2,3)

这句sql的意思是，更新city_id 字段：

1. 如果id=1 则city_id 的值为100010，
2. 如果id=2 则 city_id 的值为100011，
3. 如果id=3 则 city_id 的值为100012。
   即是将条件语句写在了一起。

这里的where部分不影响代码的执行，但是会提高sql执行的效率。

确保sql语句仅执行需要修改的行数，这里只有3条数据进行更新，而where子句确保只有3行数据执行。

更新多列的值

UPDATE base_info SET
city_id = CASE id
WHEN 1 THEN 100010
WHEN 2 THEN 100011
WHEN 3 THEN 100012
END,
city_name = CASE id
WHEN 1 THEN ‘北京’
WHEN 2 THEN ‘上海’
WHEN 3 THEN ‘广州’
END
WHERE id IN (1,2,3)

不过这个有个缺点 ： 要注意的问题是SQL语句的长度，需要考虑程序运行环境所支持的字符串长度，当然这也可以更新mysql的设置来扩展。

Laravel实现批量更新

在model方法中封装该批量更新的方法：

//批量更新
    public function updateBatch($multipleData = [])
    {
        try {
            if (empty($multipleData)) {
                Log::info("批量更新数据为空");
                return false;
            }
            $tableName = $this->table; // 表名
            $firstRow = current($multipleData);

        $updateColumn = array_keys($firstRow);
        // 默认以id为条件更新，如果没有ID则以第一个字段为条件
        $referenceColumn = isset($firstRow['id']) ? 'id' : current($updateColumn);
        unset($updateColumn[0]);
        // 拼接sql语句
        $updateSql = "UPDATE " . $tableName . " SET ";
        $sets = [];
        $bindings = [];
        foreach ($updateColumn as $uColumn) {
            $setSql = "`" . $uColumn . "` = CASE ";
            foreach ($multipleData as $data) {
                $setSql .= "WHEN `" . $referenceColumn . "` = ? THEN ? ";
                $bindings[] = $data[$referenceColumn];
                $bindings[] = $data[$uColumn];
            }
            $setSql .= "ELSE `" . $uColumn . "` END ";
            $sets[] = $setSql;
        }
        $updateSql .= implode(', ', $sets);
        $whereIn = collect($multipleData)->pluck($referenceColumn)->values()->all();
        $bindings = array_merge($bindings, $whereIn);
        $whereIn = rtrim(str_repeat('?,', count($whereIn)), ',');
        $updateSql = rtrim($updateSql, ", ") . " WHERE `" . $referenceColumn . "` IN (" . $whereIn . ")";
        Log::info($updateSql);
        // 传入预处理sql语句和对应绑定数据
        return DB::update($updateSql, $bindings);
    } catch (\Exception $e) {
        return false;
    }
}

在service层拼接需要更新的数据，并调用该函数：

 foreach ($taskInfo as $info) {
           $cityId = $info['requirement']['city_ids'];
           //此处省略n行代码
            $cityInfo = ['id' => $dataId[$info['id']]['id'], 'city_id' => $cityId];
            if ($cityInfo) {
                $cityInfos[] = $cityInfo;
            }
        }
        $res = $this->waybillDriverInfoModel->updateBatch($cityInfos);
 }

拼接的批量更新的数组格式为：
$students = [
[‘id’ => 1, ‘city_id’ => ‘100010’],
[‘id’ => 2, ‘city_id’ => ‘100011’],
];

生成的SQL语句如下：

UPDATE base_info SET `city_id` = CASE WHEN `id` = 1 THEN 100010 WHEN `id` = 2 THEN 100011 ELSE `city_id` END WHERE `id` IN (1,2)

因为每次只操作20条数据，所以这样拼接的字符串不会太长，符合mysql的字符串长度的要求，解决问题。

本文参考：https://blog.csdn.net/byywcsnd/article/details/78231429