添加分区
1
| ALTER TABLE table_name ADD PARTITION( PARTITION partition_name VALUES LESS THAN (1672502400000) );
|
删除分区
1
| ALTER TABLE table_name DROP PARTITION partition_name;
|
查看表分区
1
| SELECT TABLE_NAME, COUNT(*) AS CNT FROM information_schema.PARTITIONS WHERE PARTITION_NAME IS NOT NULL GROUP BY TABLE_NAME ORDER BY CNT DESC LIMIT 50
|
自动创建分区
原理：利用存储过程+定时任务实现自动创建分区
auto_create_partition为创建表分区，调用后为该表创建到下月结束的表分区
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| DELIMITER $$
DROP PROCEDURE IF EXISTS auto_create_partition$$
CREATE PROCEDURE `auto_create_partition`(IN `table_name` varchar(64))
BEGIN
SET @next_month:=CONCAT(date_format(date_add(now(),interval 2 month),'%Y%m'),'01');
SET @time:=CONCAT(UNIX_TIMESTAMP(@next_month),'000');
SET @SQL = CONCAT( 'ALTER TABLE `', table_name, '`',
' ADD PARTITION (PARTITION p', @next_month, " VALUES LESS THAN (",
@time ,") );" );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END$$
DELIMITER $$
CREATE EVENT `record_auto_partition`
ON SCHEDULE EVERY 1 MONTH ON COMPLETION PRESERVE
ENABLE
DO
BEGIN
call auto_create_partition('record');
END$$
|