审计表需包含操作人、时间、对象、动作等字段，用JSON存储新旧值，通过应用层或触发器记录，注意索引与数据安全。
在MySQL中设计后台操作审计表，核心目标是记录关键操作行为，便于追踪问题、排查责任和保障数据安全。一个合理的审计表结构应包含操作主体、时间、对象、动作类型和详情等信息。
1. 审计表字段设计
建议审计表包含以下基本字段：
- id：主键，自增或使用UUID，确保唯一性。
- operator_id：操作人ID，关联用户表，明确责任人。
- operator_name：操作人姓名或账号，避免后续查用户表。
- operation_type：操作类型，如INSERT、UPDATE、DELETE、LOGIN、EXPORT等，可用枚举或字符串。
- target_table：被操作的表名，用于定位数据来源。
- target_id：被操作记录的主键值，方便追溯具体行。
- old_value：修改前的数据（JSON格式），适用于UPDATE/DELETE。
- new_value：修改后的数据（JSON格式），适用于INSERT/UPDATE。
- operation_time：操作时间，用DATETIME或TIMESTAMP，默认CURRENT_TIMESTAMP。
- ip_address：操作来源IP，辅助安全分析。
- user_agent：客户端信息，可选，用于识别访问设备或浏览器。
- remark：备注，可记录操作上下文或手动填写说明。
2. 表结构示例
以下是创建审计表的SQL语句示例：
CREATE TABLE operation_audit ( id BIGINT AUTO_INCREMENT PRIMARY KEY, operator_id INT NOT NULL, operator_name VARCHAR(50) NOT NULL, operation_type VARCHAR(20) NOT NULL COMMENT 'INSERT, UPDATE, DELETE, LOGIN等', target_table VARCHAR(64) NOT NULL, target_id BIGINT, old_value JSON, new_value JSON, operation_time DATETIME DEFAULT CURRENT_TIMESTAMP, ip_address VARCHAR(45), user_agent TEXT, remark VARCHAR(255) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3. 数据采集方式
有三种常见实现方式：
- 应用层记录：在业务代码中，每次关键操作后插入一条审计记录。灵活性高，可控制记录粒度。
- 触发器自动记录：为敏感表创建AFTER INSERT/UPDATE/DELETE触发器，自动写入审计表。注意性能影响，不适用于高频操作。
- 数据库日志解析：通过binlog解析工具（如Canal）捕获变更，异步写入审计系统。适合大规模场景，但架构复杂。
4. 注意事项
设计时需考虑以下几点：
- 审计表本身不应被频繁清理，建议定期归档而非删除。
- 对old_value和new_value使用JSON字段，便于存储结构化数据，查询时可用JSON函数提取。
- 添加索引提升查询效率，如
(operation_time)
、(operator_id)
、(target_table, target_id)
。 - 避免记录敏感信息（如密码），必要时做脱敏处理。
