君子如兰1、准备工作
查看MySQL是否开启了事件
show variables like 'event_scheduler';若没有开启事件，则执行以下命令开启事件：
SET GLOBAL event_scheduler = ON;2、创建示例表
新建一个表，主要用于演示。
CREATE TABLE dm_test.tt_msg_info (
id BIGINT(20) NOT NULL,
msg_date DATE NOT NULL,
msg_key VARCHAR(32) NULL,
msg_content LONGTEXT NULL,
create_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, msg_date))
PARTITION BY RANGE(to_days(msg_date)) PARTITIONS 3
(PARTITION p20190101 VALUES LESS THAN (to_days('2019-01-02')),
PARTITION p20190102 VALUES LESS THAN (to_days('2019-01-03')),
PARTITION p20190103 VALUES LESS THAN (to_days('2019-01-04')));3、创建自动删除分区的存储过程
以数据库dm_test为例：
USE dm_test;
DROP procedure IF EXISTS auto_del_partitions;
DELIMITER $$
USE dm_test$$
CREATE PROCEDURE auto_del_partitions(IN c_schema VARCHAR(64), IN c_table VARCHAR(64), IN c_min_partition DATE)
BEGIN
#用于存储需要删除的分区名字
DECLARE p_need_del_partition_name VARCHAR(64);
# 定义循环标识，默认值为FALSE
DECLARE done INT DEFAULT FALSE;
# 定义游标（查询需要删除的分区名称）
DECLARE need_del_partition_cur CURSOR FOR
SELECT PARTITION_NAME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = c_schema
AND TABLE_NAME = c_table
AND PARTITION_DESCRIPTION < TO_DAYS(c_min_partition);
# 将结束标识绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
# 打开游标
OPEN need_del_partition_cur;
#循环开始
REPEAT
FETCH need_del_partition_cur INTO p_need_del_partition_name;
IF NOT done THEN
SET @p_sql_drop_partition = CONCAT('ALTER TABLE ', c_schema, '.', c_table, ' DROP PARTITION ', p_need_del_partition_name);
SELECT @p_sql_drop_partition;
PREPARE drop_partion FROM @p_sql_drop_partition;
EXECUTE drop_partion;
END IF;
UNTIL done END REPEAT;
#循环结束
#关闭游标
CLOSE need_del_partition_cur;
END$$
DELIMITER ;4、创建自动新增分区的存储过程
以数据库dm_test为例：
USE dm_test;
DROP procedure IF EXISTS auto_add_partitions;
DELIMITER $$
USE dm_test $$
CREATE PROCEDURE auto_add_partitions(IN c_schema VARCHAR(64), IN c_table VARCHAR(64),
IN c_partition_reserve_num INT, IN c_partition_add_num INT,
IN c_partition_type INT, IN c_interval INT)
LABEL_END:
BEGIN
DECLARE p_next_day DATE DEFAULT NULL;
DECLARE p_next_month DATE DEFAULT NULL;
DECLARE p_next_year DATE DEFAULT NULL;
DECLARE p_exsit_partition_num INT DEFAULT NULL;
DECLARE p_add_max_partition_description DATE DEFAULT NULL;
DECLARE p_add_min_partition_description DATE DEFAULT NULL;
DECLARE p_del_max_partition_description DATE DEFAULT NULL;
DECLARE p_cur_max_partition_description DATE DEFAULT NULL;
DECLARE p_add_partition_description DATE DEFAULT NULL;
DECLARE p_prev_partition_description DATE DEFAULT NULL;
DECLARE p_add_partition_name VARCHAR(255) DEFAULT NULL;
# 查看表是否有分区
SELECT COUNT(PARTITION_NAME) INTO p_exsit_partition_num FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = c_schema
AND TABLE_NAME = c_table;
# 如果表分区不存在，则打印错误信息并且退出存储过程
IF (p_exsit_partition_num < 1) THEN
SELECT "partition table not is exist" AS "ERROR";
LEAVE LABEL_END;
END IF;
# 从当前时间往前推，要保留的分区数量，默认最少留3个分区
IF (c_partition_reserve_num < 3) THEN
set c_partition_reserve_num = 3;
END IF;
# 从当前时间往后推，要增加的分区数量，默认最少增加3个分区
IF (c_partition_add_num < 3) THEN
SET c_partition_add_num = 3;
END IF;
# 每次增加分区的间隔，默认最少为1
IF (c_interval < 1) THEN
SET c_interval = 1;
END IF;
# 获取当前表的最大的分区描述值
SELECT FROM_DAYS(partition_description) INTO p_cur_max_partition_description
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = c_schema
AND TABLE_NAME = c_table
ORDER BY partition_description DESC
LIMIT 1;
# 从当前时间开始，按天分区模式的最近的1天
SET p_next_day = DATE_ADD(CURDATE(), INTERVAL c_interval DAY);
# 从当前时间开始，按月分区模式的最近的1月
SET p_next_month = STR_TO_DATE(DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL c_interval MONTH), '%Y-%m-%d');
# 从当前时间开始，按年分区模式的最近的1年
SET p_next_year = STR_TO_DATE(DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-01-01'), INTERVAL c_interval YEAR), '%Y-%m-%d');
IF (c_partition_type = 0) THEN
# 按天分区模式，需要自动增加的最大日期
SET p_add_max_partition_description = DATE_ADD(p_next_day, INTERVAL c_interval*c_partition_add_num DAY);
# 按天分区模式，需要自动增加的最小日期
SET p_add_min_partition_description = p_next_day;
# 按天分区模式，需要自动删除的最大日期
SET p_del_max_partition_description = DATE_SUB(p_next_day, INTERVAL c_interval*c_partition_reserve_num DAY);
ELSEIF (c_partition_type = 1) THEN
# 按月分区模式，需要自动增加的最大日期
SET p_add_max_partition_description = DATE_ADD(p_next_month, INTERVAL c_interval*c_partition_add_num MONTH);
# 按月分区模式，需要自动增加的最小日期
SET p_add_min_partition_description = p_next_month;
# 按月分区模式，需要自动删除的最大日期
SET p_del_max_partition_description = DATE_SUB(p_next_month, INTERVAL c_interval*c_partition_reserve_num MONTH);
ELSE
# 按年分区模式，需要自动增加的最大日期
SET p_add_max_partition_description = DATE_ADD(p_next_year, INTERVAL c_interval*c_partition_add_num YEAR);
# 按年分区模式，需要自动增加的最小日期
SET p_add_min_partition_description = p_next_year;
# 按年分区模式，需要自动删除的最大日期
SET p_del_max_partition_description = DATE_SUB(p_next_year, INTERVAL c_interval*c_partition_reserve_num YEAR);
END IF;
IF (p_cur_max_partition_description < p_add_min_partition_description) THEN
# 当前表分区的最大日期比应该自动增加的最小日期还小，则自动增加的开始日期以自动增加的最小日期为准
SET p_add_partition_description = p_add_min_partition_description;
ELSE
# 当前表分区的最大日期比应该自动增加的最小日期还大或者相等，则自动增加的开始日期以当前表分区的最大日期为准
IF (c_partition_type = 0) THEN
SET p_add_partition_description = DATE_ADD(p_cur_max_partition_description, INTERVAL c_interval DAY);
ELSEIF (c_partition_type = 1) THEN
SET p_add_partition_description = STR_TO_DATE(DATE_ADD(DATE_FORMAT(p_cur_max_partition_description, '%Y-%m-01'), INTERVAL c_interval MONTH), '%Y-%m-%d');
ELSE
SET p_add_partition_description = STR_TO_DATE(DATE_ADD(DATE_FORMAT(p_cur_max_partition_description, '%Y-01-01'), INTERVAL c_interval YEAR), '%Y-%m-%d');
END IF;
END IF;
# 循环自动增加分区
WHILE (p_add_partition_description <= p_add_max_partition_description) DO
IF (c_partition_type = 0) THEN
SET p_prev_partition_description = DATE_SUB(p_add_partition_description, INTERVAL c_interval DAY);
SET p_add_partition_name = concat('p', DATE_FORMAT(p_prev_partition_description, '%Y%m%d'));
ELSEIF (c_partition_type = 1) THEN
SET p_prev_partition_description = DATE_SUB(p_add_partition_description, INTERVAL c_interval MONTH);
SET p_add_partition_name = concat('p', DATE_FORMAT(p_prev_partition_description, '%Y%m'));
ELSE
SET p_prev_partition_description = DATE_SUB(p_add_partition_description, INTERVAL c_interval YEAR);
SET p_add_partition_name = concat('p', DATE_FORMAT(p_prev_partition_description, '%Y'));
END IF;
SET @p_sql_add_partition = concat('ALTER TABLE ', c_schema, '.', c_table, ' ADD PARTITION (PARTITION ', p_add_partition_name, ' VALUES LESS THAN (TO_DAYS("', p_add_partition_description, '")))');
SELECT @p_sql_add_partition;
PREPARE add_partion FROM @p_sql_add_partition;
EXECUTE add_partion;
IF (c_partition_type = 0) THEN
SET p_add_partition_description = DATE_ADD(p_add_partition_description, INTERVAL c_interval DAY);
ELSEIF (c_partition_type = 1) THEN
SET p_add_partition_description = DATE_ADD(p_add_partition_description, INTERVAL c_interval MONTH);
ELSE
SET p_add_partition_description = DATE_ADD(p_add_partition_description, INTERVAL c_interval YEAR);
END IF;
END WHILE;
# 删除过期的分区
CALL auto_del_partitions(c_schema, c_table, p_del_max_partition_description);
END$$
DELIMITER ;5、创建每天定时执行的事件
DELIMITER $$
drop event if exists auto_partitions_event $$
create event auto_partitions_event
on schedule every 1 day
starts '2019-01-01 00:00:00'
on COMPLETION PRESERVE
do
BEGIN
CALL auto_add_partitions('dm_test', 'tt_msg_info', 7, 7, 0, 1);
END $$
DELIMITER ;未经允许不得转载：君子如兰 » [原创]MySQL自动增加和删除分区
