Mysql自动按月表分区

很久没有碰DB了。最近需要做一个日志表，因此重新拾了下。实现了下自动按月表分区
开发环境为Mysql 5.7.28

参考资料：
Mysql分区表及自动创建分区Partition（按日）

核心的两个存储过程：
auto_create_partition为创建表分区，调用后为该表创建到下月结束的表分区。
auto_del_partition为删除表分区，方便历史数据空间回收。

DELIMITER $$
DROP PROCEDURE IF EXISTS auto_create_partition$$
CREATE PROCEDURE `auto_create_partition`(IN `table_name` varchar(64))
BEGIN
			SET @next_month:=CONCAT(date_format(date_add(now(),interval 2 month),'%Y%m'),'01');
			SET @SQL = CONCAT( 'ALTER TABLE `', table_name, '`',
					' ADD PARTITION (PARTITION p', @next_month, " VALUES LESS THAN (TO_DAYS(",
							@next_month ,")) );" );
			PREPARE STMT FROM @SQL;
			EXECUTE STMT;
			DEALLOCATE PREPARE STMT;
END$$

DROP PROCEDURE IF EXISTS auto_del_partition$$
CREATE PROCEDURE `auto_del_partition`(IN `table_name` varchar(64),IN `reserved_month` int)
BEGIN
	DECLARE v_finished INTEGER DEFAULT 0;
	DECLARE v_part_name varchar(100) DEFAULT "";
	DECLARE part_cursor CURSOR FOR 
		select partition_name from information_schema.partitions where table_schema = schema()
			and table_name=@table_name and partition_description < TO_DAYS(CONCAT(date_format(date_sub(now(),interval reserved_month month),'%Y%m'),'01'));
	DECLARE continue handler FOR 
		NOT FOUND SET v_finished = TRUE;
	OPEN part_cursor;
read_loop: LOOP
	FETCH part_cursor INTO v_part_name;
	if v_finished = 1 then
		leave read_loop;
	end if;
	SET @SQL = CONCAT( 'ALTER TABLE `', table_name, '` DROP PARTITION ', v_part_name, ";" );
	PREPARE STMT FROM @SQL;
	EXECUTE STMT;
	DEALLOCATE PREPARE STMT;
	END LOOP;
	CLOSE part_cursor;
END$$

DELIMITER ;

下面是示例

-- 假设有个表叫records,设置分区条件为按end_time按月分区
DROP TABLE IF EXISTS `records`;
CREATE TABLE `records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `memo` varchar(128) CHARACTER SET utf8mb4 NOT NULL,
  PRIMARY KEY (`id`,`end_time`)
) 
PARTITION BY RANGE (TO_DAYS(end_time))(
	PARTITION p20200801 VALUES LESS THAN ( TO_DAYS('20200801'))
);

DROP EVENT IF EXISTS `records_auto_partition`;

-- 创建一个Event,每月执行一次，同时最多保存6个月的数据
DELIMITER $$
CREATE EVENT `records_auto_partition`
ON SCHEDULE EVERY 1 MONTH ON COMPLETION PRESERVE
ENABLE
DO
BEGIN
call auto_create_partition('records');
call auto_del_partition('records',6);
END$$
DELIMITER ;

几点注意事项：

• 对于Mysql 5.1以上版本来说，表分区的索引字段必须是主键
• 存储过程中，DECLARE 必须紧跟着BEGIN，否则会报看不懂的错误
• 游标的DECLARE需要在定义声明之后，否则会报错
• 如果是自己安装的Mysql，有可能Event功能是未开启的，在创建Event时会提示错误；修改my.cnf,在 [mysqld] 下添加event_scheduler=1后重启即可。