用 Event + 存储过程实现 MySQL 自动添加分区

一、为什么要自动添加分区？

想象一下你有一个记录用户操作的日志表，每天新增上万条数据。如果所有数据都在一张表里：

查询慢
删除旧数据麻烦
备份恢复困难

解决方案：使用“分区表”（Partitioning）

但问题来了：

分区是静态的！比如你只建到 1月31号，2月1号的数据就没地方存了！

所以我们要：自动提前创建未来几天的分区 —— 这就是本文要解决的问题！

二、准备工作

确认你的 MySQL 支持并启用了 event_scheduler（事件调度器）

打开你的 MySQL 客户端（如 Navicat、MySQL Workbench 或命令行），运行：

SHOW VARIABLES LIKE 'event_scheduler';

你会看到类似结果：

Variable_name	Value
event_scheduler	OFF

如果值是 OFF，说明事件功能没开，必须开启它：

SET GLOBAL event_scheduler = ON;

再次检查是否已开启：

SHOW VARIABLES LIKE 'event_scheduler';
-- 正确输出应为：ON

注意：这个设置重启后可能失效。若要永久生效，请修改 my.cnf 配置文件，在 [mysqld] 下添加：
event_scheduler = ON

三、创建一个带分区的测试表

我们来创建一个叫 user_log 的日志表，按 日期字段 create_time 每天一个分区。

1. 创建数据库（可选）

CREATE DATABASE IF NOT EXISTS test_partition;
USE test_partition;

2. 创建分区表

CREATE TABLE user_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p20240101 VALUES LESS THAN (TO_DAYS('2024-01-02')),
    PARTITION p20240102 VALUES LESS THAN (TO_DAYS('2024-01-03'))
);

解释：

TO_DAYS(create_time)：把日期转成数字（例如 2024-01-01 → 739252）
RANGE 分区：根据数值范围划分
当前只有两天分区（2024-01-01 和 2024-01-02）

现在这张表只能接收这两天的数据。超过就会报错！

我们要做的就是：每天自动加一个新的分区，比如 p20240103, p20240104…

四、编写存储过程：自动添加未来 N 天的分区

我们将写一个通用的存储过程，它可以：

自动找出当前最后一个分区
计算接下来需要创建哪些天的分区
动态执行 SQL 添加这些分区

1. 设置分隔符（重要！因为里面有分号）

DELIMITER $$

含义：告诉 MySQL 把结束符从 ; 改成 $$，这样内部的 ; 不会提前结束定义。

2. 创建存储过程

DROP PROCEDURE IF EXISTS AddPartitionsForLogTable$$

CREATE PROCEDURE AddPartitionsForLogTable(
    IN schema_name VARCHAR(64),       -- 数据库名，比如 'test_partition'
    IN table_name   VARCHAR(64),      -- 表名，比如 'user_log'
    IN future_days  INT               -- 提前创建多少天的分区（建议7）
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE max_partition_value INT;   -- 最大分区对应的 TO_DAYS 值
    DECLARE next_date DATE;            -- 下一个要创建分区的起始日期
    DECLARE new_partition_name VARCHAR(64); -- 新分区名，如 p20240103
    DECLARE new_partition_value INT;   -- 新分区上限值（TO_DAYS(次日)）

    -- 用于拼接 SQL 语句
    DECLARE sql_stmt TEXT;

    -- 定义游标：查询当前最大的分区边界值
    DECLARE cur CURSOR FOR
        SELECT MAX(CAST(PARTITION_DESCRIPTION AS UNSIGNED))
        FROM information_schema.PARTITIONS
        WHERE TABLE_SCHEMA = schema_name
          AND TABLE_NAME = table_name
          AND PARTITION_NAME IS NOT NULL;

    -- 如果没查到数据，设 done=TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 开始事务（安全操作）
    START TRANSACTION;

    -- 打开游标获取最大分区值
    OPEN cur;
    FETCH cur INTO max_partition_value;
    CLOSE cur;

    -- 判断是否有现有分区
    IF done THEN
        -- 没有分区 → 从今天开始建
        SET next_date = CURDATE();
    ELSE
        -- 有分区 → 从“最后一天+1”开始建
        SET next_date = DATE_ADD(FROM_DAYS(max_partition_value), INTERVAL 1 DAY);
    END IF;

    -- 循环添加 future_days 天内的分区
    WHILE DATEDIFF(next_date, CURDATE()) < future_days DO
        -- 生成分区名：pYYYYMMDD 格式
        SET new_partition_name = DATE_FORMAT(next_date, 'p%Y%m%d');

        -- 分区上限 = 明天的 TO_DAYS 值
        SET new_partition_value = TO_DAYS(DATE_ADD(next_date, INTERVAL 1 DAY));

        -- 拼接 ALTER TABLE ADD PARTITION 语句
        SET sql_stmt = CONCAT(
            'ALTER TABLE `', schema_name, '`.`', table_name, '` ',
            'ADD PARTITION (',
            'PARTITION ', new_partition_name, 
            ' VALUES LESS THAN (', new_partition_value, ')',
            ')'
        );

        -- 执行动态 SQL
        SET @sql = sql_stmt;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 时间递增一天
        SET next_date = DATE_ADD(next_date, INTERVAL 1 DAY);
    END WHILE;

    -- 提交事务
    COMMIT;

END$$

3. 恢复默认分隔符

DELIMITER ;

4.存储过程调用参数说明：

参数	示例值	说明
`schema_name`	`'test_partition'`	你要操作的数据库名（字符串加引号）
`table_name`	`'user_log'`	要加分区的表名
`future_days`	`7`	提前创建未来几天的分区

五、测试存储过程是否正常工作

先手动调用一次看看效果：

CALL AddPartitionsForLogTable('test_partition', 'user_log', 7);

然后查看现在的分区情况：

SELECT 
    PARTITION_NAME, 
    PARTITION_DESCRIPTION, 
    TABLE_ROWS 
FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA = 'test_partition' 
  AND TABLE_NAME = 'user_log'
ORDER BY PARTITION_NAME;

你应该能看到类似这样的结果：

PARTITION_NAME	PARTITION_DESCRIPTION	TABLE_ROWS
p20240101	739253	0
p20240102	739254	0
p20240103	739255	NULL
p20240104	739256	NULL
…	…	…
p20240108	739260	NULL

成功！已经自动创建了未来 7 天的分区！

六、创建 Event：让系统每天自动执行

现在我们让 MySQL 每天凌晨自动调用这个存储过程。

1. 创建 Event

DELIMITER $$

DROP EVENT IF EXISTS AutoAddPartitionsDaily$$

CREATE EVENT AutoAddPartitionsDaily
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURDATE() + INTERVAL 1 DAY, '02:00:00')  -- 明天凌晨2点开始
ON COMPLETION PRESERVE  -- 保留事件（即使执行完也不删除）
ENABLE                  -- 启用状态
COMMENT '每天凌晨2点为 user_log 表添加未来7天的分区'
DO
BEGIN
    CALL AddPartitionsForLogTable('test_partition', 'user_log', 7);
END$$

DELIMITER ;

参数解释：

EVERY 1 DAY：每隔一天执行一次
STARTS ... 02:00:00：每天凌晨2点执行（避开高峰期）
ON COMPLETION PRESERVE：事件不会被自动删除
ENABLE：立即启用
DO BEGIN ... END：要执行的操作

2. 查看 Event 是否创建成功

SHOW EVENTS FROM test_partition;

你应该看到：

Db	Name	Status	Schedule	Starts	Ends	OnCompletion	SqlStatement
test_partition	AutoAddPartitionsDaily	ENABLED	EVERY 1 DAY	2024-01-02 02:00:00	NULL	PRESERVE	CALL AddPartitionsForLogTable(…)

状态为 ENABLED 表示一切就绪！

七、验证自动化是否成功

你可以做以下几件事来验证：

1.方法1：

等待明天凌晨2点自动运行（最真实）

或者……

2.方法2：

手动触发 Event 测试

CALL AddPartitionsForLogTable('test_partition', 'user_log', 7);

再次查询分区列表，确认是否有新的分区加入。

八、进阶建议（可选）

1. 添加日志记录功能（推荐）

创建一个日志表，记录每次添加分区的情况：

CREATE TABLE partition_maintenance_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operation VARCHAR(100),
    details TEXT,
    exec_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

然后修改存储过程，在关键位置插入日志：

INSERT INTO partition_maintenance_log(operation, details)
VALUES ('Add Partition', CONCAT('Added partition up to ', next_date));

2. 删除过期分区（清理历史数据）

你也应该定期删除超过 X 天的旧分区（比如保留30天）：

ALTER TABLE user_log DROP PARTITION p20240101;

也可以写另一个 Event 来自动完成。

九、常见问题与注意事项

问题	解决办法
`ERROR 1569 (HY000): Too many partitions`	MySQL 5.7 单表最多支持 1024 个分区，注意不要无限添加
`Cannot execute statement: impossible to write to binary log`	使用`ROW`格式的 binlog 或设置`binlog_format=ROW`
分区名重复导致错误	当前逻辑不会重复添加（基于最大分区推算），但如果手工删过需注意
存储过程权限不足	用户需有`ALTER`,`CREATE`,`EXECUTE`权限

整个流程就像给你的数据库装了一个“智能闹钟”，每天早上自动准备第二天要用的“抽屉”（分区），再也不用手动维护！

参考资料

MySQL 官方文档 - Partitioning
Events in MySQL
TO_DAYS() 函数说明：将日期转为整数天数