MySQL表按天分区

qode / 2023-08-24 / 原文

建表语句

要分区的字段必须为主键或联合主键之一

CREATE TABLE `test_partition` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `day` datetime NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`,`day`)
);

插入数据

INSERT INTO test_partition values 
	(1, '2023-07-01 12:30:30', 'zhangsan'), 
	(2, '2023-07-20 12:30:30', 'lisi'), 
	(2, '2023-08-02 12:30:30', 'wangwu');

创建分区

ALTER table test_partition PARTITION BY RANGE COLUMNS(day) (
	PARTITION p20230801 VALUES LESS THAN ('2023-08-01'),
	PARTITION p20230901 VALUES LESS THAN ('2023-09-01')
);

新增分区

ALTER table test_partition ADD PARTITION (
	PARTITION p20231001 VALUES LESS THAN ('2023-10-01'),
	PARTITION p20231101 VALUES LESS THAN ('2023-11-01')
);

删除分区数据

ALTER table test_partition truncate PARTITION p20231101;

删除分区

数据存储在分区上,删除分区的同时也会删除分区上的数据

ALTER table test_partition DROP PARTITION p20231101;

查看分区

SELECT partition_name,partition_description from information_schema.partitions
WHERE table_name = 'test_partition' and table_schema = '**database_name**';

重新定义分区

如果想在中间增加分区的话不能使用ADD PARTITION

ALTER table test_partition ADD PARTITION (
	PARTITION p20230810 VALUES LESS THAN ('2023-08-10')
);
报错:
VALUES LESS THAN value must be strictly increasing for each partition

重新定义分区：在不丢失数据的情况下,重新编辑分区
ALTER TABLE ... REORGANIZE PARTITION

拆分分区

-- 将p20230901分区拆分成p20230810和p20230901分区
ALTER TABLE test_partition REORGANIZE PARTITION p20230901 INTO (
	PARTITION p20230810 VALUES LESS THAN ('2023-08-10'),
	PARTITION p20230901 VALUES LESS THAN ('2023-09-01')
);

合并分区

将多个相邻的RANGE分区合并为一个RANGE分区或多个RANGE分区, 合并后的分区必须和原来的区间覆盖相同

-- 将p20230810和p20230901分区合并为p20230901分区
ALTER TABLE test_partition REORGANIZE PARTITION p20230810,p20230901 INTO (
	PARTITION p20230901 VALUES LESS THAN ('2023-09-01')
);