程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 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分鐘

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved