注意点：
（1）进行拆分的总表表名是不同的，所以创建临时表，把总表的数据先插入临时表
（2）为了避免最外层游标轮询数据结束时，抛出not found 退出程序，不会执行关闭游标等后续操作，定义continue handler，declare continue handler for not found set done1=1;
1.1、外部存储过程调用主存储过程
CREATE PROCEDURE `bbs_split_thread_post_outer`(IN `in_tabname` varchar(128))BEGINdeclare v_row_count int(11);declare v_sql varchar(200);– 必须清空临时表bbs_fromask_importask_tmpselect sysdate();truncate table bbs_fromask_importask_tmp;set @sql=concat(‘insert into bbs_fromask_importask_tmp select * from ‘,in_tabname,’;’);prepare stmt from @sql;execute stmt;call bbs_split_thread_post();select sysdate();END
CREATE PROCEDURE `bbs_split_thread_post`()BEGINdeclare v_source_count int(11);declare v_thread_pkid int(11);declare v_thread_pkid_uni_count int(11); declare done1,done2 int default 0;declare v_cur_thread_pkid cursor for select pkid,count(*) from bbs_fromask_importask_tmp group by pkid;declare continue handler for not found set done1=1;– 清空临时表truncate table pre_data_thread_tmp;truncate table pre_data_post_tmp;– 判断原表是否有数据，没有什么也不做select count(*) into v_source_count from bbs_fromask_importask_tmp;if v_source_count>0 then select ifnull(max(id),0) into @thread_max_id from yaolanbbs.pre_data_thread; — 可做修改 select @thread_max_id; — 打开游标 open v_cur_thread_pkid; repeat fetch v_cur_thread_pkid into v_thread_pkid,v_thread_pkid_uni_count; — 每次fetch最大id自增1 if not done1 then set @thread_max_id=@thread_max_id+1; insert into pre_data_thread_tmp(id,title,age,rule,param) select @thread_max_id,t1.qtitle,t1.age,’age’,t1.age from bbs_fromask_importask_tmp t1 where t1.pkid=v_thread_pkid limit 1; — 根据pkid对一组数据进行处理 begin declare v_post_answer text; declare v_cur_post_record cursor for select t1.answer from bbs_fromask_importask_tmp t1 where t1.pkid=v_thread_pkid; declare exit handler for not found close v_cur_post_record; — declare continue handler for not found set done2=1; insert into pre_data_post_tmp(tid,text,sort) select @thread_max_id,t1.qdesc,1 from bbs_fromask_importask_tmp t1 where t1.pkid=v_thread_pkid limit 1; set @sort=2; ######设置值 open v_cur_post_record; repeat fetch v_cur_post_record into v_post_answer; — select @sort; — select v_post_answer; insert into pre_data_post_tmp(tid,text,sort) values(@thread_max_id,v_post_answer,@sort); — insert into testincre values(@sort,v_post_answer); set @sort=@sort+1; until 0 end repeat; — select curdate(); 不会执行的原因，定义exit handler close v_cur_post_record; end; end if; until done1 end repeat; — 关闭游标 close v_cur_thread_pkid; — select curdate(); /*– 导入最终表 insert into pre_data_thread(id,title,create_time,age,rule,param) select id,title,create_time,age,rule,param from pre_data_thread_tmp; insert into pre_data_post(tid,text,sort,create_time) select tid,text,sort,create_time from pre_data_post_tmp;*/end if;END
总结：
（1）30G内存没有负载的情况下，8488276行拆为两个表共10500000行用时39分钟
版权声明：本文为博主原创文章，，未经博主允许不得转载。
获得幸福的二法门是珍惜你所拥有的、遗忘你所没有的
