过程还是有待改进,适用于数据量不算太庞大的迁移动作
抛开内在因素,同数据库不同表的数据迁移场景也几乎找不到…
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;