前言
近期在刷新生產(chǎn)環(huán)境數(shù)據(jù)庫(kù)的時(shí)候，需要更新表中的字段，如果對(duì)每條數(shù)據(jù)結(jié)果都執(zhí)行一次update語(yǔ)句，占用的數(shù)據(jù)庫(kù)資源就會(huì)很多，而且速度慢。
因?yàn)轫?xiàng)目是laravel框架，laravel有批量插入的方法，卻沒(méi)有批量更新的方法，沒(méi)辦法只能自己實(shí)現(xiàn)。
準(zhǔn)備
mysql case…when的用法
mysql 的 case when 的語(yǔ)法有兩種：
簡(jiǎn)單函數(shù)
case [col_name] when [value1] then [result1]…else [default] end
case [col_name] when [value1] then [result1]…else [default] end： 枚舉這個(gè)字段所有可能的值
|
1
2
3
4
5
6
7
|
select id,status '狀態(tài)值', case statuswhen 10 then '未開(kāi)始'when 20 then '配送中'when 30 then '已完成'when 40 then '已取消'end '狀態(tài)'from table |
輸出結(jié)果：
搜索函數(shù)
case when [expr] then [result1]…else [default] end
case when [expr] then [result1]…else [default] end：搜索函數(shù)可以寫(xiě)判斷，并且搜索函數(shù)只會(huì)返回第一個(gè)符合條件的值，其他case被忽略
|
1
2
3
4
5
|
select id,lessee_id '租戶(hù)id', casewhen lessee_id <=1 then '自用系統(tǒng)'when lessee_id >1 then '租用系統(tǒng)'end '系統(tǒng)分類(lèi)'from waybill_base_info |
case…when實(shí)現(xiàn)數(shù)據(jù)庫(kù)的批量更新
更新單列的值
|
1
2
3
4
5
6
7
|
update base_info set city_id = case id when 1 then when 2 then when 3 then endwhere id in (1,2,3) |
這句sql的意思是，更新city_id 字段：
如果id=1 則city_id 的值為100010，
如果id=2 則 city_id 的值為100011，
如果id=3 則 city_id 的值為100012。
即是將條件語(yǔ)句寫(xiě)在了一起。
這里的where部分不影響代碼的執(zhí)行，但是會(huì)提高sql執(zhí)行的效率。
確保sql語(yǔ)句僅執(zhí)行需要修改的行數(shù)，這里只有3條數(shù)據(jù)進(jìn)行更新，而where子句確保只有3行數(shù)據(jù)執(zhí)行。
更新多列的值
|
1
2
3
4
5
6
7
8
9
10
11
12
|
update base_info setcity_id = case idwhen 1 then 100010when 2 then 100011when 3 then 100012end,city_name = case idwhen 1 then ‘北京'when 2 then ‘上海'when 3 then ‘廣州'endwhere id in (1,2,3) |
不過(guò)這個(gè)有個(gè)缺點(diǎn) ： 要注意的問(wèn)題是sql語(yǔ)句的長(zhǎng)度，需要考慮程序運(yùn)行環(huán)境所支持的字符串長(zhǎng)度，當(dāng)然這也可以更新mysql的設(shè)置來(lái)擴(kuò)展。
laravel實(shí)現(xiàn)批量更新
在model方法中封裝該批量更新的方法：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
//批量更新 public function updatebatch($multipledata = []) { try { if (empty($multipledata)) { log::info("批量更新數(shù)據(jù)為空"); return false; } $tablename = $this->table; // 表名 $firstrow = current($multipledata); $updatecolumn = array_keys($firstrow); // 默認(rèn)以id為條件更新，如果沒(méi)有id則以第一個(gè)字段為條件 $referencecolumn = isset($firstrow['id']) ? 'id' : current($updatecolumn); unset($updatecolumn[0]); // 拼接sql語(yǔ)句 $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); // 傳入預(yù)處理sql語(yǔ)句和對(duì)應(yīng)綁定數(shù)據(jù) return db::update($updatesql, $bindings); } catch (\exception $e) { return false; }} |
在service層拼接需要更新的數(shù)據(jù)，并調(diào)用該函數(shù)：
|
1
2
3
4
5
6
7
8
9
10
|
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語(yǔ)句如下：
|
1
|
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) |
因?yàn)槊看沃徊僮?0條數(shù)據(jù)，所以這樣拼接的字符串不會(huì)太長(zhǎng)，符合mysql的字符串長(zhǎng)度的要求，解決問(wèn)題。
本文主要講解了laravel實(shí)現(xiàn)批量更新多條數(shù)據(jù)的方法，更多關(guān)于laravel的使用技巧請(qǐng)查看下面的相關(guān)鏈接
原文鏈接：https://blog.csdn.net/qq_28673091/article/details/100534908
