各位观众老爷们，大家好！今天咱们来聊聊MySQL里的“时光机”——时态表（Temporal Tables）。 想象一下，你手里的数据就像一堆不断变化的积木，今天搭了个房子，明天拆了搭个城堡，后天又改成火箭。如果你想知道昨天、上个月甚至去年这个时候，你的积木是什么形状，那你就需要时态表了。
一、什么是时态表？（What is a Temporal Table, Anyway?）
简单来说，时态表就是一种可以记录数据随时间变化历史的表。它允许你查询特定时间点或时间段内的数据状态，就像给你的数据加上了版本控制。不再是只能看到最新的积木，而是可以随时翻出以前的积木照片！
二、时态表的类型（Temporal Table Flavors）
MySQL 8.0 提供了两种时态表：
- 系统版本化表（System-versioned table）： 由系统自动维护历史数据，你不需要操心怎么记录，MySQL会帮你搞定。
- 应用程序版本化表（Application-time period table）： 需要你手动控制历史数据的记录，更加灵活，但同时也更麻烦。
三、系统版本化表（System-versioned Table）：MySQL的自动时光机
这是最简单的时态表实现方式，也是我们今天重点要讲的。让MySQL自己记录历史，多省事儿！
1. 创建系统版本化表（Creating the Time Traveler）
创建系统版本化表非常简单，只需要在 CREATE TABLE 语句中加上 SYSTEM VERSIONING 即可。
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) SYSTEM VERSIONING;
product_id：商品ID，主键。product_name：商品名称。price：商品价格。valid_from：记录生效的起始时间，由系统自动生成。valid_to：记录失效的结束时间，由系统自动生成。PERIOD FOR SYSTEM_TIME (valid_from, valid_to)：定义valid_from和valid_to两个字段作为系统时间段。SYSTEM VERSIONING：声明这是一个系统版本化表。
2. 插入数据（Sending Data Through Time）
插入数据和普通表一样，没什么特别的。
INSERT INTO products (product_id, product_name, price) VALUES
(1, '超级好吃的苹果', 5.00),
(2, '无敌耐用的键盘', 100.00);3. 更新数据（Changing the Past… and the Future）
更新数据时，MySQL会自动创建一个新的历史版本，并将旧版本标记为已过期。
UPDATE products SET price = 6.00 WHERE product_id = 1;4. 删除数据（Erasing Data From Existence… Temporarily）
删除数据也会创建一个历史版本，将当前版本标记为已过期。
DELETE FROM products WHERE product_id = 2;5. 查询历史数据（Looking into the Crystal Ball）
这才是时态表的精髓！我们可以通过 FOR SYSTEM_TIME 子句来查询特定时间点或时间段的数据。
- 查询特定时间点的数据：
SELECT * FROM products FOR SYSTEM_TIME AS OF '2023-10-27 10:00:00';这条SQL会返回在2023年10月27日10点整时，products 表的数据状态。
- 查询某个时间段内的数据：
SELECT * FROM products FOR SYSTEM_TIME BETWEEN '2023-10-27 09:00:00' AND '2023-10-27 11:00:00';这条SQL会返回在2023年10月27日9点到11点之间，products 表的所有数据版本。
- 查询在某个时间点有效的数据：
SELECT * FROM products FOR SYSTEM_TIME FROM '2023-10-27 09:00:00' TO '2023-10-27 11:00:00';这条SQL返回在给定的时间范围内有效的行。
- 查询包含某个时间点的数据：
SELECT * FROM products FOR SYSTEM_TIME CONTAINED IN ('2023-10-27 09:00:00', '2023-10-27 11:00:00');这条SQL返回给定时间范围内的所有版本。
6. 历史表（The History Lesson）
MySQL会自动创建一个隐藏的历史表，用来存储历史数据。你可以通过以下方式查看历史表：
SELECT * FROM `products_history` ;注意，products_history 是系统自动创建的，命名规则是 原表名_history。 你不能直接修改历史表的数据，只能通过修改原表来影响历史记录（比如通过回滚事务）。
四、应用程序版本化表（Application-time Period Table）：手动控制时间线
这种方式需要你自己定义时间段字段，并手动维护历史数据。虽然麻烦，但更灵活，可以根据业务需求自定义时间段的含义。
1. 创建应用程序版本化表（Building Your Own Time Machine）
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(255) NOT NULL,
salary DECIMAL(10, 2) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
PERIOD FOR app_time (start_date, end_date)
) ;start_date：记录生效的起始日期，由应用程序维护。end_date：记录失效的结束日期，由应用程序维护。PERIOD FOR app_time (start_date, end_date)：定义start_date和end_date两个字段作为应用程序时间段。
2. 插入数据（Setting the Initial Conditions）
插入数据时，你需要手动设置 start_date 和 end_date。
INSERT INTO employee (employee_id, employee_name, salary, start_date, end_date) VALUES
(1, '张三', 5000.00, '2023-01-01', '9999-12-31');注意，end_date 通常设置为一个很大的日期，表示当前记录有效。
3. 更新数据（Rewriting History, One Update at a Time）
更新数据时，你需要先更新旧记录的 end_date，然后再插入一条新的记录。
-- 更新旧记录
UPDATE employee SET end_date = '2023-10-27' WHERE employee_id = 1 AND end_date = '9999-12-31';
-- 插入新记录
INSERT INTO employee (employee_id, employee_name, salary, start_date, end_date) VALUES
(1, '张三', 6000.00, '2023-10-28', '9999-12-31');4. 查询历史数据（Peering Through the Application’s Lens）
查询历史数据的方式和系统版本化表类似，但需要使用应用程序定义的时间段。
SELECT * FROM employee FOR PERIOD app_time AS OF '2023-05-01';
SELECT * FROM employee WHERE '2023-05-01' BETWEEN start_date AND end_date;这两种方式都可以查询在2023年5月1日有效的员工信息。
五、时态表的应用场景（When to Use a Time Machine）
时态表在很多场景下都非常有用，比如：
- 审计追踪（Auditing）： 记录数据的变更历史，方便审计和追溯。
- 合规性（Compliance）： 满足法规要求，保留数据的历史版本。
- 数据分析（Data Analysis）： 分析数据的历史趋势，发现潜在的规律。
- 业务流程（Business Processes）： 支持基于历史数据的业务流程，比如计算历史订单的利润。
- 缓慢变化维度（SCD Type 2）： 在数据仓库中，可以使用时态表来实现缓慢变化维度。
六、时态表的优缺点（Pros and Cons of Time Travel）
优点：
- 简化历史数据管理： 无需手动维护历史表，MySQL会自动处理。
- 提高查询效率： 可以通过时间点或时间段快速查询历史数据。
- 增强数据可靠性： 避免数据丢失或篡改，提高数据的可追溯性。
缺点：
- 增加存储空间： 需要存储历史数据，会占用更多的存储空间。
- 影响写入性能： 每次更新或删除数据都需要创建历史版本，会影响写入性能。
- 学习成本： 需要学习新的SQL语法和概念。
- 复杂性： 系统版本化表虽然简单易用，但应用程序版本化表则需要仔细设计和维护。
七、一些注意事项（Things to Keep in Mind）
- 存储空间： 历史数据会占用大量的存储空间，需要定期清理或归档。
- 性能优化： 针对时态表的查询进行优化，比如创建索引。
- 数据类型： 选择合适的时间数据类型，比如
TIMESTAMP(6)可以精确到微秒。 - 事务： 在使用应用程序版本化表时，需要确保事务的完整性，避免数据不一致。
八、代码示例：一个完整的系统版本化表例子
-- 创建数据库
CREATE DATABASE IF NOT EXISTS temporal_demo;
USE temporal_demo;
-- 创建商品表
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) SYSTEM VERSIONING;
-- 插入数据
INSERT INTO products (product_id, product_name, price) VALUES
(1, '超级好吃的苹果', 5.00),
(2, '无敌耐用的键盘', 100.00);
-- 更新数据
UPDATE products SET price = 6.00 WHERE product_id = 1;
-- 删除数据
DELETE FROM products WHERE product_id = 2;
-- 查询当前数据
SELECT * FROM products;
-- 查询历史数据
SELECT * FROM products FOR SYSTEM_TIME AS OF '2023-10-27 10:00:00';
-- 查询某个时间段内的数据
SELECT * FROM products FOR SYSTEM_TIME BETWEEN '2023-10-27 09:00:00' AND '2023-10-27 11:00:00';
-- 查询历史表
SELECT * FROM `products_history`;
-- 清理数据库（可选）
DROP DATABASE IF EXISTS temporal_demo;九、对比表格：系统版本化 vs 应用程序版本化
| 特性 | 系统版本化表 (System-versioned) | 应用程序版本化表 (Application-time Period) |
|---|---|---|
| 时间段管理 | 系统自动维护 | 应用程序手动维护 |
| 历史数据维护 | 系统自动维护 | 应用程序手动维护 |
| 灵活性 | 较低 | 较高 |
| 复杂性 | 较低 | 较高 |
| 适用场景 | 简单的审计追踪，合规性要求 | 需要自定义时间段含义的业务场景 |
| SQL 标准支持 | MySQL 8.0 及更高版本 | 需要手动编写 SQL 查询来实现时间段查询 |
十、总结（Wrapping Up）
时态表是MySQL中一个非常强大的功能，可以帮助你轻松地管理数据的历史版本。选择哪种类型的时态表取决于你的业务需求。 如果你只需要简单的审计追踪，那么系统版本化表是一个不错的选择。 如果你需要更灵活地控制历史数据，那么应用程序版本化表可能更适合你。
记住，没有银弹！ 在选择时态表之前，一定要充分评估其优缺点，并结合你的实际情况做出决定。
好了，今天的讲座就到这里。 希望大家以后也能像玩转时光机一样玩转MySQL！ 感谢各位的观看，下次再见！