Mysql 存储过程 批量修改数据

场景如下 需要更新百万级数据 使用sql 更新会导致 锁表 改用存储过程 批量更新  

经过测试更新50W条数据需要60S左右

-- 删除已存的存储过程
DROP PROCEDURE if EXISTS update_xc_userinfoeast;

-- 定义存储过程
DELIMITER &&
CREATE PROCEDURE update_xc_userinfoeast()
BEGIN
DECLARE done int DEFAULT FALSE;
DECLARE count_temp int DEFAULT 0;
DECLARE user_id_temp VARCHAR(32);
DECLARE industry_code_temp VARCHAR(10);
DECLARE vocation_temp VARCHAR(30);
DECLARE company_temp VARCHAR(50);
declare i int DEFAULT 0;
DECLARE cur CURSOR for
SELECT xcu.vocation,xcu.industry_code,xcu.company, ca.user_id
FROM hsit_credit.xc_userocc_info_temp xcu
JOIN hsit_credit.credit_apply ca ON ca.thirdpart_user_id=xcu.cid;
DECLARE CONTINUE HANDLER for not found set done=TRUE;
SET autocommit = 0;
OPEN cur;
read_loop:LOOP
FETCH cur INTO vocation_temp,industry_code_temp,company_temp,user_id_temp;
IF
done THEN
LEAVE read_loop;
END IF;
SET count_temp = count_temp + 1;
update hsit_user.user_info_east set industry_code=industry_code_temp,
industry_code=industry_code_temp,company_name=company_temp WHERE user_id=user_id_temp;
if mod(count_temp,2000)=0 then
commit; END if;
END LOOP;
CLOSE cur;
COMMIT;
SET autocommit = 1;
select CONCAT("update_xc_userinfoeast success ");
END&&
-- 过程结束
DELIMITER;

-- 调用过程
call update_xc_userinfoeast();

DROP PROCEDURE update_xc_userinfoeast;