Mysql快速批量更新多条记录不同值的语句

释放双眼,带上耳机,听听看~!

如果是要把Mysql多条数据的某个字段改成一样的值,那语句非常简单,例如把user表的部分用户状态改成 1,语句如下:

UPDATE user SET status=1 WHERE id in ('1001,1002,1003');

那如果要把多条user记录的备注字段改成不同的值,一般情况是使用foreach循环修改。我看了Yii2和ThinkPHP5框架的源码,均没有真正批量更新不同值的方法,ThinkPHP也是采用foreach的形式,然后启用事务机制,当某一条数据更新失败就回滚。例如ThinkPHP5的saveAll批量更新操作就是使用如下代码:

ThinkPHP5的saveAll批量更新操作

这种方案当同时更新大量数据的时候很多问题就来了,比如处理时间过长,甚至超时,如果数据库不支持事务机制,还会导致数据无法回滚,最终导致数据错乱。难道就没有一种像批量插入数据的语句来批量更新数据么?答案是有的!

我们采用Mysql的WHEN  THEN 来判断做处理,现在需要把多个user表的记录里的备注改成不同的值,语句如下:

UPDATE user
SET remark = CASE id
WHEN 1001 THEN '是小明介绍来的'
WHEN 1002 THEN '比较有钱'
WHEN 1003 THEN '这个客户来自逍遥站长网'
END
WHERE id IN (1001,1002,1003)

没错,这样就可以一次性修改了多条记录的不同值,如果我们想修改备注字段的同时,还想把其他字段修改为不同的值,可以这样:

UPDATE user
SET remark = CASE id
WHEN 1001 THEN '是小明介绍来的'
WHEN 1002 THEN '比较有钱'
WHEN 1003 THEN '这个客户来自逍遥站长网'
END,
realname = CASE id
WHEN 1001 THEN '田馥甄'
WHEN 1002 THEN '陈嘉桦'
WHEN 1003 THEN '任家萱'
END
WHERE id IN (1001,1002,1003)

如果要修改大量数据,就需要用动态语言把SQL语句循环组装一下,生成的语句会比较长,别以为这样会很麻烦,此方法批量更新会快很多倍。火端喜欢用数据说话,此类比较都会亲自测试对比,火端尝试使用foreach循环修改1000条数据,测试多次,平均耗时2.8秒,而采用此方法一次性批量更新,仅仅花了0.06秒!区别非常大,使用该方法批量更新是非常划算的。

如果需要使用它,建议大家自己封装一个方法,也可以做参数绑定,防止SQL注入,以后批量更新不同记录的不同字段,就用它吧!

人已赞赏
技术经验

使用HTTP缓存状态码200和304提升网站访问速度

2020-12-5 13:48:24

技术经验

提升PHP程序运行速度的几个优化方案

2020-12-5 14:11:32

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索