-- 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);
MySQL建表按月分区
最新推荐文章于 2024-07-24 10:37:31 发布