mysql游标的使用以及对分区表的操作

背景概述

需要对一个分区表进行数据备份以及删除操作，用到了游标，以及drop分区表的操作。

解决方案

存储过程代码如下

    DELIMITER $$
#该表所在数据库名称
USE `hmw` 
$$ DROP PROCEDURE
IF
	EXISTS `backup_instances` $$ CREATE DEFINER = `hmw` @`%` PROCEDURE `backup_instances` (i_day_num INT)
	BEGIN
	declare done int;
	declare table_name varchar(25);
  declare partition_name varchar(25);
	declare partition_description varchar(25);
-- 	定义游标找到符合条件的分区表
	DECLARE
		cur CURSOR FOR SELECT
		t.table_name,--分区表名
		t.partition_name, --分区名
		t.partition_description --条件值
	FROM
		INFORMATION_SCHEMA.PARTITIONS t 
	WHERE
		t.table_name = 'HMW_SERVICE_INSTANCE' 
		AND str_to_date(REPLACE ( t.partition_description, "'", ""), '%Y-%m-%d')<SYSDATE() - INTERVAL i_day_num DAY
	ORDER BY
		str_to_date( REPLACE ( t.partition_description, "'", "" ), '%Y-%m-%d' );
		
-- 		游标循环为空标示
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
		
-- 		打开游标并循环
		open cur;
-- 		在循环外先获取一次游标值，防止多循环一次
		fetch cur into table_name,partition_name,partition_description;
		posLoop:LOOP  
-- 		如果游标结束，跳出循环
		IF done=1 THEN    
      LEAVE posLoop;
		END IF; 
		
-- 		将数据插入到备份表中，由于直接使用语句partition()不能拿到变量值，多以采用动态执行
		set @sql = CONCAT('INSERT INTO HMW_SERVICE_INSTANCE_BACKUP SELECT * FROM  HMW_SERVICE_INSTANCE 
		partition(',partition_name,')');
		
		PREPARE distSQL FROM @SQL ;
		EXECUTE distSQL;
		
-- 		清除分区表
		set @sql1 = CONCAT('alter table hmw_service_instance drop partition ',partition_name);
		PREPARE distSQL1 FROM @sql1;
		EXECUTE distSQL1;
--      查询变量值
		select table_name,partition_name,partition_description, @sql,@sql1;
		fetch cur into table_name,partition_name,partition_description;
		END LOOP posLoop;
		CLOSE cur;
		
END $$DELIMITER;

分区表的信息存储在INFORMATION_SCHEMA.PARTITIONS数据表中。这里使用游标的时候要注意，如果直接循环的话，最后找不到结果会报错，所以我们在这里定义了一个done来标记是否结束，如果结束就跳出游标循环，并且第一个数据需要在循环外面获取，因为如果放到循环内就会多循环一次，这样的话就把最后一次的操做执行了两遍。
查询分区表的并插入新表的语句是INSERT INTO HMW_SERVICE_INSTANCE_BACKUP SELECT * FROM HMW_SERVICE_INSTANCE partition(partition_name)
但是并不能直接把变量传进去，他会把传进去的变量名当作字符串去执行，所以这里需要用到动态执行sql，如下：

set @sql = CONCAT('INSERT INTO HMW_SERVICE_INSTANCE_BACKUP SELECT * FROM  HMW_SERVICE_INSTANCE 
	partition(',partition_name,')');
	
	PREPARE distSQL FROM @SQL ;
	EXECUTE distSQL;

删除分区同样也是。alter table hmw_service_instance drop partition partition_name这里的partition_name不能是变量值，代码如下：

set @sql1 = CONCAT('alter table hmw_service_instance drop partition ',partition_name);
	PREPARE distSQL1 FROM @sql1;
	EXECUTE distSQL1;