存储过程-按月创建分区

DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `create_partition_by_month`$$
CREATE PROCEDURE `create_partition_by_month`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    DECLARE ROWS_CNT INT UNSIGNED;
    DECLARE TARGET_DATE TIMESTAMP;
    DECLARE PARTITIONNAME VARCHAR(9);
    DECLARE PARTITION_ADD VARCHAR(9);
    
    SET TARGET_DATE = NOW() + INTERVAL 1 MONTH;
    SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m' );
    SET TARGET_DATE = TARGET_DATE + INTERVAL 1 MONTH;
    SET PARTITION_ADD = DATE_FORMAT( TARGET_DATE, '%Y-%m' );
    
    SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
       
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
        ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (TO_DAYS(''',
            PARTITION_ADD ,'-01'')) ENGINE = InnoDB);' );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
     ELSE
       SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
     END IF;
END$$

存储过程-按年创建分区

DELIMITER $$
#指定schema
USE `test`$$
DROP PROCEDURE IF EXISTS `create_partition_by_year`$$
#IN_SCHEMANAME 参数schema
#IN_TABLENAME 参数表名
CREATE PROCEDURE `create_partition_by_year`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    DECLARE ROWS_CNT INT UNSIGNED;
    DECLARE TARGET_DATE TIMESTAMP;
    DECLARE PARTITIONNAME VARCHAR(9);
    DECLARE PARTITION_ADD_DAY VARCHAR(9);
    
    SET TARGET_DATE = NOW() + INTERVAL 1 YEAR;
    SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y' );
    SET TARGET_DATE = TARGET_DATE + INTERVAL 1 YEAR;
    SET PARTITION_ADD_DAY = DATE_FORMAT( TARGET_DATE, '%Y' );
    
    #查询目标分区数，如名为p2020的分区数
    SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
    
    #分区数为0，则增加新分区
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
        ' ADD PARTITION (PARTITION ', PARTITIONNAME, " VALUES LESS THAN (",
            PARTITION_ADD_DAY ,") ENGINE = InnoDB);" );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
     ELSE
       SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
     END IF;
END$$

#定时任务，每年运行一次

DELIMITER $$
#该表所在的数据库名称
USE `test`$$
DROP EVENT IF EXISTS `call_create_partition_by_year`$$
CREATE EVENT `call_create_partition_by_year`
#每年调用一次
ON SCHEDULE EVERY 1 year
#从'2020-12-24 22:00:00'开始运行一次
STARTS '2020-12-24 22:00:00'
#未指定ENDS
#任务创建后
ON COMPLETION PRESERVE ENABLE
COMMENT '每年调用一次create_partition_by_year'
DO BEGIN
	call test.create_partition_by_year('test','tablename');
END$$
DELIMITER ;

#永久启用mysql的事件

[mysqld]
#修改my.ini或my.cnf，增加
event_scheduler=ON