如何在MySQL中利用触发器（Trigger）实现自动化的DML审计？-mysql教程-PHP中文网

AFTER触发器+手建审计表是最稳最可控的审计落地方式，因其在DML提交后执行，确保日志与实际数据一致；审计表需极简设计，仅含table_name、pk_value、old_data、new_data等通用字段，禁用外键和JSON索引。

如何在mysql中利用触发器（trigger）实现自动化的dml审计？

AFTER触发器 + 手建审计表，是当前最稳、最可控的落地方式

为什么必须用AFTER而不是BEFORE做审计

核心就一条：审计要记录“事实发生了什么”，不是“用户想干什么”。BEFORE触发器在事务提交前执行，如果后续SQL报错或ROLLBACK，你日志里记的变更根本没发生——这会导致审计日志和实际数据不一致。

AFTER触发器在DML操作已成功提交（或已明确回滚）后才执行，OLD和NEW里的值就是最终落库的状态。哪怕业务层手动ROLLBACK，AFTER也不会触发，天然防误记。

AFTER INSERT中NEW.id已确定（包括自增ID），可安全写入审计表
AFTER UPDATE里OLD.col和NEW.col都可用，且值真实生效
AFTER DELETE中OLD仍完整可用，而INSERT中OLD根本不存在，别硬读
注意：AFTER不能用SIGNAL阻止主操作，但这对审计反而是优点——避免审计逻辑拖垮业务一致性

审计表结构怎么设计才不翻车

别用CREATE TABLE audit_log LIKE origin_table，这是新手第一大坑。抄过来的主键、外键、默认值、索引全都不适配审计场景。

正确做法是极简手建，字段只保留通用元信息：

kashen-mysql DDLtoExcel

解析 MySQL 8.0 的 DDL 脚本，并将表结构导出为 Excel 格式。适用于用户需将 MySQL 的 `CREATE TABLE` 语句转换为结构化的 Excel 文件场景，导出内容包含字段名、数据类型、长度及精度等信息。支持多张表的批量处理，每张表对应 Excel 中的一个独立工作表。适用于数据库文档编写、数据字典生成及模式分析等任务。

下载

table_name VARCHAR(64)：被审计的表名，别用ENUM——加新表就得改DDL
pk_value VARCHAR(255)：主键值统一转字符串存，兼容INT/UUID/复合主键
old_data JSON和new_data JSON：MySQL 5.7+ 必须用JSON类型，否则JSON_OBJECT()构造失败；TEXT拼接易注入、难解析
operator VARCHAR(128)：用SUBSTRING_INDEX(USER(), '@', 1)截取用户名，CURRENT_USER()返回的是授权账号，USER()才是真实连接账号
created_at DATETIME(3)：固定用NOW(3)，别用CURRENT_TIMESTAMP——语义模糊，部分版本行为不一致
绝对不要加外键、不要对old_data/new_data建索引（JSON字段无法高效索引内容）

触发器里最容易踩的三个硬坑

写完一测就报错？大概率掉进下面这三个坑里：

在AFTER UPDATE触发器里又去UPDATE原表（比如更新一个日志状态字段），MySQL直接报Can't update table 'xxx' in stored function/trigger——触发器不能修改正在被触发的表
UPDATE触发器没加字段比对，IF OLD.email != NEW.email THEN ...漏了这句，只要行被UPDATE就记日志，哪怕所有字段都没变，日志量爆炸
INSERT触发器里写了OLD.id，报Unknown column 'OLD.id' in 'field list'——INSERT没有OLD，只认NEW
敏感字段（如password、id_card）没显式排除，直接JSON_OBJECT('password', NEW.password)，审计表里明文存密，合规直接挂掉

一个能直接跑通的UPDATE审计触发器示例

假设对users表审计name、email、status三个字段，只记录有变更的字段：

DELIMITER $$
CREATE TRIGGER users_after_update_audit
AFTER 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, pk_value, op_type,
      old_data, new_data, operator, created_at
    ) VALUES (
      'users',
      CAST(NEW.id AS CHAR),
      'UPDATE',
      JSON_OBJECT(
        'name', OLD.name,
        'email', OLD.email,
        'status', OLD.status
      ),
      JSON_OBJECT(
        'name', NEW.name,
        'email', NEW.email,
        'status', NEW.status
      ),
      SUBSTRING_INDEX(USER(), '@', 1),
      NOW(3)
    );
  END IF;
END$$
DELIMITER ;

注意CAST(NEW.id AS CHAR)确保主键转字符串，IF判断避免无变更写日志，JSON_OBJECT构造安全且可读性强。

真正麻烦的从来不是写第一个触发器，而是给几十张表挨个建、还要确保每张表的主键类型和敏感字段处理逻辑一致——这部分没法偷懒，漏一张表，审计链就断一环。