mysql游标在数据量超大的时候基本无法使用,解决思路是把数据集按某个值分段分批处理,比如这里是按主键字段id来区分,每十万数据处理一次,把待处理的数据先放到MEMORY 引擎的临时表,每批次处理完后关闭游标,进行下一批次处理。
以下脚本只是大体结构,请按需参考修改。
CREATE PROCEDURE cur_test()
BEGIN
DECLARE v_kfId int(10) unsigned;
DECLARE v_idno char(18);
DECLARE v_name varchar(128) ;
DECLARE v_batch int default 100000;
DECLARE v_startdt datetime;
DECLARE v_all int default 0;
DECLARE done INT DEFAULT FALSE;
DECLARE cur_kf CURSOR FOR
select xxxxxxxxxx from kf_all_init100_tmp t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET SESSION tmp_table_size=1024*1024*1024;
SET SESSION max_heap_table_size=512*1024*1024;
drop TEMPORARY TABLE if EXISTS `kf_all_init100_tmp`;
CREATE TEMPORARY TABLE `kf_all_init100_tmp` (
`id` int(10) unsigned NOT NULL DEFAULT '0',
`idno` varchar(18) DEFAULT NULL,
`name` varchar(128) DEFAULT NULL,
xxxxxxxxxxxxxxx
) ENGINE=MEMORY DEFAULT CHARSET=utf8;
SELECT count(*) into v_all from kf_all_init100;
set v_end=v_all;
set v_begin=v_end-v_batch+1;
while v_end>0 do
set v_startdt=now();
truncate table kf_all_init100_tmp;
insert into kf_all_init100_tmp(id,idno,name,xxxxx)
SELECT id,idno,name,xxxxxxxx from kf_all_init100 t WHERE t.id BETWEEN v_begin and v_end;
OPEN cur_kf;
read_loop: LOOP
FETCH cur_kf INTO xxxxxx;
IF done THEN
LEAVE read_loop;
END IF;
xxxxxxxxxxxxxxxxxxxxxxxx
END LOOP;
CLOSE cur_kf;
insert into deal_log100(startdt,endt,startId,endId,dealNum,sizeNum,allnum)
values(v_startdt,now(),v_begin,v_end,(v_all-v_end)+v_batch,v_batch,v_all);
set done=FALSE;
set v_end=v_begin-1;
set v_begin=v_end-v_batch+1;
end while;
END;