Laravel实现批量更新多条数据

前言

近期在刷新生产环境数据库的时候，需要更新表中的字段，如果对每条数据结果都执行一次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