MySQL触发器中可向独立日志表INSERT，但禁止操作被触发的表本身，否则报ERROR 1442；需用AFTER触发器、手动比对OLD/NEW字段（注意NULL）、日志表应分区+复合索引，并警惕事务回滚导致日志丢失。

触发器里不能直接写 INSERT INTO 日志表？

MySQL 触发器中执行 INSERT INTO 写日志表是可行的，但必须避开被触发的表本身——否则会报错 ERROR 1442 (HY000): Can't update table 'xxx' in stored function/trigger because it is already used by statement which invoked this stored functi

on/trigger。这个限制是 MySQL 的硬性约束，不是权限或配置问题。

实操建议：

• 日志表必须是独立表（比如 user_log），且不能与触发器所在表同名、也不能在触发逻辑中被当前 SQL 语句隐式读写
• 避免在 BEFORE 触发器中修改 NEW 或 OLD 字段的同时又去查/写同一张业务表
• 如果日志需记录完整行数据，优先用 AFTER INSERT/UPDATE/DELETE 触发器，此时原表已稳定，不会冲突

如何安全记录 UPDATE 前后字段变化？

只记“谁改了”不够，多数审计场景需要知道“改了什么”。MySQL 触发器不支持 JSON 对比函数（如 JSON_CONTAINS 在 5.7+ 可用但不直观），所以得手动比对关键字段。

实操建议：

• 在 AFTER UPDATE 触发器中，用 IF OLD.name != NEW.name THEN ... END IF; 判断单字段变更，再拼接日志字符串
• 若字段多，可预先定义日志内容变量：SET @log_msg = CONCAT('name:', OLD.name, '→', NEW.name, ';email:', OLD.email, '→', NEW.email);
• 注意 NULL 比较：必须用 OLD.col IS NULL != NEW.col IS NULL 或 NOT (OLD.col NEW.col)，否则 NULL != NULL 返回 NULL 导致判断失效

日志表设计要考虑哪些性能和查询需求？

日志表容易越积越大，一旦没索引或结构不合理，SELECT 审计查询会越来越慢，甚至拖垮主业务。

实操建议：

• 必加复合索引：INDEX (table_name, operation_type, created_at)，方便按表+操作类型快速筛选
• created_at 字段用 CURRENT_TIMESTAMP 默认值，不要依赖应用层传入，避免时区/精度不一致
• 避免在日志表中存大文本（如整行 JSON）；如需保留原始数据，可用 MEDIUMTEXT，但单独建归档库或定期转储到对象存储
• 考虑分区：按月对 created_at 分区（PARTITION BY RANGE (TO_DAYS(created_at))），删旧日志只需 DROP PARTITION，比 DELETE 快得多

CREATE TABLE user_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  table_name VARCHAR(64) NOT NULL,
  operation_type ENUM('INSERT','UPDATE','DELETE') NOT NULL,
  record_id BIGINT UNSIGNED,
  changed_fields TEXT,
  operator VARCHAR(128),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id, created_at),
  INDEX idx_table_op_time (table_name, operation_type, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')),
  PARTITION p202502 VALUES LESS THAN (TO_DAYS('2025-03-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

触发器 + 日志组合最常踩的坑是什么？

不是语法错误，而是事务边界和异常处理被忽略。触发器属于主 SQL 事务的一部分：主语句回滚，触发器写的日志也自动回滚——这会导致“以为记了日志，实际查不到”。

实操建议：

• 如果日志必须持久化（比如风控告警），不能依赖触发器，应改用应用层异步写入，或通过 MySQL 的 mysqlbinlog 解析 binlog 实现最终一致性
• 触发器内禁止调用存储过程以外的外部服务（如 HTTP 请求），MySQL 不支持，会直接报错
• 上线前务必测试并发更新：多个线程同时改同一行，可能因锁等待超时导致触发器失败，进而让整个事务失败

触发器适合轻量、确定性高的日志补充，比如记录操作人、时间、关键字段变更。一旦涉及跨库、高可靠或大数据量归档，就得跳出触发器思维，用更可控的架构承接。