mysql：RANGE分区，按照月份分区

最新推荐文章于 2023-09-13 14:26:45 发布

农舍丶

最新推荐文章于 2023-09-13 14:26:45 发布

阅读量753

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/qq767357802/article/details/127664330

版权

1.表字段id、time设置为联合主键
alter table adv_good_num_data DROP PRIMARY KEY ,ADD PRIMARY KEY (`id`,`time`);

2.执行分区语句（按月分区）
alter table adv_good_num_data
PARTITION BY RANGE(TO_DAYS(`time`))
(
PARTITION p202204 VALUES LESS THAN (TO_DAYS('2022-05-01')),
PARTITION p202205 VALUES LESS THAN (TO_DAYS('2022-06-01')),
       PARTITION p202206 VALUES LESS THAN (TO_DAYS('2022-07-01')),
PARTITION p202207 VALUES LESS THAN (TO_DAYS('2022-08-01')),
PARTITION p202208 VALUES LESS THAN (TO_DAYS('2022-09-01')),
PARTITION p202209 VALUES LESS THAN (TO_DAYS('2022-10-01')),
PARTITION p202210 VALUES LESS THAN (TO_DAYS('2022-11-01')),
PARTITION p202211 VALUES LESS THAN (TO_DAYS('2022-12-01')),
       PARTITION p202212 VALUES LESS THAN (TO_DAYS('2023-01-01')),
PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')),
PARTITION p202305 VALUES LESS THAN (TO_DAYS('2023-06-01')),
       PARTITION p202306 VALUES LESS THAN (TO_DAYS('2023-07-01')),
PARTITION p202307 VALUES LESS THAN (TO_DAYS('2023-08-01')),
PARTITION p202308 VALUES LESS THAN (TO_DAYS('2023-09-01')),
PARTITION p202309 VALUES LESS THAN (TO_DAYS('2023-10-01')),
PARTITION p202310 VALUES LESS THAN (TO_DAYS('2023-11-01')),
PARTITION p202311 VALUES LESS THAN (TO_DAYS('2023-12-01')),
       PARTITION p202312 VALUES LESS THAN (TO_DAYS('2024-01-01')),
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
PARTITION p202405 VALUES LESS THAN (TO_DAYS('2024-06-01')),
       PARTITION p202406 VALUES LESS THAN (TO_DAYS('2024-07-01')),
PARTITION p202407 VALUES LESS THAN (TO_DAYS('2024-08-01')),
PARTITION p202408 VALUES LESS THAN (TO_DAYS('2024-09-01')),
PARTITION p202409 VALUES LESS THAN (TO_DAYS('2024-10-01')),
PARTITION p202410 VALUES LESS THAN (TO_DAYS('2024-11-01')),
PARTITION p202411 VALUES LESS THAN (TO_DAYS('2024-12-01'))
   );