mysql之存储过程（四）--批量更新操作
最近有一个场景，在生产环境的一个库中，新增了一个字段。需要从另一个关联表中找到相当的字段回填。
影响数据数百万条。
首先，不能使用一条大的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
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;
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 ;
end if;
end if;
end while;
commit; #最后不足1000条时提交
SET autocommit = 1;
END&&
delimiter ;
备注：
1、测试了一下一条一条执行，一小时大概20W行更新
2、mod(i,1000)这块得注意 ，如果把set i=i+1;放到最后，在上面的存储过程批量就不会生效。
相关推荐
Omega 2020-08-16
zjyzz 2020-08-16
minggehenhao 2020-07-28
zhaojp0 2020-06-27
zjuwangleicn 2020-06-25
wenjieyatou 2020-06-17
liaomingwu 2020-06-16
hungzz 2020-06-16
muzirigel 2020-06-14
gsmfan 2020-06-14
ncomoon 2020-06-14
xiaobaif 2020-06-13
Rain 2020-06-12
hanshangzhi 2020-06-12
talkingDB 2020-06-12
IBMRational 2020-06-09
snowguy 2020-06-01