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_ciPARTITION 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 PRESERVEENABLECOMMENT 'Creating partitions'DO BEGIN    #调用刚才创建的存储过程，第一个参数是数据库名称，第二个参数是表名称    CALL datacollectcenter.create_partition_by_day('demo','test1');END$$DELIMITER ;`