SQL 触发器日志与审计实现

安全记录日志应使用after触发器、极简结构日志表、禁用耗时操作；mysql可用blackhole引擎异步消费，postgresql可用pg_notify()；需统一utf8mb4字符集，避免大字段截断；postgresql触发器须设security definer并正确授权；差异审计推荐hstore或显式字段比对，而非整行json。

触发器里怎么安全记录日志，不拖慢主表操作

直接写 INSERT INTO audit_log 到普通表，大概率会把事务锁住、拖慢业务。核心矛盾是：审计要完整，但不能干扰主流程。

• 用 AFTER INSERT/UPDATE/delete 而非 BEforE，避免在事务中做耗时写入；
• 日志表必须有极简结构：只存 table_name、operation、old_data（JSON）、new_data（JSON）、user、created_at；
• 禁止在触发器里调用存储过程或外部 http 请求——MySQL 不支持，PostgreSQL 也极不推荐；
• PostgreSQL 可考虑用 pg_notify() 异步通知监听端，MySQL 则建议把日志先写进 BLACKHOLE 引擎表再由外部服务消费。

MySQL 触发器读不到 NEW 和 OLD 的大字段怎么办

当字段是 TEXT、MEDIUMTEXT 或含 emoji 的 utf8mb4 字符串时，NEW.content 在某些 MySQL 版本（如 5.7.20 前）可能被截断或报 Cannot create a stored function or trigger that uses a table with an unknown storage engine 类似错误。

• 确认表字符集和排序规则统一为 utf8mb4_unicode_ci，且连接层也设对（SET NAMES utf8mb4）；
• 避免在触发器里对 NEW.xxx 做 Length()、SUBSTRING() 等计算——它们可能隐式触发截断；
• 真要处理大文本，改用 JSON_OBJECT('id', NEW.id, 'content', NEW.content) 直接转 JSON 存，MySQL 5.7+ 原生支持；
• 测试时用 select LENGTH(NEW.content) 对比原表值，快速定位是否被截。

PostgreSQL 触发器审计如何避免权限失败

PERFORM INSERT INTO audit_log 报 permission denied for table audit_log 是最常见卡点——触发器执行身份默认是调用者，不是定义者。

• 创建触发器函数时必须加 SECURITY DEFINER，否则函数以调用者权限运行；
• 确保执行函数的用户对 audit_log 表有 INSERT 权限，且该权限不能来自 WITH GRANT OPTION 的间接授予；
• 别用 current_user 当操作人，它返回的是定义者；改用 session_user 或 current_setting('app.user_id', true)（需提前 SET app.user_id = 'xxx'）；
• 日志表建议用 UNLOGGED（PostgreSQL），提速明显，但崩溃后内容不持久——审计场景通常可接受。

触发器日志查不到变更前后的具体字段差异

只记整行 JSON，排查时还得人工 diff，效率低。真正有用的审计得知道“谁把 status 从 1 改成了 3”。

• MySQL 8.0+ 可用 JSON_CONTAINS_PATH() + JSON_EXTRACT() 提取变化字段，但性能差；更稳的方式是在应用层生成变更摘要再传入；
• PostgreSQL 推荐用 hstore 扩展：先 SELECT hstore(NEW.*) - hstore(OLD.*)，直接得到差异键值对；
• 无论哪种方案，都不要在触发器里做字符串拼接生成“status changed from 1 to 3”——语义化应由查询端或展示层完成；
• 如果字段多且变动频繁，考虑只审计关键字段：在触发器里显式列出 IF NEW.status OLD.status THEN ... END IF;。

触发器审计最难的从来不是写法，而是搞清“哪些字段真需要审计”和“谁有权看这些日志”。很多团队堆完功能才发现，日志量太大查不动，或者权限配错导致关键操作没人能追溯——这两处没想清楚，代码写得再工整也没用。