MySQL分区自动化技术咨询：自动清理旧分区并新增数据分区

我完全理解你不想每天手动折腾分区的烦恼——重复删旧建新会消耗不必要的运维精力，下面这个基于MySQL事件调度器+自定义存储过程的方案，能帮你彻底自动化这个流程，一劳永逸解决问题。

第一步：开启MySQL事件调度器 #

默认情况下MySQL事件调度器是关闭的，先确保它处于启用状态：

临时开启（重启后失效） #

SET GLOBAL event_scheduler = ON;

永久开启（修改配置文件，如my.cnf/my.ini） #

在配置文件中添加如下配置，重启MySQL后生效：

event_scheduler = ON

第二步：创建分区管理存储过程 #

这个存储过程会自动完成两个核心动作：

• 删除超过保留期限的旧分区（比如你需要保留最近2个月的数据）
• 预创建未来1-2个月的新区块（避免新数据因无对应分区插入失败）

假设你的表名为your_partitioned_table，分区键是created_at（datetime类型），按月份分区，保留最近2个月，预创建未来1个月的分区：

DELIMITER //

CREATE PROCEDURE manage_partitions()
BEGIN
    DECLARE current_month INT;
    DECLARE keep_until_month INT;
    DECLARE future_month INT;
    DECLARE partition_name VARCHAR(20);
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR 
        SELECT partition_name 
        FROM INFORMATION_SCHEMA.PARTITIONS 
        WHERE table_schema = DATABASE() 
          AND table_name = 'your_partitioned_table'
          AND partition_name NOT IN ('p_default'); -- 排除默认分区（如果已设置）
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 计算时间参数：当前年月、保留截止年月、预创建的未来年月
    SET current_month = YEAR(NOW()) * 100 + MONTH(NOW());
    SET keep_until_month = YEAR(NOW() - INTERVAL 2 MONTH) * 100 + MONTH(NOW() - INTERVAL 2 MONTH);
    SET future_month = YEAR(NOW() + INTERVAL 1 MONTH) * 100 + MONTH(NOW() + INTERVAL 1 MONTH);

    -- 清理旧分区
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO partition_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 从分区名提取年月（假设分区名格式为p202405）
        IF SUBSTRING(partition_name, 2) < keep_until_month THEN
            SET @drop_sql = CONCAT('ALTER TABLE your_partitioned_table DROP PARTITION ', partition_name);
            PREPARE stmt FROM @drop_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END LOOP;
    CLOSE cur;

    -- 预创建未来1个月的分区
    SET partition_name = CONCAT('p', future_month);
    -- 检查分区是否已存在，避免重复创建报错
    SELECT COUNT(*) INTO @exists 
    FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE table_schema = DATABASE() 
      AND table_name = 'your_partitioned_table' 
      AND partition_name = partition_name;
    
    IF @exists = 0 THEN
        -- 计算分区的起始和结束时间（按月份范围）
        SET @start_date = STR_TO_DATE(CONCAT(future_month, '01'), '%Y%m%d');
        SET @end_date = DATE_ADD(@start_date, INTERVAL 1 MONTH);
        SET @add_sql = CONCAT(
            'ALTER TABLE your_partitioned_table ADD PARTITION (',
            'PARTITION ', partition_name, ' VALUES LESS THAN (TO_DAYS(''', @end_date, '''))',
            ')'
        );
        PREPARE stmt FROM @add_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END //

DELIMITER ;

第三步：创建定时事件 #

让存储过程每天在业务低峰期自动运行（比如凌晨2点）：

CREATE EVENT daily_partition_manager
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00' -- 设置第一次运行的具体时间
DO
    CALL manage_partitions();

关键注意事项 #

• 分区命名规范：一定要统一分区命名格式（比如pYYYYMM），这样存储过程才能正确识别并处理分区
• 默认分区：建议给表添加一个默认分区（p_default），用来接收意外落在已有分区范围外的数据，避免插入失败
• 权限要求：执行这些操作需要EVENT权限（创建事件）和ALTER权限（修改表分区），确保你的数据库账号拥有足够权限
• 测试验证：首次配置后，手动调用CALL manage_partitions();测试，检查分区是否正确清理和创建，避免误删数据
• 周期调整：根据业务需求，修改存储过程中的INTERVAL 2 MONTH（保留期限）和INTERVAL 1 MONTH（预创建期限）即可

这个方案配置完成后，就完全自动化了，不需要每天手动执行任何操作，彻底降低运维成本。

内容的提问来源于stack exchange，提问作者Omkar Mozar