如何在MySQL触发器中传递动态表名与列名至日志表记录变更

要实现修改表记录时自动记录变更信息到日志表，你可以通过AFTER UPDATE触发器逐个字段对比新旧值，将差异信息插入日志表。以下是具体实现步骤：

1. 确认日志表结构 #

假设你的日志表（比如命名为data_change_log）包含以下核心字段（如果你的表结构不同，只需调整INSERT语句中的字段对应关系即可）：

CREATE TABLE data_change_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(64) NOT NULL, -- 被修改的表名
    column_name VARCHAR(64) NOT NULL, -- 被修改的列名
    old_value TEXT, -- 修改前的值
    new_value TEXT, -- 修改后的值
    record_id INT NOT NULL, -- 被修改记录的主键ID
    change_time DATETIME DEFAULT CURRENT_TIMESTAMP, -- 变更时间
    operator VARCHAR(64) DEFAULT SESSION_USER() -- 操作人（数据库用户）
);

2. 为目标表创建UPDATE触发器 #

以目标表customer（包含id、name、phone、address字段）为例，创建触发器：

DELIMITER //
CREATE TRIGGER tr_customer_after_update
AFTER UPDATE ON customer
FOR EACH ROW
BEGIN
    -- 处理name字段变更
    IF (OLD.name IS NULL AND NEW.name IS NOT NULL) 
       OR (OLD.name IS NOT NULL AND NEW.name IS NULL) 
       OR OLD.name <> NEW.name THEN
        INSERT INTO data_change_log (table_name, column_name, old_value, new_value, record_id)
        VALUES ('customer', 'name', OLD.name, NEW.name, OLD.id);
    END IF;

    -- 处理phone字段变更
    IF (OLD.phone IS NULL AND NEW.phone IS NOT NULL) 
       OR (OLD.phone IS NOT NULL AND NEW.phone IS NULL) 
       OR OLD.phone <> NEW.phone THEN
        INSERT INTO data_change_log (table_name, column_name, old_value, new_value, record_id)
        VALUES ('customer', 'phone', OLD.phone, NEW.phone, OLD.id);
    END IF;

    -- 处理address字段变更
    IF (OLD.address IS NULL AND NEW.address IS NOT NULL) 
       OR (OLD.address IS NOT NULL AND NEW.address IS NULL) 
       OR OLD.address <> NEW.address THEN
        INSERT INTO data_change_log (table_name, column_name, old_value, new_value, record_id)
        VALUES ('customer', 'address', OLD.address, NEW.address, OLD.id);
    END IF;
END //
DELIMITER ;

关键注意事项 #

• NULL值处理：由于NULL <> NULL在MySQL中返回NULL（视为假），所以必须单独判断字段的NULL状态，确保NULL值的变更也能被记录。
• 多表支持：如果需要监控多个表的变更，需为每个表单独创建类似的触发器，只需替换表名和字段判断逻辑即可。
• 操作人扩展：如果需要记录应用层面的用户ID，可在更新数据时通过SET @current_user = 'xxx'设置会话变量，触发器中插入@current_user作为operator字段值。
• 性能考量：每次更新若涉及多个字段变更，会插入多条日志记录，这是符合需求的行为；若需合并单条记录的所有变更，可考虑将变更序列化为JSON存储，但会增加解析成本。

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