mysql对历史数据分区

mysql对历史数据按季度分区
1.先删除原主键
Alter table crm_def_worksheet drop primary key;

2.对原主键和现有主键做联合主键
Alter table crm_def_worksheet add primary key(`id`, `create_date`);

3.执行分区语句
ALTER TABLE crm_def_worksheet PARTITION BY RANGE (to_days(create_date))
(
PARTITION p_2018_06 VALUES LESS THAN (TO_DAYS(‘2018-06-01’)),
PARTITION p_2018_12 VALUES LESS THAN (TO_DAYS(‘2018-12-01’)),

PARTITION p_2019_03 VALUES LESS THAN (TO_DAYS(‘2019-03-01’)),
PARTITION p_2019_06 VALUES LESS THAN (TO_DAYS(‘2019-06-01’)),
PARTITION p_2019_09 VALUES LESS THAN (TO_DAYS(‘2019-09-01’)),
PARTITION p_2019_12 VALUES LESS THAN (TO_DAYS(‘2019-12-01’)),

PARTITION p_2020_03 VALUES LESS THAN (TO_DAYS(‘2020-03-01’)),
PARTITION p_2020_06 VALUES LESS THAN (TO_DAYS(‘2020-06-01’)),
PARTITION p_2020_09 VALUES LESS THAN (TO_DAYS(‘2020-09-01’)),
PARTITION p_2020_12 VALUES LESS THAN (TO_DAYS(‘2020-12-01’)),

PARTITION p_2021_03 VALUES LESS THAN (TO_DAYS(‘2021-03-01’)),
PARTITION p_2021_06 VALUES LESS THAN (TO_DAYS(‘2021-06-01’)),
PARTITION p_2021_09 VALUES LESS THAN (TO_DAYS(‘2021-09-01’)),
PARTITION p_2021_12 VALUES LESS THAN (TO_DAYS(‘2021-12-01’)),

PARTITION p_2022_03 VALUES LESS THAN (TO_DAYS(‘2022-03-01’)),
PARTITION p_2022_06 VALUES LESS THAN (TO_DAYS(‘2022-06-01’)),
PARTITION p_2022_09 VALUES LESS THAN (TO_DAYS(‘2022-09-01’)),
PARTITION p_2022_12 VALUES LESS THAN (TO_DAYS(‘2022-12-01’)),

PARTITION p_2023_03 VALUES LESS THAN (TO_DAYS(‘2023-03-01’)),
PARTITION p_2023_06 VALUES LESS THAN (TO_DAYS(‘2023-06-01’)),
PARTITION p_2023_09 VALUES LESS THAN (TO_DAYS(‘2023-09-01’)),
PARTITION p_2023_12 VALUES LESS THAN (TO_DAYS(‘2023-12-01’)),

PARTITION p_2024_03 VALUES LESS THAN (TO_DAYS(‘2024-03-01’)),
PARTITION p_2024_06 VALUES LESS THAN (TO_DAYS(‘2024-06-01’)),
PARTITION p_2024_09 VALUES LESS THAN (TO_DAYS(‘2024-09-01’)),
PARTITION p_2024_12 VALUES LESS THAN (TO_DAYS(‘2024-12-01’)),

PARTITION p_2025_03 VALUES LESS THAN (TO_DAYS(‘2025-03-01’)),
PARTITION p_2025_06 VALUES LESS THAN (TO_DAYS(‘2025-06-01’)),
PARTITION p_2025_09 VALUES LESS THAN (TO_DAYS(‘2025-09-01’)),
PARTITION p_2025_12 VALUES LESS THAN (TO_DAYS(‘2025-12-01’)),

PARTITION p_max VALUES LESS THAN MAXVALUE );
注意事项，如果生产数据量太大超百万级请备份数据谨慎操作，因数据量太大，重新分区时会产生大量io影响系统正常使用，如主从延迟，锁表时间太长导致插入失败等现象

————————————————
版权声明：本文为CSDN博主「你好龙卷风！！！」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zhaofuqiangmycomm/article/details/116531320