如何编写SQL存储过程审计日志_将执行记录写入监控表-SQL-PHP中文网

sql server用sp_executesql动态记日志最轻量可控，须独立事务、关键字段json化、索引优化；mysql需insert ignore或on duplicate key update避错；pg宜用perform调用volatile无事务函数+unlogged表；跨库日志表应精简字段、统一命名、前置脱敏截断。

如何编写sql存储过程审计日志_将执行记录写入监控表

SQL Server 中用 `sp_executesql` 动态记录存储过程调用日志

直接在存储过程开头插入日志，比触发器或 SQL Server Audit 更轻量、更可控。关键不是“能不能记”，而是“什么时候记、记什么、会不会拖慢主流程”。

常见错误是把日志写进事务主体里——比如在 UPDATE 事务中顺手 INSERT INTO audit_log，结果日志表锁住导致整个业务卡死。

日志写入必须独立事务：用 SET XACT_ABORT OFF + 显式 BEGIN TRY...END TRY 包裹，失败不中断主逻辑
只记关键字段：procedure_name、executed_at、caller_host（用 HOST_NAME()）、input_params（建议 JSON 化，避免长文本拖慢）
监控表必须有合适索引：按 executed_at 建非聚集索引，否则查最近一小时日志会全表扫

MySQL 存储过程中写审计日志的兼容性陷阱

MySQL 不支持在存储过程里直接调用 INSERT 写日志并忽略错误（不像 SQL Server 的 TRY/CATCH），容易因日志表不存在或权限不足导致主过程报错退出。

典型错误现象：ERROR 1442 (HY000): Can't update table 'audit_log' in stored function/trigger because it is already used by statement which invoked this stored function/trigger——这是在触发器里又去改同一张表引发的限制。

绕过方案：用 INSERT IGNORE INTO audit_log ...，前提是表有唯一键（比如 (proc_name, executed_at) 组合）
更稳妥做法：改用 INSERT ... ON DUPLICATE KEY UPDATE ignored = ignored，确保语句语法合法且不报错
注意 log_bin_trust_function_creators 参数：如果开启 binlog 且过程含写操作，需设为 ON，否则创建失败

PostgreSQL 函数中异步写日志避免阻塞

PG 的函数默认在同一个事务上下文中执行，日志写入失败 = 主事务回滚。但审计日志本就不该影响业务一致性，所以得“发出去就不管”。

不能依赖 dblink 或外部队列时，最简方案是用 pg_notify + 后台监听进程；若必须内置，可用 PERFORM 调用一个无事务函数：

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（默认），否则优化器可能跳过调用
调用时用 PERFORM log_procedure_call(...)，不是 SELECT，避免返回结果干扰
表 audit_log 建议用 UNLOGGED（如果可接受崩溃丢失少量日志），写入快 3–5 倍

跨数据库统一日志结构设计要点

不同数据库对时间精度、JSON 支持、错误捕获粒度差异很大，强行用同一套 DDL 和逻辑会踩坑。

比如 MySQL 5.7 的 JSON 类型不能存 NULL 字段值，而 PG 的 JSONB 可以；SQL Server 的 sys.dm_exec_sessions 能取到客户端应用名，MySQL 得靠 information_schema.PROCESSLIST 临时查。

日志表字段尽量精简：只保留 id（自增/BIGSERIAL）、proc_name（VARCHAR(128)）、executed_at（带毫秒）、params_hash（CHAR(64)，SHA2-256，避免存原始参数）
不要存 error_message 字段指望自动捕获异常——各库异常处理机制不一致，应由调用方显式传入
监控表名统一用 audit_proc_exec，别用 log_ 或 history_ 前缀，避免和业务表命名风格冲突