MySQL8 之创建维护小时分区表

1, 手工操作

# 创建分区表
CREATE TABLE `t_mr_ftp_file` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`sdate` INT(10) NOT NULL,
	`ip` VARCHAR(20) NULL DEFAULT NULL,
	`prod` VARCHAR(10) NULL DEFAULT NULL,
	`mrtype` VARCHAR(10) NULL DEFAULT NULL,
	`name` VARCHAR(256) NULL DEFAULT NULL,
	`size` BIGINT(20) NULL DEFAULT NULL,
	`create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`, `sdate`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
PARTITION BY RANGE (sdate)
(
PARTITION p2020081923 VALUES LESS THAN (2020082000) ENGINE = InnoDB
)
;

# 查看分区
SELECT * FROM information_schema.partitions WHERE table_schema = 'monitor' AND table_name = 't_mr_ftp_file';

# 新增分区
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082000 VALUES LESS THAN (2020082001) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082001 VALUES LESS THAN (2020082002) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082002 VALUES LESS THAN (2020082003) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082003 VALUES LESS THAN (2020082004) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082004 VALUES LESS THAN (2020082005) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082005 VALUES LESS THAN (2020082006) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082006 VALUES LESS THAN (2020082007) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082007 VALUES LESS THAN (2020082008) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082008 VALUES LESS THAN (2020082009) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082009 VALUES LESS THAN (2020082010) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082010 VALUES LESS THAN (2020082011) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082011 VALUES LESS THAN (2020082012) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082012 VALUES LESS THAN (2020082013) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082013 VALUES LESS THAN (2020082014) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082014 VALUES LESS THAN (2020082015) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082015 VALUES LESS THAN (2020082016) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082016 VALUES LESS THAN (2020082017) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082017 VALUES LESS THAN (2020082018) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082018 VALUES LESS THAN (2020082019) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082019 VALUES LESS THAN (2020082020) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082020 VALUES LESS THAN (2020082021) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082021 VALUES LESS THAN (2020082022) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082022 VALUES LESS THAN (2020082023) ENGINE = INNODB );
alter TABLE `t_mr_ftp_file` add PARTITION( PARTITION p2020082023 VALUES LESS THAN (2020082100) ENGINE = INNODB );

# 删除分区
alter table `t_mr_ftp_file` drop PARTITION p2020081923;

2, 自动维护分区

# 创建存储过程
## 功能，创建明天当前小时的小时分区
DELIMITER $$
#该表所在数据库名称
USE `monitor`$$
DROP PROCEDURE IF EXISTS `create_partition_by_hour`$$
CREATE PROCEDURE `create_partition_by_hour`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    #当前日期存在的分区的个数
    DECLARE ROWS_CNT INT UNSIGNED;
    #目前日期，为当前日期的后一天
    DECLARE TARGET_DATE TIMESTAMP;
    #分区的名称，格式为p2020082100
    DECLARE PARTITIONNAME VARCHAR(11);
    #当前分区名称的分区值上限，即为 PARTITIONNAME + 1
    DECLARE PARTITION_ADD_HOUR VARCHAR(11);
    SET TARGET_DATE = NOW() + INTERVAL 1 DAY;
    SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m%d%H' );
    SET TARGET_DATE = TARGET_DATE + INTERVAL 1 HOUR;
    SET PARTITION_ADD_HOUR = DATE_FORMAT( TARGET_DATE, '%Y%m%d%H' );
    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 (",
            PARTITION_ADD_HOUR ,") 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 ;

# 运行方法
CALL `create_partition_by_hour`('monitor', 't_mr_ftp_file')

# 利用 event 自动创建分区
# 设置为每小时调用一次存储过程create_partition_by_hour
DELIMITER $$
#该表所在的数据库名称
USE `monitor`$$
CREATE EVENT IF NOT EXISTS `event_hourly_generate_partition`
ON SCHEDULE EVERY 1 hour   #执行周期，还有天、月等等
STARTS '2020-08-20 00:00:00' # 本次设置的日期没有意义，但 event 会在设置的几分几秒启动
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions'
DO BEGIN
    #调用刚才创建的存储过程，第一个参数是数据库名称，第二个参数是表名称
    CALL monitor.create_partition_by_hour('monitor','t_mr_ftp_file');
END$$
DELIMITER ;