SQL 如何设计审计字段？

发布日期： 2026-01-25 阅读量： 作者： 冷漠man

audit_time和created_time语义不同，不可共用CURRENT_TIMESTAMP；应分别设为ON UPDATE和仅DEFAULT；updated_by需通过存储过程传参+触发器校验；soft_delete建议函数索引；PostgreSQL需会话变量传递user_id。

audit_time 和 created_time 为什么不能都用 CURRENT_TIMESTAMP

MySQL 中 CURRENT_TIMESTAMP 默认只支持一个列自动初始化，第二个列会报错 Invalid default value for 'xxx'。即使你用触发器绕开，created_time 本该是「插入那一刻的不可变时间」，而 audit_time 应反映「最后一次审计操作的时间」——二者语义不同，强行共用同一默认值会导致逻辑混淆。

实操建议：

created_time 设为 TIMESTAMP DEFAULT CURRENT_TIMESTAMP，且
禁止 UPDATE
audit_time 设为 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
如果用的是 MySQL 5.6.5+，可考虑 DATETIME 类型配合显式赋值，避免 TIMESTAMP 的时区隐式转换问题

如何保证 updated_by 和 audit_by 字段不被绕过

直接在应用层拼 SQL 或 ORM save() 时漏填字段，updated_by 就会变成 NULL 或默认值，审计链就断了。数据库层面无法强制「非空 + 当前登录用户」，因为 DB 不知道谁在登录。

实操建议：

所有写操作必须走存储过程或带参数的预编译语句，把 user_id 作为必传参数传入（例如 IN p_operator_id BIGINT）
在触发器中校验：IF NEW.updated_by IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'updated_by cannot be null'; END IF;
避免在应用里用 UPDATE table SET col = ? 这种裸更新，必须显式带上 updated_by 和 audit_time

soft_delete 字段要不要加索引

如果经常查「未删除的数据」，比如 WHERE deleted = 0，不加索引会导致全表扫描，尤其当表增长到百万级后，查询明显变慢。但加索引也有代价：每次 INSERT/UPDATE 都要维护索引树，且 deleted 通常只有 0/1 两个值，区分度极低。

实操建议：

优先考虑「函数索引」（MySQL 8.0.13+）：CREATE INDEX idx_active ON t_user ((CASE WHEN deleted = 0 THEN id END));
或者建普通索引 + 覆盖查询字段，如 INDEX idx_deleted_status (deleted, status, created_time)
如果 99% 查询都带 deleted = 0，且数据分布倾斜严重（比如 99.9% 是未删除），可以接受索引低效，但务必压测验证

PostgreSQL 里怎么处理 audit_user 的动态赋值

PostgreSQL 没有像 MySQL 那样内置的 CURRENT_USER 触发器上下文变量能直接映射到业务用户 ID。你拿到的往往是数据库连接用户（如 app_user），不是前端传来的 user_id。

实操建议：

在连接池层（如 PgBouncer）或应用层用 SET app.user_id = 123 设置会话变量，再在触发器里读取：current_setting('app.user_id', true)::BIGINT
触发器中加判空保护：IF NOT EXISTS (SELECT 1 FROM pg_settings WHERE name = 'app.user_id') THEN RAISE EXCEPTION 'app.user_id not set'; END IF;
避免用 SESSION_USER 或 CURRENT_USER 直接赋值，它们和业务身份无关

审计字段看着简单，真正难的是让每个 INSERT/UPDATE 都稳定、可追溯、不被跳过。最容易被忽略的其实是「连接层变量传递」和「触发器中的空值防御」——这两处一松，整条审计链就形同虚设。

上一篇: 华为荣耀手机怎样唤醒yoyo_华为荣耀手机语音唤醒yoyo方

下一篇: SQL 使用窗口函数实现去重保留最新记录

SQL 如何设计审计字段？

随便看看

南昌壹佳企网络通信有限公司