mysql对历史数据分区

mysql对历史数据按季度分区

1.先删除原主键，并对原主键和现有主键做联合主键
 

ALTER TABLE `crm_def_worksheet `
MODIFY COLUMN `create_date`  datetime NOT NULL AFTER `AGENT_NAME`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `create_date`);



2.执行分区语句
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影响系统正常使用，如主从延迟，锁表时间太长导致插入失败等现象

查看分区是否成功：

select partition_name part,partition_expression expr,partition_description descr,table_rows
  from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA=SCHEMA() AND TABLE_NAME= '表名';