1. 核心逻辑:CASE WHEN条件更新
UPDATE table SET order_items_id = CASE WHEN stock_out_item_id = 1 THEN 100WHEN stock_out_item_id = 2 THEN 200ELSE order_items_id END WHERE stock_out_item_id IN (1,2)
- 原理:通过单条SQL实现多条记录的条件更新,每个
WHEN
对应一个stock_out_item_id
到order_item_id
的映射 - ELSE保留原值:确保不在更新列表中的记录保持不变
- WHERE IN过滤:仅更新指定ID的记录,避免全表扫描
以php代码为例
public function handle(){$this->syncStockoutItemsSaleOrderItemsIdV2();}public function syncStockoutItemsSaleOrderItemsIdV2(){// 关闭查询日志以减少内存使用DB::connection('mysql')->disableQueryLog();// 记录开始时间$startTime = microtime(true);$updatedCount = 0;DB::connection("mysql")->table('lie_stock_out_items')->orderBy("stock_out_item_id","desc")->where("order_items_id",0)->where("ext_info","like","%order_item_id%")->chunk(5000, function ($stockOutItemsList) use (&$updatedCount) {// 准备批量更新的数据$itemsUpdateData = []; // 用于lie_stock_out_items表的更新数据$reportUpdateData = []; // 用于lie_stock_out_report表的更新数据foreach($stockOutItemsList as $stockOutItems){$extinfo = json_decode($stockOutItems->ext_info, true);if(!empty($extinfo) && !empty($extinfo["order_item_id"])){$stockOutItemId = $stockOutItems->stock_out_item_id;$orderItemId = $extinfo["order_item_id"];// 收集需要更新的数据,使用关联数组存储$itemsUpdateData[$stockOutItemId] = $orderItemId;$reportUpdateData[$stockOutItemId] = $orderItemId;$updatedCount++;}}// dump($itemsUpdateData);// dump($reportUpdateData);// 执行批量更新$this->batchUpdate('lie_stock_out_items', $itemsUpdateData);$this->batchUpdate('lie_stock_out_report', $reportUpdateData);});// 输出执行统计$endTime = microtime(true);$executionTime = round($endTime - $startTime, 2);echo "批量更新完成!共处理 {$updatedCount} 条记录,耗时 {$executionTime} 秒\n";}// 批量更新方法private function batchUpdate($table, $updateData){if(empty($updateData)) return;// 分批处理大数组,每批最多处理2000条,避免SQL语句过长$chunks = array_chunk($updateData, 2000, true);foreach($chunks as $chunk){$cases = [];$params = [];$ids = [];// 构建CASE语句foreach ($chunk as $stockOutItemId => $orderItemId) {$ids[] = $stockOutItemId;$cases[] = "WHEN stock_out_item_id = ? THEN ?";$params[] = $stockOutItemId;$params[] = $orderItemId;}// 执行批量更新$idsStr = implode(',', $ids);$casesStr = implode(' ', $cases);$query = "UPDATE {$table} SET order_items_id = CASE {$casesStr} ELSE order_items_id END WHERE stock_out_item_id IN ({$idsStr})";// dump($query);// dump($params);DB::connection('mysql')->statement($query, $params);}}
分块处理机制(关键优化)
$chunks = array_chunk($updateData, 2000, true); // 每批2000条
- 必要性:MySQL对单条SQL语句长度有限制(max_allowed_packet默认4MB),超大数据量会导致:
- 语法解析失败
- 内存溢出
- 网络传输超时
- 分块策略:保持原始键值对关系(
true
参数),确保更新时ID与新值正确对应
3. 参数绑定防注入
$params = [1,100,2,200]; // 实际绑定值 DB::statement($query, $params); // 使用PDO预处理
- 安全机制:所有动态值通过
?
占位符绑定,避免SQL注入 - 参数顺序:绑定顺序需与
CASE
中的?
出现顺序严格对应
4. 性能对比优势
更新方式 | SQL次数 | 数据库交互 | 事务开销 | 适用场景 |
---|---|---|---|---|
传统循环更新 | N次 | N次 | N次 | 小数据量 |
本方案 | 1次 | 1次 | 1次 | 大数据量批量更新 |
Eloquent批量更新 | 1次 | 1次 | 1次 | 简单字段更新 |
执行流程示例
假设输入:
$updateData = [101 => 2001,102 => 2002,// ... 共3000条 ];
处理步骤:
- 分成2个块(每块2000条)
- 生成SQL片段:
-- 块1 UPDATE table SET order_items_id = CASE WHEN stock_out_item_id=101 THEN 2001... # 共2000个WHEN END WHERE stock_out_item_id IN (101,102,...,2000)-- 块2 UPDATE table SET order_items_id = CASE WHEN stock_out_item_id=2001 THEN 2003... # 剩余1000个WHEN END WHERE stock_out_item_id IN (2001,2002,...,3000)
这种方案在百万级数据更新场景下,比传统循环更新快10倍以上,同时避免内存溢出风险,是Laravel中处理大数据量更新的标准实践。
本文来自博客园,作者:孙龙-程序员,转载请注明原文链接:https://www.cnblogs.com/sunlong88/p/19105686