mysql 每月自动创建分区

每月自动创建分区逻辑，通过创建一个事件定时任务去执行存储过程
数据表结构，分区字段必须是主键索引中的字段，这里用时间来分

0b67f3b79e08028bc02520de9632c40.png

自动创建分区需要先添加几个分区

#添加分区
ALTER TABLE attribute_data PARTITION by RANGE (UNIX_TIMESTAMP(create_time))
(
    PARTITION p20200701 VALUES LESS THAN (UNIX_TIMESTAMP('2020-07-01 00:00:00')),
    PARTITION p20200801 VALUES LESS THAN (UNIX_TIMESTAMP('2020-08-01 00:00:00')),
    PARTITION p20200901 VALUES LESS THAN (UNIX_TIMESTAMP('2020-09-01 00:00:00')),
    PARTITION p20201001 VALUES LESS THAN (UNIX_TIMESTAMP('2020-10-01 00:00:00')),
    PARTITION p20201101 VALUES LESS THAN (UNIX_TIMESTAMP('2020-11-01 00:00:00')),
    PARTITION p20201201 VALUES LESS THAN (UNIX_TIMESTAMP('2020-12-01 00:00:00'))
)

查看分区

#查看分区
SELECT PARTITION_NAME,SUBPARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.`PARTITIONS`
WHERE TABLE_NAME = 'attribute_data'

添加存储过程

#添加存储过程
DELIMITER $$
 USE `desert_cloud`$$
 DROP PROCEDURE IF EXISTS `create_attribute_data_partition`$$
 CREATE DEFINER=`root`@`localhost` PROCEDURE `create_attribute_data_partition`()
 BEGIN
 /* 事务回滚，其实放这里没什么作用，ALTER TABLE是隐式提交，回滚不了的。*/
     DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
     START TRANSACTION;
 /* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
     SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
     WHERE table_name='attribute_data' ORDER BY partition_ordinal_position DESC LIMIT 1;
      SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 MONTH))+0;
 /* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
     SET @s1=CONCAT('ALTER TABLE attribute_data ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (UNIX_TIMESTAMP (''',DATE(@Max_date),''')))');
     /* 输出查看增加分区语句*/
     SELECT @s1;
     PREPARE stmt2 FROM @s1;
     EXECUTE stmt2;
     DEALLOCATE PREPARE stmt2;
 /* 取出最小的分区的名称，并删除掉 。
     注意：删除分区会同时删除分区内的数据，慎重 */
     /*select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS
     where table_name='tb_3a_huandan_detail' order by partition_ordinal_position limit 1;
     SET @s=concat('ALTER TABLE tb_3a_huandan_detail DROP PARTITION ',@P0_Name);
     PREPARE stmt1 FROM @s;
     EXECUTE stmt1;
     DEALLOCATE PREPARE stmt1; */
 /* 提交 */
     COMMIT ;
  END$$
 DELIMITER;

查看存储过程

#查看存储过程
show procedure status

调用存储过程

#调用存储过程
call create_attribute_data_partition()

添加事件

#添加事件
CREATE EVENT create_attribute_data_even
ON SCHEDULE
EVERY 1 MONTH STARTS '2020-12-01 00:00:00'
DO
CALL create_attribute_data_partition()

停止事件

ALTER EVENT create_attribute_data_even ON COMPLETION PRESERVE DISABLE;