MySQL触发器开发需求：多表备份、数据校验与分区清理

你的需求不适合用触发器实现——触发器是响应表的增删改操作触发的行/语句级逻辑，而你需要的是定时批量执行的任务。推荐用MySQL存储过程+事件调度器实现，这比外部cron更稳定，且能在数据库内部保证事务一致性。

一、核心思路 #

1. 用存储过程封装所有操作：批量建备份表、数据一致性校验、清理过期分区
2. 存储过程内开启事务，设置异常捕获，任一环节失败则整体回滚
3. 创建MySQL事件，每日自动执行该存储过程

二、完整存储过程实现 #

DELIMITER //

CREATE PROCEDURE batch_table_maintenance()
BEGIN
    -- 关闭自动提交，开启事务
    SET autocommit = 0;
    START TRANSACTION;

    -- 声明异常处理器：出错则回滚并退出
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '执行失败，已回滚所有操作' AS result;
    END;

    -- --------------------------
    -- 1. 批量创建table1-table5的备份表
    -- --------------------------
    DECLARE table_idx INT DEFAULT 1;
    DECLARE current_table VARCHAR(50);
    DECLARE backup_table VARCHAR(50);

    WHILE table_idx <= 5 DO
        SET current_table = CONCAT('table', table_idx);
        SET backup_table = CONCAT(current_table, '_backup');

        -- 先删除已存在的备份表（若需要保留历史备份，可改为TRUNCATE）
        SET @drop_sql = CONCAT('DROP TABLE IF EXISTS ', backup_table);
        PREPARE drop_stmt FROM @drop_sql;
        EXECUTE drop_stmt;
        DEALLOCATE PREPARE drop_stmt;

        -- 创建新的备份表
        SET @create_sql = CONCAT('CREATE TABLE ', backup_table, ' AS SELECT * FROM ', current_table);
        PREPARE create_stmt FROM @create_sql;
        EXECUTE create_stmt;
        DEALLOCATE PREPARE create_stmt;

        SET table_idx = table_idx + 1;
    END WHILE;

    -- --------------------------
    -- 2. 校验原表与备份表的数据一致性
    -- --------------------------
    SET table_idx = 1;
    WHILE table_idx <= 5 DO
        SET current_table = CONCAT('table', table_idx);
        SET backup_table = CONCAT(current_table, '_backup');

        -- 校验总行数
        SET @count_sql = CONCAT(
            'SELECT (SELECT COUNT(*) FROM ', current_table, ') = (SELECT COUNT(*) FROM ', backup_table, ') AS count_match'
        );
        PREPARE count_stmt FROM @count_sql;
        EXECUTE count_stmt INTO @count_match;
        DEALLOCATE PREPARE count_stmt;

        IF @count_match = 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT(current_table, ' 与备份表行数不一致');
        END IF;

        -- 校验表校验和（更严谨的一致性验证）
        SET @checksum_sql = CONCAT(
            'SELECT (SELECT CHECKSUM TABLE ', current_table, ') = (SELECT CHECKSUM TABLE ', backup_table, ') AS checksum_match'
        );
        PREPARE checksum_stmt FROM @checksum_sql;
        EXECUTE checksum_stmt INTO @checksum_match;
        DEALLOCATE PREPARE checksum_stmt;

        IF @checksum_match = 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT(current_table, ' 与备份表数据不一致');
        END IF;

        SET table_idx = table_idx + 1;
    END WHILE;

    -- --------------------------
    -- 3. 清理原表中超出3天的分区（保留当日、昨日、前日）
    -- --------------------------
    SET table_idx = 1;
    WHILE table_idx <= 5 DO
        SET current_table = CONCAT('table', table_idx);

        -- 获取需要删除的分区（partition_date < 当前日期-3天）
        SET @partition_sql = CONCAT(
            'SELECT partition_name FROM INFORMATION_SCHEMA.PARTITIONS ',
            'WHERE table_schema = DATABASE() AND table_name = ''', current_table, ''' ',
            'AND partition_description < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 3 DAY), ''%Y%m%d'')'
        );
        PREPARE partition_stmt FROM @partition_sql;
        EXECUTE partition_stmt;
        DEALLOCATE PREPARE partition_stmt;

        -- 遍历删除分区（这里假设分区名格式为pYYYYMMDD，比如p20230219）
        -- 若你的分区命名规则不同，需调整partition_description的对比逻辑
        SET @drop_part_sql = CONCAT(
            'ALTER TABLE ', current_table, ' DROP PARTITION ',
            (SELECT GROUP_CONCAT(partition_name SEPARATOR ', ') FROM INFORMATION_SCHEMA.PARTITIONS 
             WHERE table_schema = DATABASE() AND table_name = ''', current_table, ''' 
             AND partition_description < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 3 DAY), ''%Y%m%d''))'
        );
        PREPARE drop_part_stmt FROM @drop_part_sql;
        EXECUTE drop_part_stmt;
        DEALLOCATE PREPARE drop_part_stmt;

        SET table_idx = table_idx + 1;
    END WHILE;

    -- 所有操作成功，提交事务
    COMMIT;
    SELECT '所有操作执行成功' AS result;
END //

DELIMITER ;

三、创建每日执行的事件 #

-- 确保MySQL事件调度器已开启
SET GLOBAL event_scheduler = ON;

-- 创建每日凌晨2点执行的事件
CREATE EVENT daily_table_maintenance
ON SCHEDULE EVERY 1 DAY
STARTS DATE_ADD(CURDATE(), INTERVAL 2 HOUR)
DO
CALL batch_table_maintenance();

四、关键注意事项 #

• 权限要求：执行该方案需要EVENT、CREATE ROUTINE、ALTER ROUTINE、DROP、CREATE TABLE等权限
• 分区命名适配：如果你的分区不是以pYYYYMMDD格式命名，需要修改存储过程中分区查询的partition_description对比逻辑
• 性能优化：若表数据量极大，建议在业务低峰期执行（调整事件的执行时间），或拆分备份/校验/清理步骤为独立任务
• DDL事务说明：MySQL中CREATE TABLE AS SELECT属于可事务化的DDL，但DROP TABLE会隐式提交当前事务，因此存储过程中先关闭自动提交再开启事务，确保整体原子性

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