mysql中用于数据迁移存储过程分享

delimiter $$
use `servant_591up`$$
drop procedure if exists `sp_move_data`$$
create procedure `sp_move_data`()
begin
declare v_exit int default 0;
declare v_spid bigint;
declare v_id bigint;
declare i int default 0;
declare c_table int;
declare v_uniquekey varchar(57);
declare v_tagcatalogid int;
declare v_rootcatalogid int;
declare v_userid bigint;
declare v_questionid char(36);
declare v_correctcount int;
declare v_errorcount int;
declare v_lastiscorrect int;
declare v_lastanswerxml text charset utf8;
declare v_totalcosttime int;
declare v_reviews varchar(200) charset utf8;
declare v_answerresultcategory int;
declare v_lastcosttime int;
declare v_lastanswertime datetime;
declare v_ispublic int;
declare v_subject int;
declare v_totalcount int;
declare v_answermode smallint(6);
declare v_exerciseweight float;
declare c_ids cursor for select uniquekey,tagcatalogid,rootcatalogid,userid,questionid,correctcount,errorcount,lastiscorrect,lastanswerxml,totalcosttime,reviews,answerresultcategory,lastcosttime,lastanswertime,ispublic,subject,totalcount,answermode,exerciseweight from ol_answerresult_56;
declare continue handler for not found set v_exit=1;
open c_ids;
repeat
fetch c_ids into v_uniquekey,v_tagcatalogid,v_rootcatalogid,v_userid,v_questionid,v_correctcount,v_errorcount,v_lastiscorrect,v_lastanswerxml,v_totalcosttime,v_reviews,v_answerresultcategory,v_lastcosttime,v_lastanswertime,v_ispublic,v_subject,v_totalcount,v_answermode,v_exerciseweight;
if v_exit = 0 then
set @vv_id = v_id;
select mod(v_userid,100) into c_table;
set @sql_context =
concat('insert into new_answerresult_',
c_table,'
(uniquekey,tagcatalogid,rootcatalogid,userid,questionid,correctcount,errorcount,lastiscorrect,lastanswerxml,totalcosttime,reviews,answerresultcategory,lastcosttime,lastanswertime,ispublic,subject,totalcount,answermode,exerciseweight)values(',
'''',v_uniquekey,'''',',',
v_tagcatalogid,',',
v_rootcatalogid,',',
v_userid,',',
'''',v_questionid,'''',',',
v_correctcount,',',
v_errorcount,',',
v_lastiscorrect,',',
'''',v_lastanswerxml,'''',',',
v_totalcosttime,',',
'''',replace(ifnull(v_reviews,''),'''',''),'''',',',
v_answerresultcategory,',',
v_lastcosttime,',',
'''',v_lastanswertime,'''',',',
v_ispublic,',',
v_subject,',',
v_totalcount,',',
v_answermode,',',
v_exerciseweight,')');
prepare stmt from @sql_context;
execute stmt ;
deallocate prepare stmt;
end if;
set i=i+1;
#100
#if mod(i,100)=0 then commit;
#end if;
until v_exit=1
end repeat;
close c_ids;
#commit;
end$$
delimiter ;