Laravel如何实现指定匹配条件的数据表高效批量更新

场景描述 #

业务表包含columnindex、type、id三个字段，初始表数据：

columnindex |  type | id
1               1     1
2               2     1
3               3     1

待更新数组为$arr = [1 => 2, 2 => 5, 3 => 6,...]，数组键对应columnindex值，数组值对应需要更新的type值，要求更新后同id下的type字段和数组映射关系匹配。
原有逐行循环更新方案无优化，每次循环触发一次SQL请求，数据量大时执行效率极低，原实现代码：

$arr = [1 => 2, 2 => 5, 3 => 6];
foreach($arr as $columnindex => $type) {
    SessionPrepared::where("id", 1)->where("columnindex", $columnindex)->update(["type" => $type]);
}

最优实现：单条SQL批量更新（基于CASE WHEN语法） #

该方案仅需1次数据库交互，性能是逐行更新的几十到上百倍，是批量按条件更新不同值的标准实现方式。
实现逻辑是利用MySQL的CASE WHEN表达式，匹配每行的columnindex值返回对应的目标type值，同时增加WHERE条件限定更新范围，避免误改其他数据。
以ThinkPHP/Laravel框架下的ORM实现为例：

$arr = [1 => 2, 2 => 5, 3 => 6];
$bindId = 1;
// 拼接CASE WHEN逻辑与范围条件值
$whenSegments = [];
$targetColumnIndexes = [];
foreach ($arr as $idx => $targetType) {
    $targetColumnIndexes[] = $idx;
    $whenSegments[] = sprintf("WHEN %d THEN %d", $idx, $targetType);
}
$caseExpression = implode(' ', $whenSegments);
$idxRange = implode(',', $targetColumnIndexes);
// 执行单条更新SQL
SessionPrepared::whereRaw("id = ? AND columnindex IN ({$idxRange})", [$bindId])
    ->update([
        'type' => \DB::raw("CASE columnindex {$caseExpression} END")
    ]);

上述代码生成的原生SQL如下：

UPDATE `session_prepared`
SET `type` = CASE columnindex
    WHEN 1 THEN 2
    WHEN 2 THEN 5
    WHEN 3 THEN 6
END
WHERE id = 1 AND columnindex IN (1,2,3);

兼容方案：事务包裹循环更新 #

如果受业务场景限制无法构造原生表达式，可以给循环更新包裹数据库事务，减少多次事务提交的IO开销，性能比无事务裸循环高5倍左右，适合更新条数少于100条的小数据量场景：

$arr = [1 => 2, 2 => 5, 3 => 6];
\DB::transaction(function () use ($arr) {
    foreach ($arr as $columnindex => $type) {
        SessionPrepared::where("id", 1)
            ->where("columnindex", $columnindex)
            ->update(["type" => $type]);
    }
});

注意：使用CASE WHEN方案时，必须添加columnindex IN (...)的范围过滤条件，否则表中不匹配的行的type字段会被更新为NULL，造成数据异常。

内容的提问来源于stack exchange，提问作者Dmitro