如何利用SQL存储过程实现数据库级别的审计日志记录？-SQL-PHP中文网

审计日志必须脱离主事务：sql server用set xact_abort off+try/catch；mysql用insert ignore或on duplicate key update；postgresql用unlogged表+volatile函数捕获异常；字段精简为id、proc_name、executed_by、executed_at、params_json。

如何利用sql存储过程实现数据库级别的审计日志记录？

SQL Server 存储过程中写审计日志必须脱离主事务

日志写入和业务逻辑共用一个事务，看似省事，实则埋雷：主逻辑失败回滚，日志也消失；日志写入失败，又可能拖垮整个事务。根本解法是让日志插入不参与主事务控制。

推荐做法是显式禁用事务传播：SET XACT_ABORT OFF，再用 BEGIN TRY/END TRY 包裹日志语句，确保即使插入失败也不中断业务流程。更稳妥的方案是配合 sp_getapplock 做并发写保护，避免多线程同时 INSERT 造成主键冲突或死锁。

不要在 UPDATE 或 INSERT 事务块内部直接写 INSERT INTO audit_log
避免使用 CURRENT_USER() 记录执行人——连接池下它返回数据库账号，不是真实操作者；改用 ORIGINAL_LOGIN()
时间戳统一用 SYSDATETIME() 赋值给变量，而非每条日志都调用函数，防止高并发下毫秒级时间重复

MySQL 存储过程里写审计日志要绕开“表已使用”限制

MySQL 在存储过程或函数中直接 INSERT 到同一张被主逻辑修改的表，会触发错误：ERROR 1442 (HY000): Can't update table 'audit_log' in stored function/trigger because it is already used by statement...。这不是权限问题，而是 MySQL 的内部限制。

绕过方式有两种：

用 INSERT IGNORE INTO audit_log，前提是表有唯一约束（如 (proc_name, executed_at)）
更推荐 INSERT ... ON DUPLICATE KEY UPDATE ignored = VALUES(ignored)，语法合法、不报错、兼容性好
注意检查 log_bin_trust_function_creators 参数：若开启 binlog，且过程含写操作，必须设为 ON，否则创建失败

PostgreSQL 函数中记录审计日志得用 UNLOGGED 表 + VOLATILE 函数

PG 默认所有函数都在当前事务中运行，日志写入失败 = 主事务回滚。但审计日志本就不该影响业务一致性，所以必须“发出去就不管”。

Levity

Levity是一款面向物流运营和复杂收件箱流程的 AI 自动化平台。

下载

最简可行方案是建一张 UNLOGGED 审计表（不写 WAL，性能高），再定义一个 VOLATILE 函数，里面做 INSERT 并忽略错误：

CREATE OR REPLACE FUNCTION log_procedure_call(proc_name TEXT, params JSONB)
RETURNS void AS $$
BEGIN
  INSERT INTO audit_log (proc_name, params, created_at) 
  VALUES (proc_name, params, NOW());
EXCEPTION WHEN OTHERS THEN
  -- 不抛出，不回滚主事务
END;
$$ LANGUAGE plpgsql VOLATILE;

UNLOGGED 表重启后数据丢失，但审计日志本身允许少量丢失，换来的写入性能提升明显
别用 PERFORM 调用普通函数——它仍受事务控制；VOLATILE 是关键标记
不建议用 pg_notify 做异步，那需要额外监听进程，运维复杂度陡增

审计表字段设计别贪全，先保这五个核心字段

见过太多团队一上来就加 client_ip、host_name、full_sql，结果发现：client_ip 在代理/连接池下全是 127.0.0.1；host_name() 返回的是应用服务器名；full_sql 写入慢、查得更慢、还容易泄露敏感参数。

真正稳定、可落地、能定位问题的最小字段集只有这五个：

id：BIGINT IDENTITY，不用 UUID，插入性能差 3 倍以上
proc_name：用 OBJECT_NAME(@@PROCID)（SQL Server）或硬编码字符串（MySQL/PG），别靠 SHOW CREATE PROCEDURE 反查
executed_by：从应用层传入的 @user_login 参数，不是数据库内置函数
executed_at：DATETIME2(3)（SQL Server）、NOW(3)（MySQL）、NOW()（PG），统一毫秒精度
params_json：用 FOR JSON PATH（SQL Server）、JSON_OBJECT()（MySQL 5.7+）、to_jsonb()（PG）序列化，别拼字符串

其余字段如 affected_rows、error_info 属于“按需添加”，但一旦加了就得在每个存储过程的 CATCH 块里补全，否则字段空值比例高，反而干扰查询。