mysql将旧表数据复制到新表 mysql表数据迁移到历史表

MySQL存储过程通过游标循环遍历

• 简单说明
• 存储过程详情
• 调试存储过程

简单说明

日志表迁移，从当前表迁移到历史表。
通过存储过程，实现的逻辑：查询符合迁移条件的记录，放入游标中，通过循环遍历游标，把记录迁移到历史表，同时删除当前表对应的记录。最终记录迁移日志。可针对迁移日志进行监控，已达到对迁移失败记录重新迁移等。

存储过程详情

CREATE PROCEDURE `transLogMoveProc`()
BEGIN

-- 需要定义接收游标数据的变量
  declare a_id bigint(20);
  declare a_user_id varchar(32) DEFAULT NULL ;
  declare a_user_name varchar(80) DEFAULT NULL ;
  declare a_remark1 varchar(32) DEFAULT NULL ;
  declare a_version varchar(20) DEFAULT NULL ;
  declare a_host_ip varchar(80) DEFAULT NULL;
  declare a_code varchar(32) DEFAULT NULL ;
  declare a_message varchar(100) DEFAULT NULL ;
  declare a_create_time timestamp   DEFAULT NULL;
  declare a_create_by varchar(32) DEFAULT NULL ;
  declare a_modify_time timestamp   DEFAULT NULL ;
  declare a_modify_by varchar(32) DEFAULT NULL ;

	-- 迁移记录数
	DECLARE i_count int default 0;
	-- 提交记录数
	DECLARE c_count int default 0;
	-- 当次迁移总数
	DECLARE all_count int default 0;
	-- 遍历数据结束标志
	DECLARE done INT DEFAULT FALSE;
	-- 当次迁移成功标志
	DECLARE execute_status INT 	DEFAULT FALSE;
	DECLARE beginTime timestamp   DEFAULT NULL ;
	DECLARE endTime timestamp   DEFAULT NULL ;
	DECLARE diffTime int default 0;
	DECLARE bcount int default 0;
	
	DECLARE rs_cursor CURSOR FOR (
	SELECT 
	  id as a_id,
		user_id as a_user_id,
		user_name as a_user_name,
		remark1 as a_remark1,
		version as a_version,
		code as a_code, 
		message as a_message,
		create_time as a_create_time ,
		create_by as a_create_by,
		modify_time as a_modify_time ,
		modify_by as a_modify_by from t_fop_trans_log_test where 
		TIMESTAMPDIFF(MONTH,DATE_FORMAT(tran_date,'%Y-%m-%d %H:%i:%S'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) >= 1);

	-- 将结束标志绑定到游标
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
		set beginTime = NOW();	
			-- 当次迁移总数
		select count(1) into all_count from t_fop_trans_log_test where 
		TIMESTAMPDIFF(MONTH,DATE_FORMAT(tran_date,'%Y-%m-%d %H:%i:%S'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) >= 1;
		select all_count;
		
		-- 打开游标
		OPEN rs_cursor;
		  set AUTOCOMMIT=0;
		  -- 开始循环
		  transLog: LOOP
			  -- 提取游标里的数据
			  FETCH rs_cursor INTO a_id, a_user_id, a_user_name, a_remark1, a_version, a_code, a_message, a_create_time, a_create_by, a_modify_time, a_modify_by;
      
			  -- 声明结束
			  IF done THEN
  				LEAVE transLog;
			  END IF;
			
			-- 循环事件
		  -- 把符合迁移条件的记录迁移到历史表		  
			insert into t_trans_log_his ( `id`,`user_id`, `user_name`, `remark1`, `version`,  `code`, `message`, `create_time`, `create_by`, `modify_time`, `modify_by`) values (a_id, a_user_id, a_user_name, a_remark1, a_version,  a_code, a_message, a_create_time, a_create_by, a_modify_time, a_modify_by);
		  -- 从当前表删除已经迁移的记录
			 DELETE FROM t_trans_log_test where id = a_ID ;
			
			set i_count=i_count + 1;
    	set c_count=c_count + 1;
			-- 每10000条记录提交一次，避免频繁提交io，导致执行缓慢
			IF MOD(c_count,10000)=0 THEN
				COMMIT;
				SELECT c_count;
				SET c_count=0;
			END IF;
		END LOOP apiLog;
		
		COMMIT;
		set endTime = NOW();
	  
		set diffTime = TIMESTAMPDIFF(SECOND,beginTime,endTime);
		select 	beginTime;
	  select 	endTime;
		select 	diffTime;
		select i_count;
		-- 总记录数与迁移记录数相等，标识迁移成功
		if(i_count = all_count) then
			set execute_status = TRUE;
			-- 迁移记录表中记录当次记录，迁移总数，迁移开始时间，迁移结束时间。迁移月份等
 			insert into t_fop_apimove_log(`move_count`,`use_time`, `move_time`, `move_status`) VALUES (i_count, diffTime,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'), execute_status);
			select count(1) into bcount from t_fop_apimove_log;
		END IF;
		if (i_count != all_count) then
			 insert into t_fop_apimove_log(`move_count`,`use_time`, `move_time`, `move_status`) VALUES (i_count, diffTime,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'), execute_status);
			 select cout(1) into bcount from t_fop_apimove_log;
		 end if;
		 COMMIT;
		 select bcount;
		SELECT execute_status;
		-- 关闭游标
		CLOSE rs_cursor;
	END

调试存储过程

写了一大串存储过程脚本后，如何调式存储过程？网上找了比较多的方法是有专门针对mysql的存储过程的客户端dbforgemysql，但是运行调试需要比较高的用户权限，一般正常使用用户是没有权限去调试的。
比较常规的方法是，存储过程中，在你想断点debug的地方，增加变量，然后select @变量 可打印出调试信息。

如：

-- 当次迁移总数
select count(1) into all_count from t_fop_trans_log_test where 
		TIMESTAMPDIFF(MONTH,DATE_FORMAT(tran_date,'%Y-%m-%d %H:%i:%S'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) >= 1;
-- 这里就是调试信息，运行存储过程时，如果前面都没有报错，则可在控制台打印出all_count的值		
select all_count;

同时，在正式环境中，增加迁移记录表，可监控每次迁移是否成功，这点很重要。

-- 总记录数与迁移记录数相等，标识迁移成功
		if(i_count = all_count) then
			set execute_status = TRUE;
			-- 迁移记录表中记录当次记录，迁移总数，迁移开始时间，迁移结束时间。迁移月份等
 			insert into t_move_log(`move_count`,`use_time`, `move_time`, `move_status`) VALUES (i_count, diffTime,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'), execute_status);
			select count(1) into bcount from t_fop_apimove_log;
		END IF;
		if (i_count != all_count) then
			 insert into t_move_log(`move_count`,`use_time`, `move_time`, `move_status`) VALUES (i_count, diffTime,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'), execute_status);
		 end if;