SQL Academy · 课时

为什么要保留历史记录

Q: 「为什么要保留历史记录」这节课中我会学到什么？

利用过去的数据进行审计、撤销和分析。 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

利用过去的数据进行审计、撤销和分析。

为什么要保留历史记录 是 CoddyKit 上的免费 SQL Academy 课时。这是第 1 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

覆盖旧数据的问题

每次运行 UPDATE 或 DELETE 时，旧数据都会永久消失。这样看似高效，却会带来实际问题：您无法回答诸如上周二的价格是多少？或是谁在何时修改了这条记录？之类的问题。

保留历史意味着存储行的每个版本，而不仅是最新版本。本课将探讨其重要性，以及结构化查询语言如何帮助您做到这一点。

保留历史的三个原因

在数据库中保留历史数据有三个经典原因：

1. 审计 — 证明某项更改已经发生、由谁执行以及何时执行。
2. 撤销 — 在不恢复整个数据库的情况下回滚错误。
3. 分析 — 回答有关过去的问题、发现趋势并比较不同期间。

设计良好的历史记录策略可以在不过度重复存储的情况下满足全部三项需求。

简单的审计表

最简单的方法是使用单独的审计表，记录每项变更。每一行都会记录旧值、新值、执行变更的人员以及变更时间。

下面是针对 products 表的审计表。operation 列存储 INSERT、UPDATE 或 DELETE。

CREATE TABLE products_audit (
  audit_id   SERIAL PRIMARY KEY,
  product_id INT            NOT NULL,
  operation  VARCHAR(6)     NOT NULL,  -- INSERT / UPDATE / DELETE
  old_price  NUMERIC(10,2),
  new_price  NUMERIC(10,2),
  changed_by TEXT           NOT NULL,
  changed_at TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

填充审计表

您可以手动写入审计表，但最可靠的方法是使用数据库触发器，在数据发生变化时自动触发。这样，任何应用程序代码都无法绕过日志记录。

这里直接插入一条审计记录，以便在介绍触发器之前说明其结构。

INSERT INTO products_audit (product_id, operation, old_price, new_price, changed_by)
VALUES (42, 'UPDATE', 9.99, 12.49, 'alice');

SELECT * FROM products_audit ORDER BY changed_at DESC LIMIT 5;

读取审计记录

审计表中的行累积后，您可以查询这些记录来回答审计问题。下面的查询显示单个产品的完整价格历史，最新记录在前。

SELECT
  changed_at,
  changed_by,
  operation,
  old_price,
  new_price
FROM products_audit
WHERE product_id = 42
ORDER BY changed_at DESC;

生效日期：有效时间历史

审计表记录的是您何时执行了变更（事务时间）。有时还需要跟踪某个状态在现实世界中何时成立——这称为有效时间。

在主表中添加 valid_from 和 valid_to 列，可以创建有效时间历史，这种历史有时也称为缓慢变化维度（SCD 类型 2）。

CREATE TABLE employee_history (
  id          SERIAL PRIMARY KEY,
  employee_id INT            NOT NULL,
  department  TEXT           NOT NULL,
  salary      NUMERIC(10,2)  NOT NULL,
  valid_from  DATE           NOT NULL,
  valid_to    DATE           -- NULL means current record
);

-- Current record for employee 7
INSERT INTO employee_history (employee_id, department, salary, valid_from)
VALUES (7, 'Engineering', 85000, '2023-01-01');

更新缓慢变化的记录

当员工调换部门时，您不会 UPDATE 其行。相反，您会通过设置 valid_to 来关闭旧行，然后插入一条新的、没有结束日期的行。这样可以保留完整历史记录。

-- Step 1: close the current record
UPDATE employee_history
SET valid_to = '2024-06-01'
WHERE employee_id = 7 AND valid_to IS NULL;

-- Step 2: insert the new record
INSERT INTO employee_history (employee_id, department, salary, valid_from)
VALUES (7, 'Product', 90000, '2024-06-01');

-- Verify history
SELECT department, salary, valid_from, valid_to
FROM employee_history
WHERE employee_id = 7
ORDER BY valid_from;

查询某个时间点的状态

有了有效时间列，您就可以询问在特定日期哪些状态成立——这是使用普通 UPDATE 模型不可能完成的查询。

WHERE 子句会检查目标日期是否落在该行的有效期范围内。

-- What department and salary did employee 7 have on 2023-09-15?
SELECT department, salary, valid_from, valid_to
FROM employee_history
WHERE employee_id = 7
  AND valid_from <= '2023-09-15'
  AND (valid_to > '2023-09-15' OR valid_to IS NULL);

系统版本化时态表

现代数据库（PostgreSQL 16+、SQL Server、MySQL 8）支持系统版本化时态表。数据库会在隐藏列中自动跟踪事务时间，您可以使用特殊语法查询过去的状态。

SQL Server 示例——不同数据库引擎的概念相同：

-- SQL Server / MariaDB style (illustrative)
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  status      VARCHAR(20),
  total       NUMERIC(10,2),
  SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
  SysEndTime   DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);

-- Query historical state
SELECT * FROM orders FOR SYSTEM_TIME AS OF '2024-01-15 12:00:00'
WHERE order_id = 100;

使用历史记录实现撤销

历史表不仅用于读取数据，您还可以利用它们撤销错误。如果某个批处理作业破坏了 500 条价格记录，您可以从审计表中恢复这些记录，而无需动用备份。

-- Undo all price changes made by the bad batch job at a specific time
UPDATE products p
SET price = a.old_price
FROM products_audit a
WHERE p.id         = a.product_id
  AND a.operation  = 'UPDATE'
  AND a.changed_by = 'batch_job'
  AND a.changed_at BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:05:00';

-- Confirm affected rows
SELECT COUNT(*) AS rows_restored FROM products_audit
WHERE changed_by = 'batch_job'
  AND changed_at BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:05:00';

随时间变化的分析

历史数据为时间序列分析提供了可能。您可以跟踪某项指标的变化、比较环比数据，或检测异常，而无需接触单独的数据仓库。

此查询使用审计表，显示某个产品在每个日历月中的平均价格。

SELECT
  DATE_TRUNC('month', changed_at) AS month,
  ROUND(AVG(new_price), 2)         AS avg_price
FROM products_audit
WHERE product_id = 42
  AND operation IN ('INSERT', 'UPDATE')
GROUP BY 1
ORDER BY 1;

知识检验

测试您对 SQL 中历史数据存储的理解。

回顾：为什么要保留历史记录

在本课中，您了解了覆盖数据为何存在风险，以及 SQL 模式如何保留历史记录以支持审计、撤销和分析。

要点：

审计表会记录每个 INSERT、UPDATE 和 DELETE 操作，以及执行者和执行时间。
有效时间（SCD 第 2 类）行使用 valid_from / valid_to 列记录现实世界中的时间线。
时点查询通过筛选这些日期列来回答历史问题。
系统版本化时态表在数据库层面自动跟踪事务时间。
历史数据无需备份即可实现精准撤销和丰富的时间序列分析。

保留过去并不是额外负担，而是构建可信且可审计系统的基础。

常见问题解答

「为什么要保留历史记录」课时是免费的吗？

是的 — 「为什么要保留历史记录」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「为什么要保留历史记录」这节课中我会学到什么？

利用过去的数据进行审计、撤销和分析。你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 1 节课，共 4 节。

「为什么要保留历史记录」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy