使用hyperf框架没有批量更新方法,只能自己拼接SQL语句进行批量跟你更新
封装一个SQL句更新方法
- //批量更新
- public function updateBatch($multipleData = [])
- {
- try {
- $tableName = Db::getTablePrefix() . 'table';
- // 获取到数据里面的所有字段名
- $firstRow = current($multipleData);
- $updateColumn = array_keys($firstRow);
- // 以id为条件更新
- $referenceColumn = isset($firstRow['id']) ? 'id' : current($updateColumn);
- $updateSql = "UPDATE " . $tableName . " SET ";
- $sets = [];
- $bindings = [];
- $setSql = "`price` = CASE id";
- foreach ($multipleData as $data) {
- $setSql .= " WHEN " . $data['id'] . " THEN ".rand(7,15);
- }
- $setSql .= " END ";
- $sets[] = $setSql;
- $updateSql .= implode(', ', $sets);
- // 更新条件
- $whereIn = collect($multipleData)->pluck($referenceColumn)->values()->all();
- $whereIns = implode(',', $whereIn);
- // 最终拼接好的SQL语句
- $updateSql = rtrim($updateSql, ", ") . " WHERE `" . $referenceColumn . "` IN (" . $whereIns . ")";
- return Db::update($updateSql);
- } catch (\Exception $e) {
- return false;
- }
- }
上面方法,最终生成的$updateSql语句如下:
UPDATE table SET
`field` = CASE id
WHEN 279 THEN 8
WHEN 278 THEN 9
WHEN 277 THEN 15
WHEN 223 THEN 9
WHEN 221 THEN 12
END
WHERE `id` IN (279,278,277,223,221)