-- DROP TABLE STUDY_TEST;

-- 表按照RECORDTIME月份分区
CREATE TABLE `STUDY_TEST` (
  `id` INT(8) NOT NULL AUTO_INCREMENT,
  `callnum` VARCHAR(32) COMMENT '',
  `receivenum` VARCHAR(32) COMMENT '',
  `serialno` VARCHAR(64) COMMENT '',
  -- 默认当前时间
  `recordtime` DATETIME default now() COMMENT '',
  `locationtype` VARCHAR(64) COMMENT '',
  `callsts` VARCHAR(4) COMMENT '',
  `channelid` VARCHAR(20) NOT NULL COMMENT '',
  `chkcode` VARCHAR(1) NOT NULL COMMENT '',
  `dyfield1` VARCHAR(128) COMMENT '扩展字段1',
  `dyfield2` VARCHAR(512) COMMENT '扩展字段2',
  `dyfield3` VARCHAR(512) COMMENT '扩展字段3',
   PRIMARY KEY (`id`,`RECORDTIME`),
   KEY `IX_C_TT_SCESITMTI_RECORDTIME` (`RECORDTIME`),
   KEY `IX_C_TT_SCESITMTI_SERIALNO` (`SERIALNO`)
) AUTO_INCREMENT=10000000 COMMENT '通用APP外呼请求表'
PARTITION BY RANGE (TO_DAYS(RECORDTIME)) (

PARTITION p202201 VALUES LESS THAN (TO_DAYS('2022-02-01 00:00:00')),
PARTITION p202202 VALUES LESS THAN (TO_DAYS('2022-03-01 00:00:00')),
PARTITION p202203 VALUES LESS THAN (TO_DAYS('2022-04-01 00:00:00')),
PARTITION p202204 VALUES LESS THAN (TO_DAYS('2022-05-01 00:00:00')),
PARTITION p202205 VALUES LESS THAN (TO_DAYS('2022-06-01 00:00:00')),
PARTITION p202206 VALUES LESS THAN (TO_DAYS('2022-07-01 00:00:00')),
PARTITION p202207 VALUES LESS THAN (TO_DAYS('2022-08-01 00:00:00')),
PARTITION p202208 VALUES LESS THAN (TO_DAYS('2022-09-01 00:00:00')),
PARTITION p202209 VALUES LESS THAN (TO_DAYS('2022-10-01 00:00:00')),
PARTITION p202210 VALUES LESS THAN (TO_DAYS('2022-11-01 00:00:00')),
PARTITION p202211 VALUES LESS THAN (TO_DAYS('2022-12-01 00:00:00')),
PARTITION p202212 VALUES LESS THAN (TO_DAYS('2023-01-01 00:00:00')),

PARTITION p_others VALUES LESS THAN MAXVALUE
);

-- mysql指定分区查询
SELECT * FROM T_C_TT_SCESITMTINFO PARTITION(p202206,p202106);