Laravel 中实现 MySQL 批量更新的两种方式：case when 与 join

示例数据

假设有一个 users 表，结构如下：

现在要实现如下的批量更新：

• id 为 1 的用户状态更新为 inactive
• id 为 2 的用户状态更新为 suspended
• id 为 3 的用户状态更新为 active
• id 为 4 的用户状态更新为 suspended

假设更新条件存储在数组 $updates 中：

$updates = [
    ['id' => 1, 'status' => 'inactive'],
    ['id' => 2, 'status' => 'suspended'],
    ['id' => 3, 'status' => 'active'],
    ['id' => 4, 'status' => 'suspended'],
];

方法一：使用 case when 实现批量更新

case when 语句适合较少数据的批量更新操作，可以根据 id 的值选择性更新每行的数据。

实现代码

1. 构造 case when 语句：遍历数组，将 id 与对应的 status 值拼接成 when 条件。
2. 生成完整 SQL：使用 case when 条件更新，限定更新范围。

use Illuminate\Support\Facades\DB;

$sql = 'update users set status = case id';
foreach ($updates as $update) {
    $sql .= sprintf('when %d then %s ', $update['id'], $update['status');
}
$sql .= sprintf( 'else status end where id in (%s)', implode(',', array_column($updates, 'id')) );

DB::update($sql);
// 或: DB::statement($sql);

生成的 SQL 如下：

update users
set status = case id
                 when 1 then 'inactive'
                 when 2 then 'suspended'
                 when 3 then 'active'
                 when 4 then 'suspended'
                 else status end
where id in (1, 2, 3, 4);

优缺点

• 优点：简单清晰，适合小批量更新。
• 缺点：更新多字段时处理较麻烦, 数据量较大时 SQL 会变得冗长，性能不如 join 高。

方法二：使用 join union all 实现批量更新

join union all 适合较大数据量的批量更新。它通过虚拟表更新匹配的字段，性能更优。

实现代码

1. 构造 union all 子查询：将 $updates 数组转换为 union all 子查询结构。
2. 生成完整 SQL：使用 join 将主表与子查询连接，进行更新操作。

use Illuminate\Support\Facades\DB;

$union = [];
foreach ($updates as $update){
    $union[] = sprintf("select %s as id, '%s' as status", $update['id'], $update['status']);
}
$union = implode(' union all ', $union);
$ids   = implode(',', array_column($updates, 'id'));

$sql = <<<SQL
update users as u
    join ({$union}) as updates
on u.id = updates.id
    set u.status = updates.status
where u.id in ({$ids})
SQL;

DB::update($sql);
// 或: DB::statement($sql);

生成的 SQL 如下：

update users as u
    join (select 1 as id, 'inactive' as status
          union all
          select 2 as id, 'suspended' as status
          union all
          select 3 as id, 'active' as status
          union all
          select 4 as id, 'suspended' as status) as updates on u.id = updates.id
set u.status = updates.status
where u.id in (1, 2, 3, 4);

优缺点

• 优点：性能较高，适合大量数据批量更新。
• 缺点：代码稍微复杂一些，但 SQL 结构更加清晰。

总结

根据数据量和需求选择合适的方式：case when 更适合简单的、小数据量的批量更新；而 join union all 在数据量大、更新字段较多时更高效。

注意：以上代码仅仅供参考, 使用是需要结合实际场景进行修改。(比如: 数据量过大时需要分批处理)

本作品采用《CC 协议》，转载必须注明作者和本文链接