MySQL按时间Range分区

查询分区

--查询表分区
select partition_name,
partition_expression,
partition_description,
table_rows
from
information_schema.partitions
where
table_schema = schema() and table_name='table_name';

删除分区

--删除表分区
ALTER TABLE fg_cdr DROP PARTITION p20250101;

创建分区

--创建表分区
ALTER TABLE table_name PARTITION BY RANGE (TO_DAYS(created_at))
(
	PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
	PARTITION p20221221 VALUES LESS THAN (TO_DAYS('2025-01-01')) ENGINE = InnoDB
);

自动化-可以创建存储过程，开启mysql执行计划，每天执行一次,创建当前最大分区加一天的分区

为指定表添加分区存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `ADD_ONE_PARTITION_BY_DATETIME`(IN PARTITION_TABLE_NAME VARCHAR(100))
BEGIN
SELECT REPLACE
	( PARTITION_NAME, 'p', '' ) INTO @PARTITION_NAME
FROM
	INFORMATION_SCHEMA.PARTITIONS 
WHERE
	TABLE_SCHEMA = SCHEMA ( ) 
	AND TABLE_NAME = @PARTITION_TABLE_NAME 
ORDER BY
	PARTITION_ORDINAL_POSITION DESC 
	LIMIT 1;
SET @PARTITION_DATE_NAME = DATE( DATE_ADD( @PARTITION_NAME + 0, INTERVAL 1 DAY ) ) + 0;
SET @PARTITION_DATE_VALUE = DATE( DATE_ADD( @PARTITION_NAME + 0, INTERVAL 2 DAY ) ) + 0;
SET @STATEMENT_SQL = CONCAT( 'ALTER TABLE ', @PARTITION_TABLE_NAME, ' ADD PARTITION (PARTITION p', @PARTITION_DATE_NAME, ' VALUES LESS THAN (TO_DAYS(''', DATE(@PARTITION_DATE_VALUE), ''')))' );
SELECT @STATEMENT_SQL;
PREPARE PREPARE_STATEMENT_SQL FROM	@STATEMENT_SQL;
EXECUTE PREPARE_STATEMENT_SQL;
DEALLOCATE PREPARE PREPARE_STATEMENT_SQL;
END

批量指定哪些表表要创建分区(同类型根据时间字段创建Range分区)

CREATE DEFINER=`root`@`localhost` PROCEDURE `A_AUTO_CREATE_PARTITIONS`()
BEGIN
	DECLARE
	EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
	START TRANSACTION;

	SET @PARTITION_TABLE_NAME = 'table_name1';
	CALL ADD_ONE_PARTITION_BY_CHAR(@PARTITION_TABLE_NAME);

	SET @PARTITION_TABLE_NAME = 'table_name2';
	CALL ADD_ONE_PARTITION_BY_CHAR(@PARTITION_TABLE_NAME);

	COMMIT;
END

开启mysql定时任务即可, 在定时任务中调用: A_AUTO_CREATE_PARTITIONS()