Laravel实现批量更新多条数据

前言 近期在刷新生产环境数据库的时候，需要更新表中的字段，如果对每条数据结果都执行一次update语句，占用的数据库资源就会很多，而且速度慢。 因为项目是Laravel框架，Laravel有批量插入的方法，却没有批量更新的方法，没办法只能自己实现。 准备 mysql case…when的用法 MySQL 的 case when 的语法有两种： 简单函数 CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END CASE [col_name] WHEN [value1] THEN [result1]…ELSE [defau 在本文中，我们将探讨如何在Laravel框架中实现批量更新多条数据，这对于处理大量数据更新时能显著提高效率和减少数据库资源的消耗。Laravel虽然提供了方便的批量插入数据的功能，但在批量更新方面并不直接支持，因此我们需要利用MySQL的`CASE...WHEN`语句来实现这一目标。 让我们了解MySQL的`CASE...WHEN`语法。它分为两种形式： 1. **简单函数CASE**: 这种情况下的`CASE`语句用于根据某个列的特定值返回不同的结果。例如，我们可以通过以下方式枚举一个字段的所有可能值： ```sql SELECT id, status '状态值', CASE status WHEN 10 THEN '未开始' WHEN 20 THEN '配送中' WHEN 30 THEN '已完成' WHEN 40 THEN '已取消' END '状态' FROM table; ``` 2. **搜索函数CASE**: 这种形式允许在`WHEN`后面的条件中使用表达式，返回第一个符合条件的结果。例如： ```sql SELECT id, lessee_id '租户ID', CASE WHEN lessee_id <= 1 THEN '自用系统' WHEN lessee_id > 1 THEN '租用系统' END '系统分类' FROM waybill_base_info; ``` 批量更新数据的核心在于使用`CASE...WHEN`结合`WHERE`子句来指定需要更新的记录。例如，如果我们想更新`base_info`表中`city_id`字段的值，可以这样编写SQL语句： ```sql 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会根据`id`的值来设定`city_id`，同时`WHERE`子句确保只更新指定的记录。 当需要更新多列时，可以将`CASE`语句扩展到多个列，但需要注意SQL语句的长度可能会受到限制，这取决于你的程序运行环境和数据库配置。 为了在Laravel中实现批量更新，我们可以创建一个Model方法来封装这个逻辑。以下是一个示例： ```php // 在Model中添加此方法 public function updateBatch($multipleData = []) { try { if (empty($multipleData)) { Log::info("批量更新数据为空"); return false; } $tableName = $this->table; // 获取表名 $firstRow = current($multipleData); $updateColumn = array_keys($firstRow); // 获取需要更新的列名 $referenceColumn = isset($firstRow['id']) ? 'id' : current($updateColumn); // 使用'id'或第一个字段作为条件 unset($updateColumn[0]); // 移除'id'列（如果存在） // 拼接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 .= "END, "; $sets[] = $setSql; } $updateSql .= implode(", ", $sets); // 去掉最后一个逗号 $updateSql = rtrim($updateSql, ', '); // 添加WHERE子句 $updateSql .= " WHERE `" . $referenceColumn . "` IN (" . implode(", ", array_fill(0, count($multipleData), '?')) . ")"; $bindings = array_merge($bindings, array_column($multipleData, $referenceColumn)); DB::update($updateSql, $bindings); } catch (\Exception $e) { Log::error("批量更新出错: " . $e->getMessage()); } } ``` 这个`updateBatch`方法接受一个二维数组，其中每个元素都是一个关联数组，代表一条需要更新的数据记录。通过循环遍历这些记录，我们构建了`CASE...WHEN`结构并执行更新操作。 总结来说，通过理解MySQL的`CASE...WHEN`语法并结合Laravel的Eloquent ORM，我们可以创建自定义的批量更新方法，有效提高数据更新的效率，降低数据库的负载。在实际项目中，这种实现方式对于处理大量数据的更新场景尤其有用。