MySQL 根据时间自动创建分区脚本

以下是一个MySQL脚本示例，用于根据时间自动创建和管理分区表：

— 1. 首先创建一个按时间分区的表（如果尚未存在）
CREATE TABLE IF NOT EXISTS time_partitioned_data (
id INT AUTO_INCREMENT,
data_value VARCHAR(255),
created_at DATETIME NOT NULL,
PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p_min VALUES LESS THAN (TO_DAYS('2023-01-01'))
);

— 2. 创建存储过程来自动管理分区
DELIMITER //
CREATE PROCEDURE auto_manage_partitions(IN table_name VARCHAR(64), IN days_ahead INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE partition_name VARCHAR(64);
DECLARE partition_value VARCHAR(64);
DECLARE max_value DATE;
DECLARE new_partition_date DATE;
DECLARE new_partition_name VARCHAR(64);
DECLARE new_partition_value INT;
DECLARE alter_sql TEXT;

— 获取当前最大分区值
SELECT MAX(TO_DAYS(created_at)) INTO @max_day
FROM time_partitioned_data;

SET max_value = IFNULL(FROM_DAYS(@max_day), CURDATE());

— 创建未来分区
SET new_partition_date = max_value;

WHILE DATEDIFF(DATE_ADD(new_partition_date, INTERVAL 1 MONTH), max_value) <= days_ahead DO
SET new_partition_date = DATE_ADD(new_partition_date, INTERVAL 1 MONTH);
SET new_partition_name = CONCAT('p_', DATE_FORMAT(new_partition_date, '%Y%m'));
SET new_partition_value = TO_DAYS(new_partition_date);

— 检查分区是否已存在
SELECT COUNT(*) INTO @partition_exists
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'time_partitioned_data'
AND PARTITION_NAME = new_partition_name;

IF @partition_exists = 0 THEN
SET alter_sql = CONCAT('ALTER TABLE ', table_name,
' ADD PARTITION (PARTITION ', new_partition_name,
' VALUES LESS THAN (', new_partition_value, '))');
PREPARE stmt FROM alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT CONCAT('Created partition: ', new_partition_name, ' for date: ', new_partition_date) AS message;
END IF;
END WHILE;

— 可选：删除旧分区（例如保留最近12个月的数据）
/*
SELECT PARTITION_NAME, PARTITION_DESCRIPTION INTO @old_partition, @old_value
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'time_partitioned_data'
AND PARTITION_NAME != 'p_min'
ORDER BY PARTITION_DESCRIPTION ASC
LIMIT 1;

IF TO_DAYS(CURDATE()) – @old_value > 365 THEN
SET @drop_sql = CONCAT('ALTER TABLE ', table_name, ' DROP PARTITION ', @old_partition);
PREPARE stmt FROM @drop_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT CONCAT('Dropped old partition: ', @old_partition) AS message;
END IF;
*/
END //
DELIMITER ;

— 3. 创建事件定期执行分区管理
CREATE EVENT IF NOT EXISTS manage_partitions_event
ON SCHEDULE EVERY 1 MONTH
STARTS CURRENT_TIMESTAMP
DO
CALL auto_manage_partitions('time_partitioned_data', 90); — 提前创建未来90天的分区

— 启用事件调度器
SET GLOBAL event_scheduler = ON;