mysql动态分区_MySQL动态创建分区
按日期分区的数据表，我们希望每年、每个月甚至每天动态创建一个分区，这种情况就需要用事件和存储过程来实现动态添加分区，下面的存储过程是按年分区增加当年分区的过程：
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
按日期分区的数据表，我们希望每年、每个月甚至每天动态创建一个分区，这种情况就需要用事件和存储过程来实现动态添加分区，下面的存储过程是按年分区增加当年分区的过程： 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