MySQL跨Schema批量创建触发器脚本需求：基于information_schema自动生成

以下是一个可自动生成所需触发器的MySQL存储过程，它会读取基线Schema的表结构，为对应审计表批量创建UPDATE触发器：

DELIMITER //

CREATE PROCEDURE Generate_Audit_Triggers(
    IN p_baseline_schema VARCHAR(64),
    IN p_audit_schema VARCHAR(64)
)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_table_name VARCHAR(64);
    DECLARE v_insert_columns TEXT;
    DECLARE v_old_values TEXT;
    DECLARE v_trigger_sql TEXT;
    
    -- 游标：遍历基线Schema下的所有实体表
    DECLARE table_cursor CURSOR FOR
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = p_baseline_schema
          AND table_type = 'BASE TABLE';
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN table_cursor;
    
    table_loop: LOOP
        FETCH table_cursor INTO v_table_name;
        IF done THEN
            LEAVE table_loop;
        END IF;
        
        -- 拼接审计表的插入列（固定审计列+基线表所有列）
        SELECT GROUP_CONCAT(CONCAT('`', column_name, '`') SEPARATOR ', ')
        INTO v_insert_columns
        FROM information_schema.columns
        WHERE table_schema = p_baseline_schema
          AND table_name = v_table_name;
        
        -- 拼接旧数据值列表（对应基线表列）
        SELECT GROUP_CONCAT(CONCAT('OLD.`', column_name, '`') SEPARATOR ', ')
        INTO v_old_values
        FROM information_schema.columns
        WHERE table_schema = p_baseline_schema
          AND table_name = v_table_name;
        
        -- 组装完整的触发器创建SQL
        SET v_trigger_sql = CONCAT(
            'CREATE TRIGGER AUDIT_UPDATE_old_', v_table_name, ' ',
            'AFTER UPDATE ON ', p_baseline_schema, '.', v_table_name, ' ',
            'FOR EACH ROW ',
            'INSERT INTO ', p_audit_schema, '.', v_table_name, ' ',
            '(`AUDIT_STAMP`, `AUDIT_ACTION`, ', v_insert_columns, ') ',
            'VALUES (CURRENT_TIMESTAMP, "K", ', v_old_values, ');'
        );
        
        -- 执行动态SQL创建触发器
        SET @sql = v_trigger_sql;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
    END LOOP table_loop;
    
    CLOSE table_cursor;
    
END //

DELIMITER ;

使用步骤 #

1. 将存储过程参数p_baseline_schema替换为你的基线Schema名称，p_audit_schema替换为审计Schema名称
2. 调用存储过程：CALL Generate_Audit_Triggers('你的基线Schema名', '你的审计Schema名');
3. 存储过程会自动遍历所有基线表，为对应的审计表创建UPDATE触发器，逻辑与你提供的示例完全匹配：更新后将旧数据插入审计表，同时记录审计时间和操作标识

细节说明 #

• 用游标遍历所有基线实体表，确保无遗漏
• 从information_schema自动读取列名，无需手动维护列列表
• 表名、列名都用反引号包裹，避免与MySQL关键字冲突
• 审计时间默认用CURRENT_TIMESTAMP（含完整日期时间），若需仅保留时间可改回current_time

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