mysql表range分区

按天分区
CREATE TABLE `day` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `create_time` DATETIME NOT NULL COMMENT '入库时间',
  PRIMARY KEY (`id`,`create_time`),
) ENGINE=INNODB AUTO_INCREMENT=4190 DEFAULT CHARSET=utf8 COMMENT='网点系统接口请求日志表'
/*!50500 PARTITION BY RANGE  COLUMNS(create_time)
(PARTITION p20200904 VALUES LESS THAN ('2020-09-05') ENGINE = InnoDB,
 PARTITION p20200905 VALUES LESS THAN ('2020-09-06') ENGINE = InnoDB,
 PARTITION p20200906 VALUES LESS THAN ('2020-09-07') ENGINE = InnoDB,
 PARTITION p20200907 VALUES LESS THAN ('2020-09-08') ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

按月分区
CREATE TABLE `month` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `create_time` DATETIME NOT NULL COMMENT '入库时间',
  PRIMARY KEY (`id`,`create_time`),
) ENGINE=INNODB AUTO_INCREMENT=4190 DEFAULT CHARSET=utf8 COMMENT='网点系统接口请求日志表'
/*!50500 PARTITION BY RANGE  COLUMNS(create_time)
(PARTITION p202008 VALUES LESS THAN ('2020-09-00') ENGINE = InnoDB,
 PARTITION p202009 VALUES LESS THAN ('2020-10-00') ENGINE = InnoDB,
 PARTITION p202010 VALUES LESS THAN ('2020-11-00') ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

查看是否生效EXPLAIN PARTITIONS
EXPLAIN PARTITIONS SELECT * FROM `g_dot_interface_log` WHERE create_time>'2020-09-07' AND create_time<'2020-09-08';
结果:partitions = p20200907

EXPLAIN PARTITIONS SELECT * FROM `g_dot_interface_log_month` WHERE create_time>'2020-10-07' AND create_time<'2020-10-08';
结果:partitions = p202010

新增分区
ALTER TABLE month
ADD PARTITION (
PARTITION p202011 VALUES LESS THAN ('2020-12-00') ENGINE = InnoDB, PARTITION p202012 VALUES LESS THAN ('2021-01-00') ENGINE = InnoDB,
);
删除分区
当删除了一个分区,也同时删除了该分区中所有的数据。
ALTER TABLE month DROP PARTITION p201010;
分区的合并

下面的SQL,将p201001 – p201009 合并为3个分区p2010Q1 – p2010Q3

ALTER TABLE month 
REORGANIZE PARTITION p201001,p201002,p201003,
p201004,p201005,p201006,
p201007,p201008,p201009 INTO
(
PARTITION p2010Q1 VALUES LESS THAN (201004) ENGINE = InnoDB,
PARTITION p2010Q2 VALUES LESS THAN (201007) ENGINE = InnoDB,
PARTITION p2010Q3 VALUES LESS THAN (201010) ENGINE = InnoDB
)