【存储过程】通过存储过程迁移数据

最新推荐文章于 2023-01-05 12:08:21 发布

boyeson

最新推荐文章于 2023-01-05 12:08:21 发布

阅读量2.6k

点赞数

文章标签： mysql 存储过程

本文链接：https://blog.csdn.net/boyeson/article/details/121809508

版权

本文介绍了一种简单方法，通过存储过程将当前表的分区数据迁移到历史表。过程中涉及变量处理、事务控制以确保数据一致性，同时记录迁移耗时和状态，便于监控和调试。该存储过程接受迁移时间、当前表名和历史表名为参数，适用于动态表名或分区的情况。

摘要由CSDN通过智能技术生成

通过存储过程迁移当前表分区数据到历史表

记录另外一种非常简单的方法，来迁移当前表分区数据到历史表。
当表名或表分区是变量时，sql应该如何编写；
存储过程还包含调试信息，如select @transTime，可打印变量值。
存储过程有三个变量，分别是迁移数据时间，当前表名，历史表名。
增加了事务一致性的控制，如果迁移数据异常，则当前表的数据不会被删除。
增加了存储过程的耗时记录和迁移记录，每次迁移都会被记录到表中，可随时观察迁移的数据量，迁移时间，迁移的表名，迁移状态等。

存储过程

CREATE PROCEDURE `transLogMoveProc2`(in beforMonth int, in currentTableName varchar(32), in hisTableName  varchar(32))
BEGIN

-- 需要定义变量
	-- 当次迁移总数
	DECLARE all_count int default 0;
	DECLARE error_status INTEGER DEFAULT 1;
	-- 当次迁移成功标志
	DECLARE execute_status INT 	DEFAULT FALSE;
	declare partName VARCHAR(200);
	declare sqlStmt VARCHAR(200);
	declare sqlStmt2 VARCHAR(200);
	declare sqlStmt3 VARCHAR(200);
	DECLARE diffTime int default 0;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error_status=-1;
	set beginTime = DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S');	
	-- 计算距离当前时间3个月所在月份时间
	set transTime = DATE_SUB(beginTime,INTERVAL beforMonth MONTH);
	-- 计算分区名字
	set partName = CONCAT('P',SUBSTR(DATE_FORMAT(transTime,'%Y%m') FROM 3 FOR 6));
			-- 当次迁移总数
		set @sqlStmt = CONCAT("select count(1) from ", currentTableName, " PARTITION( ",partName, ") into @all_count ;") ;
		PREPARE stmt from @sqlStmt;
		EXECUTE stmt;
		-- 开启事务
		START TRANSACTION;
			-- 当前表分区数据迁移到历史表中
			set @sqlStmt2 = CONCAT("insert into ",hisTableName, " select * from ", currentTableName, " PARTITION(",partName ,");") ;
			-- 删除当前表分区数据
			set @sqlStmt3 = CONCAT("alter table ", currentTableName, " TRUNCATE1 PARTITION  ",partName ,";") ;
			PREPARE stmt2 from @sqlStmt2;
			EXECUTE stmt2;
			PREPARE stmt3 from @sqlStmt3;
			EXECUTE stmt3;
		IF(error_status = -1) THEN
			ROLLBACK;
		ELSE
			COMMIT;
			set execute_status = TRUE;
		END IF;
		set endTime = DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S');
		-- 计算存储过程迁移的耗时
		set diffTime = TIMESTAMPDIFF(SECOND,beginTime,endTime);
		-- 迁移记录表中记录当次记录，迁移总数，迁移开始时间，迁移结束时间。迁移月份等
 		insert into t_move_log(`move_count`,`current_Table`,`his_Table`,`begin_time`,`end_time`,`use_time`, `create_time`, `move_status`) VALUES (@all_count,currentTableName, hisTableName, beginTime, endTime, diffTime,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'), execute_status);
			
	END