mysql 切割（以表分区）

查询建表sql

1

SHOW CREATE TABLE table_name；

其中原建表语句中的LESS THAN (738917) ，其中738917可以通过 SELECT FROM_DAYS(738917); 查询具体的时间。

添加分区

1
2
3
4


ALTER TABLE new_table PARTITION BY RANGE (TO_DAYS(date_column)) (
    PARTITION p1 VALUES LESS THAN (TO_DAYS('2021-01-01')),
    PARTITION p2 VALUES LESS THAN (TO_DAYS('2022-01-01'))
);

导出数据到新表

1
2


INSERT INTO new_table PARTITION (p2019)
SELECT * FROM source_table PARTITION (p2019);

统计分区数据数

1
2


SELECT COUNT(*) FROM new_table PARTITION (p2019);
SELECT COUNT(*) FROM source_table PARTITION (p2019);

对比备份前后分区数据

1

SELECT (SELECT COUNT(*) FROM table1 PARTITION (p2022)) AS source_table_row_count, (SELECT COUNT(*) FROM table2 PARTITION (p2022)) AS new_table_row_count;

删除源表数据

1

ALTER TABLE source_table DROP PARTITION p201901;