Mysql分区表及自动创建分区Partition

Range分区表建表语句如下，其中分区键必须和id构成主键和唯一键

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE `test1` (   `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '自增主键(guid)',   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',   `partition_key` int(8) NOT NULL COMMENT '分区键(格式:yyyyMMdd)',   PRIMARY KEY (`id`,`partition_key`),   UNIQUE KEY `id_UNIQUE` (`id`,`partition_key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PARTITION BY RANGE (partition_key) (PARTITION p0 VALUES LESS THAN (20180619) ENGINE = InnoDB,  PARTITION p20180619 VALUES LESS THAN (20180620) ENGINE = InnoDB,  PARTITION p20180621 VALUES LESS THAN (20180622) ENGINE = InnoDB,  PARTITION p20180622 VALUES LESS THAN (20180623) ENGINE = InnoDB,  PARTITION p20180623 VALUES LESS THAN (20180624) ENGINE = InnoDB);

新增分区

1 2 3

alter TABLE `test1` add PARTITION(  PARTITION p20180629 VALUES LESS THAN (20180630) ENGINE = InnoDB );

删除分区

1	alter table `test1`  drop PARTITION p20180629;

 

Mysql不能自动创建分区，需要使用mysql event事件的方式自动创建分区

1.创建分区的存储过程如下（每次执行先校验当前分区是否存在，如果存在则不处理；不存在则创建）：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

DELIMITER $$ #该表所在数据库名称 USE `demo`$$ DROP PROCEDURE IF EXISTS `create_partition_by_day`$$ CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64)) BEGIN     #当前日期存在的分区的个数     DECLARE ROWS_CNT INT UNSIGNED;     #目前日期，为当前日期的后一天     DECLARE TARGET_DATE TIMESTAMP;     #分区的名称，格式为p20180620     DECLARE PARTITIONNAME VARCHAR(9);     #当前分区名称的分区值上限，即为 PARTITIONNAME + 1     DECLARE PARTITION_ADD_DAY VARCHAR(9);     SET TARGET_DATE = NOW() + INTERVAL 1 DAY;     SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m%d' );     SET TARGET_DATE = TARGET_DATE + INTERVAL 1 DAY;     SET PARTITION_ADD_DAY = DATE_FORMAT( TARGET_DATE, '%Y%m%d' );     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_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 ;

2.数据库定时任务(每小时执行一次)

1 2 3 4 5 6 7 8 9 10 11 12 13 14

DELIMITER $$ #该表所在的数据库名称 USE `demo`$$ CREATE EVENT IF NOT EXISTS `daily_generate_partition` ON SCHEDULE EVERY 1 hour   #执行周期，还有天、月等等 STARTS '2018-06-20 00:00:00' ON COMPLETION PRESERVE ENABLE COMMENT 'Creating partitions' DO BEGIN     #调用刚才创建的存储过程，第一个参数是数据库名称，第二个参数是表名称     CALL datacollectcenter.create_partition_by_day('demo','test1'); END$$ DELIMITER ;