如何在SQL表中实现审计或更改跟踪机制（更改历史记录/日志记录）？有哪些方法，它们的优缺点是什么，以及在设计此功能时需要考虑哪些重要因素？

答案

审计机制（日志或更改历史记录）用于跟踪所有数据更改并确保透明性。主要有几种方法：

1. 审计触发器（Audit Triggers）： 特殊的AFTER/BEFORE INSERT/UPDATE/DELETE触发器，将旧状态和/或新状态的行保存到单独的审计表中。优点是对应用程序透明；缺点是可能会减慢DML操作。

2. 在单独表中隔离历史（History Table）： 采用版本记录 — 在修改时，将“旧”行复制到历史表中，并带有时间戳。主表仅包含当前数据。

3. 内置机制（Change Data Capture, Temporal Tables）： 例如，在SQL Server中，可以启用SYSTEM_VERSIONED TEMPORAL TABLE，数据库将自动存储更改历史。

示例（MySQL，审计触发器）：

CREATE TABLE user_audit (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  action VARCHAR(10),
  old_value TEXT,
  new_value TEXT,
  changed_at DATETIME
);

DELIMITER //
CREATE TRIGGER audit_user_update
  AFTER UPDATE ON users
  FOR EACH ROW BEGIN
    INSERT INTO user_audit (user_id, action, old_value, new_value, changed_at)
    VALUES (OLD.id, 'UPDATE', OLD.name, NEW.name, NOW());
END//
DELIMITER ;

诱惑性问题

问题： 如果需要完整的更改历史记录，是否只需要在UPDATE上有审计触发器就足够了？

答案： 不够。完整的历史记录只能通过组合所有操作类型的触发器来获得：INSERT（记录新行）、UPDATE（记录更改）和DELETE（记录删除的记录）。

示例：

-- 需要为每种操作创建3个单独的触发器，否则历史会有“空白”。

由于不熟悉主题细节而导致的实际错误示例

---

故事1：公司仅使用UPDATE触发器进行审计，并在一年内没有注意到删除的记录没有被记录。最终在调查事故时无法确定重要用户帐户何时被删除。

---

故事2：项目将更改的旧值和新值存储在同一个文本字段中，以序列化的JSON形式，这大大增加了分析的复杂性——需要复杂的解析器和单独的数据库副本进行报告。

---

故事3：在高负载的数据库中，开发人员通过触发器实施审计，而没有考虑日志表的增长。随着时间的推移，历史表的大小超出了主表的50倍，导致存储空间溢出，整个项目出现故障。

---