mysql開發之---使用遊標雙層嵌套對總表進行拆分為文章表和回複表，mysql遊標

mysql開發之---使用遊標雙層嵌套對總表進行拆分為文章表和回複表，mysql遊標

注意點：

（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分鐘

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。