【MySQL】分区操作

1. 创建分区表

CREATE TABLE `tb_table` (
  `record_id` varchar(255) NOT NULL,
  `insert_time` datetime NOT NULL,
  PRIMARY KEY (`record_id`, `insert_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(insert_time)) (
		PARTITION p20231023 VALUES LESS THAN (TO_DAYS('2023-10-24')),
		PARTITION p20231024 VALUES LESS THAN (TO_DAYS('2023-10-25')),
		PARTITION p20231025 VALUES LESS THAN (TO_DAYS('2023-10-26')),
		PARTITION p20231026 VALUES LESS THAN (TO_DAYS('2023-10-27')),
		PARTITION p20231027 VALUES LESS THAN (TO_DAYS('2023-10-28')),
		PARTITION p20231028 VALUES LESS THAN (TO_DAYS('2023-10-29')),
		PARTITION p20231029 VALUES LESS THAN (TO_DAYS('2023-10-30')),
		PARTITION p20231030 VALUES LESS THAN (TO_DAYS('2023-10-31')),
		PARTITION p20231031 VALUES LESS THAN (TO_DAYS('2023-11-01')),
		PARTITION p20231101 VALUES LESS THAN (TO_DAYS('2023-11-02')),
		PARTITION p20231102 VALUES LESS THAN (TO_DAYS('2023-11-03')),
		PARTITION p20231103 VALUES LESS THAN (TO_DAYS('2023-11-04')),
		PARTITION p20231104 VALUES LESS THAN (TO_DAYS('2023-11-05')),
		PARTITION p20231105 VALUES LESS THAN (TO_DAYS('2023-11-06')),
		PARTITION p20231106 VALUES LESS THAN (TO_DAYS('2023-11-07')),
		PARTITION p20231107 VALUES LESS THAN (TO_DAYS('2023-11-08')),
		PARTITION p20231108 VALUES LESS THAN (TO_DAYS('2023-11-09')),
		PARTITION p20231109 VALUES LESS THAN (TO_DAYS('2023-11-10')),
		PARTITION p20231110 VALUES LESS THAN (TO_DAYS('2023-11-11')),
		PARTITION p20231111 VALUES LESS THAN (TO_DAYS('2023-11-12')),
		PARTITION p20231112 VALUES LESS THAN (TO_DAYS('2023-11-13')),
		PARTITION p20231113 VALUES LESS THAN (TO_DAYS('2023-11-14')),
		PARTITION p20231114 VALUES LESS THAN (TO_DAYS('2023-11-15')),
		PARTITION p20231115 VALUES LESS THAN (TO_DAYS('2023-11-16')),
		PARTITION p20231116 VALUES LESS THAN (TO_DAYS('2023-11-17')),
		PARTITION p20231117 VALUES LESS THAN (TO_DAYS('2023-11-18')),
		PARTITION p20231118 VALUES LESS THAN (TO_DAYS('2023-11-19')),
		PARTITION p20231119 VALUES LESS THAN (TO_DAYS('2023-11-20')),
		PARTITION p20231120 VALUES LESS THAN (TO_DAYS('2023-11-21')),
		PARTITION p20231121 VALUES LESS THAN (TO_DAYS('2023-11-22')),
		PARTITION p20231122 VALUES LESS THAN (TO_DAYS('2023-11-23')),
		PARTITION p20231123 VALUES LESS THAN (TO_DAYS('2023-11-24')),
		PARTITION p20231124 VALUES LESS THAN (TO_DAYS('2023-11-25')),
		PARTITION p20231125 VALUES LESS THAN (TO_DAYS('2023-11-26')),
		PARTITION p20231126 VALUES LESS THAN (TO_DAYS('2023-11-27')),
		PARTITION p20231127 VALUES LESS THAN (TO_DAYS('2023-11-28')),
		PARTITION p20231128 VALUES LESS THAN (TO_DAYS('2023-11-29'))
);

2. 查询分区

SELECT * FROM information_schema.PARTITIONS WHERE TABLE_NAME = 'tb_table' and TABLE_SCHEMA = 'SCHEMA';

3. 增加分区

ALTER TABLE tb_table add PARTITION (
    PARTITION p20240102 VALUES LESS THAN (TO_DAYS('2024-01-03'))
);

4. 删除分区

ALTER TABLE tb_table drop PARTITION p20240103;