mysql触发器中如何记录日志_mysql实战示例解析

触发器中insert日志未生效，主因是事务引擎不一致（如日志表用myisam）、嵌套触发器超限、权限或sql_mode限制；应统一用innodb、禁用日志表触发器、加异常处理器，并注意before/after时机对old/new数据的访问差异。

触发器里写 INSERT 日志语句为什么没生效

MySQL 触发器中执行 INSERT INTO log_table 失败，最常见的原因是触发器和日志表在同一个事务中，而日志表用了 MyISAM 引擎（不支持事务），或者用了 InnoDB 但触发器本身因权限、SQL_MODE 或递归限制被静默终止。尤其注意：如果日志表是 InnoDB，且触发器在 AFTER UPDATE 中写日志，而日志语句又触发了另一个触发器（比如日志表上也有 AFTER INSERT），就可能因 innodb_lock_wait_timeout 或 max_sp_recursion_depth 导致中断。

实操建议：

拍我AI

AI视频生成平台PixVerse的国内版本

下载

• 统一用 InnoDB 创建日志表，并显式关闭日志表的触发器（避免嵌套）：

  CREATE TABLE user_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    table_name VARCHAR(64),
    action VARCHAR(10),
    old_data JSON,
    new_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  ) ENGINE=InnoDB;

• 在触发器开头加 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; 防止日志失败拖垮主操作（仅适用于审计类日志，非关键业务流）
• 检查 SHOW VARIABLES LIKE 'log_bin_trust_function_creators';，若为 OFF 且触发器含函数调用，需设为 ON 或用 SET GLOBAL 临时放开

BEFORE vs AFTER 触发器记录变更数据的区别

记录“改了什么”，关键看你想捕获的是变更前状态、变更后状态，还是两者都要。比如审计敏感字段（如 salary），BEFORE UPDATE 能拿到 OLD.salary，AFTER UPDATE 才能读到 NEW.salary；但 AFTER 触发器不能修改 NEW 值，而 BEFORE 可以——这直接影响你是否能在日志里存脱敏值（如把手机号中间四位替换成 ****）。

实操建议：

• 要记录完整变更对比，必须组合使用：BEFORE UPDATE 存 OLD.* 到临时用户变量（如 @old_salary := OLD.salary），再在 AFTER UPDATE 中读取并插入日志
• 避免在 BEFORE INSERT 中对 NEW.id 赋值后，又在日志里记 NEW.id ——此时自增 ID 尚未生成，会是 0 或 NULL，应改用 AFTER INSERT + LAST_INSERT_ID()
• BEFORE DELETE 是唯一能拿到被删行全量数据的时机，AFTER DELETE 中 OLD 已不可访问

JSON 类型存日志字段时的兼容性陷阱

MySQL 5.7+ 支持 JSON 类型，但触发器里直接拼 JSON_OBJECT('id', NEW.id, 'name', NEW.name) 很方便，问题在于：如果 NEW.name 是 NULL，JSON_OBJECT 会忽略该键；如果字段含特殊字符（如换行、双引号），不加 JSON_QUOTE() 会导致 JSON 格式损坏；更隐蔽的是，某些客户端（如旧版 PHP PDO）对 JSON 字段返回字符串而非对象，后续解析易出错。

实操建议：

• 强制转义所有字符串字段：

  JSON_OBJECT(
    'id', NEW.id,
    'name', JSON_QUOTE(NEW.name),
    'updated_at', JSON_QUOTE(NEW.updated_at)
  )

• 若 MySQL 版本 CONCAT('{', ... , '}') 拼接，但必须手动处理单引号、反斜杠、双引号 —— 不推荐，优先升级
• 日志表的 JSON 字段设默认值 NULL，不要设 '' 或 '{}'，否则 JSON_VALID() 检查会失败

高并发下触发器写日志导致性能抖动

每条 DML 都触发一次 INSERT，在 QPS 过千的表上，日志表会成为瓶颈：索引更新、磁盘刷写、MVCC 版本链拉长都会拖慢主表。更严重的是，如果日志表和主表在同一个库，锁竞争（尤其是 auto_inc 锁）会让事务等待时间飙升。

实操建议：

• 日志表单独建库（如 audit_db），用不同物理磁盘或 SSD 分区，减少 I/O 冲突
• 日志表去掉非必要索引，只保留 created_at 的普通索引（用于按天归档），禁用全文、前缀、函数索引
• 用 INSERT DELAYED（MySQL 5.6 及以前）已废弃，替代方案是异步化：触发器里只写轻量消息到 sys_log_buffer 内存表（MEMORY 引擎），再由定时任务批量落盘