前言
近期在刷新生产环境数据库的时候,需要更新表中的字段,如果对每条数据结果都执行一次update语句,占用的数据库资源就会很多,而且速度慢。
因为项目是laravel框架,laravel有批量插入的方法,却没有批量更新的方法,没办法只能自己实现。
准备
mysql case…when的用法
mysql 的 case when 的语法有两种:
简单函数
case [col_name] when [value1] then [result1]…else [default] end
case [col_name] when [value1] then [result1]…else [default] end: 枚举这个字段所有可能的值
1
2
3
4
5
6
7
|
select id,status '状态值' , case status when 10 then '未开始' when 20 then '配送中' when 30 then '已完成' when 40 then '已取消' end '状态' from table |
输出结果:
搜索函数
case when [expr] then [result1]…else [default] end
case when [expr] then [result1]…else [default] end:搜索函数可以写判断,并且搜索函数只会返回第一个符合条件的值,其他case被忽略
1
2
3
4
5
|
select id,lessee_id '租户id' , case when lessee_id <=1 then '自用系统' when lessee_id >1 then '租用系统' end '系统分类' from waybill_base_info |
case…when实现数据库的批量更新
更新单列的值
1
2
3
4
5
6
7
|
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部分不影响代码的执行,但是会提高sql执行的效率。
确保sql语句仅执行需要修改的行数,这里只有3条数据进行更新,而where子句确保只有3行数据执行。
更新多列的值
1
2
3
4
5
6
7
8
9
10
11
12
|
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语句的长度,需要考虑程序运行环境所支持的字符串长度,当然这也可以更新mysql的设置来扩展。
laravel实现批量更新
在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( "批量更新数据为空" ); 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语句和对应绑定数据 return db::update( $updatesql , $bindings ); } catch (\exception $e ) { return false; } } |
在service层拼接需要更新的数据,并调用该函数:
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 ); } |
拼接的批量更新的数组格式为:
$students = [
[‘id' => 1, ‘city_id' => ‘100010'],
[‘id' => 2, ‘city_id' => ‘100011'],
];
生成的sql语句如下:
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) |
因为每次只操作20条数据,所以这样拼接的字符串不会太长,符合mysql的字符串长度的要求,解决问题。
本文主要讲解了laravel实现批量更新多条数据的方法,更多关于laravel的使用技巧请查看下面的相关链接
原文链接:https://blog.csdn.net/qq_28673091/article/details/100534908