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: 枚举这个字段所有可能的值

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被忽略

select id,lessee_id '租户ID', case
when lessee_id <=1 then '自用系统'
when lessee_id >1 then '租用系统'
End '系统分类'
from waybill_base_info

case…when实现数据库的批量更新

更新单列的值

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行数据执行。

更新多列的值

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方法中封装该批量更新的方法:

//批量更新
 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层拼接需要更新的数据,并调用该函数:

 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语句如下:

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的使用技巧请查看下面的相关链接

(0)

相关推荐

  • Laravel框架学习笔记之批量更新数据功能

    本文实例讲述了Laravel框架批量更新数据功能.分享给大家供大家参考,具体如下: 前言 上周公司的比赛项目以泪奔结束...一台2核4G的VPS完全蹦了..集体被老大叼杠.下个月的比赛又要开始了,所以现在抓紧时间升级服务器,优化代码与SQL,刚看到排名那有1000多条更新语句,太受不了了,所以在google找了下资源,找到了批量更新数据的方法,记录一下. 代码 //from https://github.com/mavinoo/laravelBatch static function batch

  • laravel实现批量更新多条记录的方法示例

    前言 相信熟悉laravel的童鞋都知道,laravel有批量一次性插入多条记录,却没有一次性按条件更新多条记录. 是否羡慕thinkphp的saveAll,是否羡慕ci的update_batch,但如此优雅的laravel怎么就没有类似的批量更新的方法呢? 高手在民间 Google了一下,发现stackoverflow( https://stackoverflow.com/questions/26133977/laravel-bulk-update )上已经有人写好了,但是并不能防止sql注入

  • Laravel 批量更新多条数据的示例

    引言 最近在写任务中,碰到一个问题,需要批量更新多条数据,但是Laravel没有提供这样的方法,Google了一些方法,刚好借着任务来举例说明一下. 任务要求 任务是一个简单的清除未读通知的API,其实就是把通知表中符合user id 和 is read = 0 的行中的 is_read改为1(0代表未读,1代表已读). 方法1 我首先想到的是利用where()方法查出user id和is read符合条件的notices,然后利用foreach循环和save()更新数据表. $notices

  • Laravel实现批量更新多条数据

    前言 近期在刷新生产环境数据库的时候,需要更新表中的字段,如果对每条数据结果都执行一次update语句,占用的数据库资源就会很多,而且速度慢. 因为项目是Laravel框架,Laravel有批量插入的方法,却没有批量更新的方法,没办法只能自己实现. 准备 mysql case-when的用法 MySQL 的 case when 的语法有两种: 简单函数 CASE [col_name] WHEN [value1] THEN [result1]-ELSE [default] END CASE [co

  • mysql批量更新多条记录的同一个字段为不同值的方法

    首先mysql更新数据的某个字段,一般这样写: UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value'; 也可以这样用in指定要更新的记录: UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values'); 这里注意 'other_values' 是一个逗号(,)分隔的字符串,如:1,2,3 如果更新多条数据而且每条记录要更新

  • mysql 批量更新与批量更新多条记录的不同值实现方法

    批量更新 mysql更新语句很简单,更新一条数据的某个字段,一般这样写: 复制代码 代码如下: UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value'; 如果更新同一字段为同一个值,mysql也很简单,修改下where即可: 复制代码 代码如下: UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values'); 这里注意 'o

  • MySQL如何快速批量插入1000w条数据

    听说有个面试题是: 如何快速向mysql中插入1000w条数据? 我私下试了一下, 发现插入10000条数据用了0.9s, 插入10w条数据用了4.7s, 插入100w条数据用了58s左右,1000w条数据,我的笔记本吭哧了5分钟,自己停了, 心中1000w只草泥马呼啸而过,我用的是下面的代码: -- 进入数据库 use test; -- 显示所有表 show tables; -- 创建majors表 create table majors(id int, major varchar(255))

  • MySql中使用INSERT INTO语句更新多条数据的例子

    我们知道当插入多条数据的时候insert支持多条语句: 复制代码 代码如下: INSERT INTO t_member (id, name, email) VALUES     (1, 'nick', 'nick@126.com'),     (4, 'angel','angel@163.com'),     (7, 'brank','ba198@126.com'); 但是对于更新记录,由于update语法不支持一次更新多条记录,只能一条一条执行: 复制代码 代码如下: UPDATE t_mem

  • ASP.NET一次性对GridView批量更新多行数据

    假定有一个Product表,字段有(Id,Name,Quantity,...)我们要一次批量更新Quantity的值 首先在Gridview中,Quantity列以TemplateField显示,其他的列属性设为只读,把显示格式设为TextBox. <asp:TemplateField HeaderText="Quantity"> <itemtemplate> <asp:TextBox ID="editQuantity" runat=&

  • 详解mybatis 批量更新数据两种方法效率对比

    上节探讨了批量新增数据,这节探讨批量更新数据两种写法的效率问题. 实现方式有两种, 一种用for循环通过循环传过来的参数集合,循环出N条sql, 另一种 用mysql的case when 条件判断变相的进行批量更新 下面进行实现. 注意第一种方法要想成功,需要在db链接url后面带一个参数  &allowMultiQueries=true 即:  jdbc:mysql://localhost:3306/mysqlTest?characterEncoding=utf-8&allowMulti

随机推荐