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
declare i int;
set i=0;
set @sum=(SELECT count(DISTINCT busi_id) FROM `B` WHERE `pid`=1242343324 and status=9 );
set @log = "log query ....";
select CONCAT(@log,@sum," 条");
while i<=@sum 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
offsetwidth和offsetleftselect 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_id=@busi_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 ;
上⾯的存储过程效率还不够快，并且limit i,1会导致，存储过程在执⾏到中间某⼀时刻后会取不到数据，导致漏数据。根据经验值，如果影响数据量在500W之内，可以直接游标取出来更新，⽽且不会有漏掉的。另外，根据经验值，批量提交3000到5000是可以的，当然保险期间可以1000到2000提交⼀
taro是什么意思次。
修改后的存储过程正下：
DELIMITER &&
CREATE PROCEDURE updateTimeV2(IN comCount bigint)
Begin
DECLARE c_busi_id BIGINT(20);
declare i int DEFAULT 0;
DECLARE cur_award CURSOR for
SELECT DISTINCT busi_id FROM `A` WHERE `pid`=1242343324 and yn =1 and order_time is null ;
OPEN cur_award;
LOOP
FETCH cur_award INTO c_busi_id;
set i=i+1;
selector抖音set @other_col =(SELECT cast(other_col as date) FROM `B` where `platform_id`=1242343324 and yn =1 and `busi_id` = c_busi_id limit 1); if @other_col is NULL THEN
select CONCAT(@log," other_col is null");
else
SET autocommit = 0;mysql查看所有存储过程
select CONCAT("before update c_busi_id is ",c_busi_id," other_col is ",@other_col);
update A set order_time =@other_col,modified = now() where `pid`=1242343324 and busi_id=c_busi_id and yn =1;
select CONCAT("after update c_busi_id is ",c_busi_id);
if mod(i,comCount)=0 then
select CONCAT("befor update commit i is ",i);
commit;
end if;
end if;
END LOOP;
commit;
SET autocommit = 1;
END&&
delimiter ;
pipa经测试优化后的存储过程的性能要远远优化优化前的，且不会漏记录。
备注：
inputstream cannot be null
1、测试了⼀下⼀条⼀条执⾏，⼀⼩时⼤概20W⾏更新
2、mod(i,1000)这块得注意，如果把set i=i+1；放到最后，在上⾯的存储过程批量就不会⽣效。
3、删除存储过程drop procedure updateTimeV1;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论