Mysql development --- split the table into a post table and a reply table using two-layer nested cursors _ MySQL

Note: (1) the names of the tables to be split are different. therefore, create a temporary table and insert the data in the table into the temporary table (2) to avoid the end of the outermost layer cursor round-robin data, a notfound is thrown out of the program, and subsequent operations such as closing the cursor are not executed, the following points must be noted:

(1) the table names of the tables to be split are different. therefore, create a temporary table and insert the data in the table into the temporary table first.

(2) in order to avoid the end of data round-robin by the outermost cursor, the not found Program is thrown out, and subsequent operations such as closing the cursor are not executed, the continue handler is defined, declare continue handler for not found set done1 = 1;

1.1. the external stored procedure calls the primary stored procedure

Create procedure 'bbs _ split_thread_post_outer '(IN 'in _ tabname' varchar (128) BEGINdeclare v_row_count int (11); declare v_ SQL varchar (200 ); -- the temporary table sysdate () must be cleared; 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 (); ENDCREATE 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 consumer group by pkid; declare continue handler for not found set done1 = 1; -- empty the temporary table truncate table pre_data_thread_tmp; truncate table pre_data_post_tmp; -- judge whether the original table has data, and do not select count (*) into v_source_count from bbs_fromask_importask_tmp; if v_source_count> 0 thenselect ifnull (max (id), 0) into @ thread_max_id from yaolanbbs. pre_data_thread; -- modify select @ thread_max_id; -- open the cursor open v_cur_thread_pkid; repeatfetch v_cur_thread_pkid into v_thread_pkid, v_thread_pkid_uni_count; -- the maximum id of each fetch is increased from 1if not done1 thenset @ 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; -- process a set of data based on pkid begindeclare v_post_answer text; declare revoke cursor for select t1.answer from nation 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; ###### set the value open v_cur_post_record; repeatfetch into v_post_answer; -- select @ sort; -- select v_post_answer; insert into values (tid, text, sort) values (@ thread_max_id, v_post_answer, @ sort); -- insert into testincrevalues (@ sort, v_post_answer); set @ sort = @ sort + 1; until 0 end repeat; -- select curdate (); the reason for not executing. define exit handlerclose v_cur_post_record; end if; until done1 end repeat; -- close the cursor close v_cur_thread_pkid; -- select curdate (); /* -- import 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

Summary:

(1) if the 30 GB memory has no load, it takes 39 minutes to split 8488276 rows into two tables.