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')),
mysql下载哪个版本好2022PARTITION 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影响系统正常使⽤，如主从延迟，锁表时间太长导致插⼊失败等现象

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。