如何在MySQL中通过触发器实现数据的审计日志记录？

MySQL触发器通过OLD和NEW关键字获取变更前后数据：INSERT仅用NEW，DELETE仅用OLD，UPDATE两者皆可；BEFORE中NEW可修改，OLD始终只读；须显式指定字段如OLD.id，不支持OLD.*；推荐用JSON_OBJECT构造快照。

触发器里怎么拿到变更前后的数据

MySQL 触发器通过 OLD 和 NEW 关键字访问行级变更数据，但必须注意：只有 BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER INSERT 等对应时机才能使用，且不能在 BEFORE INSERT 中读取 OLD（不存在），也不能在 AFTER DELETE 中读取 NEW（已删除）。

常见错误是写成 INSERT INTO audit_log SET old_data = OLD.* —— 这会报语法错误，MySQL 不支持 OLD.* 直接展开。必须显式列出字段，比如：OLD.id、OLD.name。

1. BEFORE UPDATE 可同时读 OLD 和 NEW，适合记录变更对比
2. AFTER INSERT 只能用 NEW，AFTER DELETE 只能用 OLD
3. 如果表字段多，建议用 JSON 构造变更快照：JSON_OBJECT('id', NEW.id, 'name', NEW.name)

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

审计表不能和业务表共用主键或唯一约束，否则插入失败会导致原操作回滚 —— 这是最大陷阱。触发器内写的日志表必须允许重复、允许空值、不依赖业务逻辑约束。

典型错误是给审计表加 UNIQUE(id) 或外键引用原表，结果一删原记录就触发外键冲突，整个事务失败。

1. 字段至少包含：table_name、operation（'INSERT'/'UPDATE'/'DELETE'）、row_id（原表主键值）、old_data（JSON）、new_data（JSON）、created_at、user（可用 CURRENT_USER()）
2. 避免大字段（如 TEXT）做索引；高频写入场景下，created_at 加索引便于按时间查
3. 不要在审计表上建触发器，防止嵌套调用死循环

如何安全地记录当前操作用户

CURRENT_USER() 返回的是连接认证用户（如 '[email protected].%'），不是应用层传来的实际操作人。如果业务系统用统一数据库账号，这个值就没意义。

真正可追溯的操作人信息，必须由应用层显式传入，比如通过 SET @audit_user = 'zhangsan'，再在触发器中读 @audit_user。但要注意变量作用域：会话级变量在事务中有效，跨连接无效。

1. 务必在业务 SQL 前执行 SET @audit_user = 'xxx'，且确保该语句与后续 DML 在同一会话
2. 触发器中要用 IF @audit_user IS NULL THEN ... ELSE ... END IF 做兜底，避免空值导致插入失败
3. 不推荐用 USER()，它返回客户端主机名，容易伪造且格式不稳定

触发器性能和事务风险怎么控制

触发器运行在主事务上下文中，一旦审计写入失败（比如磁盘满、锁超时），整个原始 DML 就会失败。这不是“日志丢了”，而是“业务改不了”。这点必须提前意识到。

没有真正的异步触发器；想解耦只能靠应用层发消息或定时落库，但那就不是触发器方案了。

1. 审计表用 ENGINE=InnoDB，避免 MyISAM 的表级锁拖慢高并发写
2. 避免在触发器里做复杂计算、远程调用、大 JSON 序列化 —— 会显著拖慢主事务响应
3. 测试时一定要模拟高并发更新，观察锁等待和慢查询日志里是否出现触发器相关 INSERT INTO audit_log

最易被忽略的一点：触发器无法捕获批量操作的中间状态，比如 UPDATE t SET x=1 WHERE id IN (1,2,3)，触发器对每一行单独触发，但你无法知道这三条更新是否属于同一个业务动作 —— 审计粒度天然就是行级，没法自动聚合成“一次订单修改”。