按日期分区的数据表,我们希望每年、每个月甚至每天动态创建一个分区,这种情况就需要用事件和存储过程来实现动态添加分区,下面的存储过程是按年分区增加当年分区的过程:
BEGIN
#Routine body goes here...
DECLARE currentTime date DEFAULT CURDATE();
DECLARE partName VARCHAR(12);
DECLARE i_part_name VARCHAR(12);
DECLARE i_flag INT DEFAULT 0;
DECLARE currentYear INT DEFAULT 0;
#指针遍历分区名,避免定义重复分区名
DECLARE cur_partition CURSOR for select partition_name part from information_schema.PARTITIONS
where TABLE_SCHEMA = schema() and TABLE_NAME='test';
#此句必须添加,不然会出现死循环,含义是当遍历指针为空后赋值此变量为NULL
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET i_part_name = NULL;
#定义欲创建的分区名,用于遍历匹配已有分区名
SET partName=CONCAT('part_',YEAR(currentTime));
OPEN cur_partition;
FETCH cur_partition into i_part_name;
WHILE (i_part_name IS NOT NULL)
DO
IF i_part_name = partName THEN
SET i_flag = 1;
END IF;
FETCH cur_partition into i_part_name;
END WHILE;
CLOSE cur_partition;
#如果没有创建分区则创建当前年份的分区
IF i_flag = 0 THEN
SET currentYear=YEAR(currentTime) + 1;
SET @v_add_s = CONCAT('ALTER TABLE test ADD PARTITION (PARTITION ',partName,' VALUES LESS THAN (',currentYear,'))');
prepare stmt from @v_add_s;
execute stmt;
deallocate prepare stmt;
END IF;
END