注意點:
(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)) BEGIN declare v_row_count int(11); declare v_sql varchar(200); -- 必須清空臨時表bbs_fromask_importask_tmp select 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`() BEGIN declare 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分鐘