mysql 存储过程 迁移_Mysql 数据迁移存储过程

过程还是有待改进,适用于数据量不算太庞大的迁移动作, 抛开内在因素,同数据库不同表的数据迁移场景也几乎找不到....

DROP PROCEDURE if exists procedure_transfer_persistent;

CREATE PROCEDURE procedure_transfer_persistent()

BEGIN

DECLARE start_time timestamp DEFAULT current_timestamp();

DECLARE error_num integer DEFAULT 0;

DECLARE warn_num integer DEFAULT 0;

DECLARE no_more_record integer DEFAULT 0;

DECLARE id varchar(36);

DECLARE num integer default 0;

DECLARE cur_record CURSOR FOR SELECT UUID FROM table_1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN

SET error_num = error_num + 1;

SET id = '';

END;

DECLARE CONTINUE HANDLER FOR SQLWARNING

BEGIN

SET warn_num = warn_num + 1;

SET id = '';

END;

OPEN cur_record;

loop_subject:

loop

FETCH cur_record INTO t_uuid;

IF no_more_record = 1 THEN

leave loop_subject;

END IF;

insert into table_2 (col_1, col_2, col_3) /*插表动作*/

select col_1, col_2, col_3 FROM table_1 where col_1 = id;/*查表动作*/

delete from table_1 where col_1 = id; /*删表动作*/

IF num > 10000 /*一万条数据提交一次事物*/

THEN

COMMIT;

SET num = 0;

ELSE

SET num = num + 1;

END IF;

end loop;

COMMIT;

CLOSE cur_record;

# 游标释放

SELECT concat('开始时间:', start_time, ' 完成时间 : ', current_timestamp(), ' 异常:', error_num, '条 警告:', warn_num, '条');

END;