過程還是有待改進,适用于資料量不算太龐大的遷移動作, 抛開内在因素,同資料庫不同表的資料遷移場景也幾乎找不到....
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;