mysql之存储过程(四)--批量更新操作

王艺强 2020-04-23

最近有一个场景,在生产环境的一个库中,新增了一个字段。需要从另一个关联表中找到相当的字段回填。

影响数据数百万条。

首先,不能使用一条大的update语句来更新,这个锁太大,容易产生锁征用,造成死锁。

 update B a set new_column=(SELECT other_col from A b where status=9 and a.busi_id=b.busi_id and b.pid=1242343324),modified=now() where pid=21343
 
因此,写一个存储过程解决,存储过程一条条处理,太慢且消耗本地IO,所以写了个批量更新的存储过程。
如下:
 
DELIMITER &&
CREATE PROCEDURE updateTimeV1(IN comCount bigint)
BEGIN
    #Routine body goes here...

  declare i int;
  set i=0;
  set @sum=(SELECT count(DISTINCT order_no) FROM `B` WHERE `pid`=1242343324 and status=9 );
  set @log = "log query ....";
  select CONCAT(@log,@sum," 条");
 
  while i< do
   set i=i+1;
   set @busi_id =( SELECT DISTINCT busi_id FROM `A` WHERE `pid`=1242343324  limit i,1);
   set @other_col =(SELECT other_col FROM `B` where `pid`=1242343324 and yn =1 and `busi_id` = @busi_id  limit 1);
   if @busi_id is NULL THEN
    select CONCAT(@log," busi_id is null");
   elseif @other_col is NULL THEN
    select CONCAT(@log," other_col is null");
   else
      #START TRANSACTION;
      /** 关闭事务的自动提交 */  
      SET autocommit = 0;
      update A set new_column other_col,modified = now() where  `pid`=1242343324  and busi_idbusi_id  and status =15;  
   ##   if mod(i,comCount)=0 then commit;
    if mod(i,1000)=0 then commit;
   end if;
   
   end if;
     
   end while;
   commit; #最后不足1000条时提交
   SET autocommit = 1;

END&&
delimiter ;
 
备注:
      1、测试了一下一条一条执行,一小时大概20W行更新
      2、mod(i,1000)这块得注意 ,如果把set i=i+1;放到最后,在上面的存储过程批量就不会生效。

相关推荐

拼命工作好好玩 / 0评论 2020-06-13