MySQL Development---Split a summary table into a post table and a reply table using a cursor double-layer nesting

Note the point:

(1) The total table name for the split is different, so create a temporary table, and the data for the gross position table is inserted into the temporary table first

(2) In order to avoid the end of the outermost cursor polling data, throw not found exit the program, do not perform subsequent operations such as closing the cursor, define continue handler, declare continue handler for not found set done1=1 ;

1.1. External stored procedure calls the main stored procedure

CREATE PROCEDURE ' Bbs_split_thread_post_outer ' (in ' in_tabname ' varchar (128))
BEGIN
DECLARE v_row_count int (11);
DECLARE v_sql varchar (200);

--Must empty the temp table 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;


--Emptying the temporary table
TRUNCATE TABLE pre_data_thread_tmp;
TRUNCATE TABLE pre_data_post_tmp;

--to determine whether the original table has data, nothing to do
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; --Can be modified
Select @thread_max_id;

--Open cursor
Open v_cur_thread_pkid;
Repeat
Fetch v_cur_thread_pkid into V_thread_pkid,v_thread_pkid_uni_count;
--Maximum ID increment per fetch 1
If not done1 then
Set @[email protected]_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;
--processing a set of data according to 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 @[email protected]+1;
Until 0 end repeat;
--select Curdate (); Does not perform the reason for defining exit handler
Close V_cur_post_record;
End
End If;
Until Done1 end repeat;
--Close cursor
Close V_cur_thread_pkid;
--select Curdate ();
/*--Importing the final table
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

Summarize:

(1) 30G memory without load, 8488276 rows split into two tables total 10500000 rows spents 39 minutes

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL Development---Split a summary table into a post table and a reply table using a cursor double-layer nesting