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

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

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

那如果要把多条user记录的备注字段改成不同的值，一般情况是使用foreach循环修改。我看了Yii2和ThinkPHP5框架的源码，均没有真正批量更新不同值的方法，ThinkPHP也是采用foreach的形式，然后启用事务机制，当某一条数据更新失败就回滚。例如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注入，以后批量更新不同记录的不同字段，就用它吧！

本文转载自火端网络

评论 (0)