laravel框架中批量更新数据

在php框架中 tp中就有批量更新封装好的 SaveAll

在laravel中有批量插入没有批量更新操作;因此我们可以自己去封装一个然后批量进行更新操作

封装参考代码:

/*** 批量更新** @param $tableName 表名称* @param string $pk 更新的字段* @param array $multipleData 要更新的数据* @return bool|int*/
public function updateBatch($tableName, string $pk = 'id', array $multipleData = []): bool|int
{try {if (empty($multipleData)) {Log::info("批量更新数据为空");return false;}$firstRow = current($multipleData);$updateColumn = array_keys($firstRow);// 默认以id为条件更新，如果没有ID则以第一个字段为条件$referenceColumn = !isset($firstRow[$pk]) ? '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) {Log::info("批量更新数据失败：" . $e->getMessage());return false;}
}

运行前可以先打印查看数据是否正确这个封装可以有效防止sql注入问题

一般用于脚本批量更新

下标是表名称 --我是多表更新