mysql中触发器实现数据审计与历史记录功能_网络技术

MySQL触发器中，BEFORE UPDATE/DELETE用OLD.字段获取旧值，BEFORE INSERT/UPDATE用NEW.字段获取新值；AFTER触发器不可修改NEW，且INSERT中OLD不可用。

触发器里怎么拿到旧数据和新数据

MySQL 的 BEFORE UPDATE 和 BEFORE DELETE 触发器中，用 OLD.字段名 访问修改前/删除前的值；BEFORE INSERT 和 BEFORE UPDATE 中，用 NEW.字段名 访问插入值或更新后的值。注意：AFTER 类型触发器不能修改 NEW（会报错），且 OLD 在 INSERT 中不可用。

常见错误：在 AFTER INSERT 里写 INSERT INTO audit_log SELECT NEW.id, ... 看似可行，但若表有自增主键且未显式指定，NEW.id 在 AFTER 阶段才真正确定——这本身没问题；但若审计表也依赖该 ID 做外键或唯一约束，需确保事务一致性，建议统一用 BEFORE 触发器 + 显式插入逻辑更可控。

审计表结构设计要注意什么

审计表不是原表复制粘贴。典型字段包括：id（自增）、table_name（VARCHAR(64)）、operation（ENUM('INSERT','UPDATE','DELETE')）、old_data 和 new_data（推荐 JSON 类型，MySQL 5.7+ 支持，比拼接字符串安全）、changed_by（从 USER() 或应用层传入的上下文变量获取）、created_at（CURRENT_TIMESTAMP）。

关键点：

old_data/new_data 字段必须设为 JSON 类型，否则无法用 JSON_OBJECT() 直接构造；若用 TEXT，得手写字符串拼接，极易 SQL 注入或格式错乱
不要给审计表加过多索引——高频写入场景下，索引会拖慢主业务操作；按需在 table_name + created_at 或 changed_by + created_at 上建复合索引即可
避免在触发器里调用存储函数处理敏感字段（如脱敏），函数执行开销会叠加到每条 DML 上，影响主流程响应

一个可直接用的 UPDATE 审计触发器示例

假设对 users 表做变更审计，只记录 name、email、status 字段变化：

DELIMITER $$
CREATE TRIGGER users_audit_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF OLD.name != NEW.name OR OLD.email != NEW.email OR OLD.status != NEW.status THEN
    INSERT INTO audit_log (
      table_name,
      operation,
      old_data,
      new_data,
      changed_by,
      created_at
    ) VALUES (
      'users',
      'UPDATE',
      JSON_OBJECT(
        'name', OLD.name,
        'email', OLD.email,
        'status', OLD.status
      ),
      JSON_OBJECT(
        'name', NEW.name,
        'email', NEW.email,
        'status', NEW.status
      ),
      USER(),
      NOW()
    );
  END IF;
END$$
DELIMITER ;

说明：

用 IF 判断具体字段是否变化，避免无意义日志刷屏
没用 CONCAT 拼 JSON，防止字段含单引号、换行等导致 JSON 解析失败
USER() 返回「客户端连接用户@主机」，不是应用层登录用户；如需真实操作人，得让应用在 SQL 前执行 SET @current_user = 'xxx'，触发器里读 @current_user

触发器不适用于高并发或大字段场景

每次 DML 都同步写审计表，等于把一次写变成两次（甚至更多），在 QPS 过千或单条记录超 1MB 的场景下，I/O 和锁竞争会明显抬高主表延迟。这时应考虑替代方案：

用 MySQL 的 BINLOG（row 格式）配合解析工具（如 Maxwell、Canal）异步投递变更到 Kafka，再由消费者写审计库——解耦、可重放、不拖慢主库
应用层 ORM 插件统一拦截 save/update/delete 方法，生成审计日志——控制力强，但需所有业务走同一套 SDK，存在漏埋点风险
触发器仅记录关键字段变更摘要（如“user_id=123 的 status 从 active 变为 inactive”），详细快照由定时任务每日归档——平衡实时性与性能

最容易被忽略的是：触发器里的 INSERT 操作和主 DML 在同一个事务中，一旦审计表写入失败（比如磁盘满、字段超长），整个业务更新会回滚。生产环境务必监控 audit_log 表的磁盘空间和字段长度限制。

# mysql # js # json # 工具 # ai # mysql触发器 # sql # kafka # if # select # enum # 字符串 # delete # 并发 # 异步 # 器里 # 应用层 # 的是 # 器中 # 字段名 # 设为 # 两次 # 要注意 # 报错 # 可直接

相关栏目：【网站优化151355 】【网络推广146373 】【网络技术251813 】【 AI营销90571 】