前言
近期在刷新生產(chǎn)環(huán)境數(shù)據(jù)庫的時候，需要更新表中的字段，如果對每條數(shù)據(jù)結果都執(zhí)行一次update語句，占用的數(shù)據(jù)庫資源就會很多，而且速度慢。
因為項目是Laravel框架，Laravel有批量插入的方法，卻沒有批量更新的方法，沒辦法只能自己實現(xiàn)。
準備
mysql case…when的用法
MySQL 的 case when 的語法有兩種：
簡單函數(shù)
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END： 枚舉這個字段所有可能的值
select id,status '狀態(tài)值', case status
when 10 then '未開始'
when 20 then '配送中'
when 30 then '已完成'
when 40 then '已取消'
End '狀態(tài)'
from table
輸出結果：
搜索函數(shù)
CASE WHEN [expr] THEN [result1]…ELSE [default] END
CASE WHEN [expr] THEN [result1]…ELSE [default] END：搜索函數(shù)可以寫判斷，并且搜索函數(shù)只會返回第一個符合條件的值，其他case被忽略
select id,lessee_id '租戶ID', case
when lessee_id =1 then '自用系統(tǒng)'
when lessee_id >1 then '租用系統(tǒng)'
End '系統(tǒng)分類'
from waybill_base_info
case…when實現(xiàn)數(shù)據(jù)庫的批量更新
更新單列的值
UPDATE base_info SET
city_id = CASE id
WHEN 1 THEN
WHEN 2 THEN
WHEN 3 THEN
END
WHERE id IN (1,2,3)
這句sql的意思是，更新city_id 字段：
如果id=1 則city_id 的值為100010，
如果id=2 則 city_id 的值為100011，
如果id=3 則 city_id 的值為100012。
即是將條件語句寫在了一起。
這里的where部分不影響代碼的執(zhí)行，但是會提高sql執(zhí)行的效率。
確保sql語句僅執(zhí)行需要修改的行數(shù)，這里只有3條數(shù)據(jù)進行更新，而where子句確保只有3行數(shù)據(jù)執(zhí)行。
更新多列的值
UPDATE base_info SET
city_id = CASE id
WHEN 1 THEN 100010
WHEN 2 THEN 100011
WHEN 3 THEN 100012
END,
city_name = CASE id
WHEN 1 THEN ‘北京'
WHEN 2 THEN ‘上海'
WHEN 3 THEN ‘廣州'
END
WHERE id IN (1,2,3)
不過這個有個缺點 ： 要注意的問題是SQL語句的長度，需要考慮程序運行環(huán)境所支持的字符串長度，當然這也可以更新mysql的設置來擴展。
Laravel實現(xiàn)批量更新
在model方法中封裝該批量更新的方法：
//批量更新
public function updateBatch($multipleData = [])
{
try {
if (empty($multipleData)) {
Log::info("批量更新數(shù)據(jù)為空");
return false;
}
$tableName = $this->table; // 表名
$firstRow = current($multipleData);
$updateColumn = array_keys($firstRow);
// 默認以id為條件更新，如果沒有ID則以第一個字段為條件
$referenceColumn = isset($firstRow['id']) ? '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語句和對應綁定數(shù)據(jù)
return DB::update($updateSql, $bindings);
} catch (\Exception $e) {
return false;
}
}
在service層拼接需要更新的數(shù)據(jù)，并調(diào)用該函數(shù)：
foreach ($taskInfo as $info) {
$cityId = $info['requirement']['city_ids'];
//此處省略n行代碼
$cityInfo = ['id' => $dataId[$info['id']]['id'], 'city_id' => $cityId];
if ($cityInfo) {
$cityInfos[] = $cityInfo;
}
}
$res = $this->waybillDriverInfoModel->updateBatch($cityInfos);
}
拼接的批量更新的數(shù)組格式為：
$students = [
[‘id' => 1, ‘city_id' => ‘100010'],
[‘id' => 2, ‘city_id' => ‘100011'],
];
生成的SQL語句如下：
UPDATE base_info SET `city_id` = CASE WHEN `id` = 1 THEN 100010 WHEN `id` = 2 THEN 100011 ELSE `city_id` END WHERE `id` IN (1,2)
因為每次只操作20條數(shù)據(jù)，所以這樣拼接的字符串不會太長，符合mysql的字符串長度的要求，解決問題。
本文主要講解了Laravel實現(xiàn)批量更新多條數(shù)據(jù)的方法，更多關于Laravel的使用技巧請查看下面的相關鏈接
您可能感興趣的文章:- Laravel框架學習筆記之批量更新數(shù)據(jù)功能
- Laravel 批量更新多條數(shù)據(jù)的示例
- laravel實現(xiàn)批量更新多條記錄的方法示例
