MySQL高阶讲座之：`MySQL`的`Temporal Table`：如何利用`MySQL` 8.0实现时态数据。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷们，大家好！我是今天的主讲人，一个在代码堆里摸爬滚打多年的老码农。今天咱们聊点高级的，关于MySQL 8.0 的 Temporal Table，也就是时态表。这玩意儿听起来高大上，其实就是帮你记录数据历史变化的，以后再也不用手动维护那些审计表了，想想是不是有点小激动？

开场白：为什么需要时态表？

在数据江湖里，数据变化是常态。举个栗子，一个用户的地址，一开始是北京，后来搬到上海，再后来又去了深圳。如果我们只保存最新的地址，那之前的地址信息就丢了，以后想查这个用户啥时候在北京住过就抓瞎了。

传统的做法，要么是手动建个审计表，每次更新都往审计表里插一条记录，要么是在原表里加几个字段，比如 start_date 和 end_date，用来表示数据的有效时间段。这两种方法都比较麻烦，而且容易出错。

MySQL 8.0 提供的 Temporal Table 就是来解决这个问题的。它能自动帮你记录数据的历史变化，让你随时可以查询到任何时间点的数据状态。是不是感觉像开了金手指？

第一部分：时态表的三种类型

MySQL 8.0 提供了三种类型的时态表：

System-versioned tables (系统版本表)：由系统自动维护历史数据，每次修改都会自动把旧数据存到历史表中。
Application-time period tables (应用时间段表)：由应用程序维护历史数据，需要手动指定数据的有效时间段。
Bi-temporal tables (双时态表)：结合了系统版本表和应用时间段表的优点，既能自动维护历史数据，又能手动指定数据的有效时间段。

接下来，我们一个个来聊聊。

第二部分：系统版本表 (System-versioned tables)

这是最简单的一种时态表，也是最常用的一种。它由系统自动维护历史数据，你只需要告诉 MySQL 哪些表是时态表，MySQL 就会自动帮你把旧数据存到历史表中。

2.1 创建系统版本表

创建系统版本表很简单，只需要在 CREATE TABLE 语句中加上 WITH SYSTEM VERSIONING 即可。

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    address VARCHAR(255)
) WITH SYSTEM VERSIONING;

这行代码就创建了一个名为 employee 的系统版本表。MySQL 会自动创建一个名为 employee_history 的历史表，用来存储 employee 表的历史数据。历史表的命名规则是 原表名_history。

2.2 插入数据

插入数据跟普通表一样：

INSERT INTO employee (id, name, address) VALUES
(1, '张三', '北京'),
(2, '李四', '上海');

2.3 更新数据

更新数据也很简单：

UPDATE employee SET address = '深圳' WHERE id = 1;

这条语句会把 employee 表中 id 为 1 的记录的 address 字段更新为 ‘深圳’。同时，MySQL 会自动把原来的数据（id 为 1，name 为 ‘张三’，address 为 ‘北京’）存到 employee_history 表中。

2.4 查询历史数据

查询历史数据需要用到 FOR SYSTEM_TIME 子句。这个子句可以指定查询的时间点或时间段。

查询某个时间点的数据：

SELECT * FROM employee FOR SYSTEM_TIME AS OF '2023-10-26 10:00:00';

这条语句会查询在 2023-10-26 10:00:00 这个时间点 employee 表的数据状态。

查询某个时间段的数据：

SELECT * FROM employee FOR SYSTEM_TIME BETWEEN '2023-10-26 09:00:00' AND '2023-10-26 11:00:00';

这条语句会查询在 2023-10-26 09:00:00 到 2023-10-26 11:00:00 这个时间段内 employee 表的数据变化。

查询某个时间点之后的数据：

SELECT * FROM employee FOR SYSTEM_TIME FROM '2023-10-26 10:00:00';

这条语句会查询 2023-10-26 10:00:00 之后 employee 表的数据状态。

查询某个时间点之前的数据：

SELECT * FROM employee FOR SYSTEM_TIME TO '2023-10-26 10:00:00';

这条语句会查询 2023-10-26 10:00:00 之前 employee 表的数据状态。

2.5 删除数据

删除数据也会自动把旧数据存到历史表中：

DELETE FROM employee WHERE id = 2;

2.6 历史表的结构

历史表的结构跟原表基本一样，只是多了两个字段：row_start 和 row_end。这两个字段用来表示数据的有效时间段。

row_start：表示数据开始生效的时间。
row_end：表示数据失效的时间。

可以用如下语句查看历史表结构

DESCRIBE employee_history;

2.7 系统版本表的优缺点

优点：
- 自动维护历史数据，无需手动操作。
- 查询历史数据方便，只需要使用 FOR SYSTEM_TIME 子句。
缺点：
- 无法手动指定数据的有效时间段。
- 历史表会占用额外的存储空间。

第三部分：应用时间段表 (Application-time period tables)

应用时间段表需要由应用程序维护历史数据，需要手动指定数据的有效时间段。

3.1 创建应用时间段表

创建应用时间段表需要在 CREATE TABLE 语句中指定两个时间类型的字段，分别表示数据的开始时间和结束时间。

CREATE TABLE contract (
    contract_id INT PRIMARY KEY,
    employee_id INT,
    salary DECIMAL(10, 2),
    start_date DATE,
    end_date DATE,
    PERIOD FOR p (start_date, end_date)
);

这条语句创建了一个名为 contract 的应用时间段表。start_date 和 end_date 字段分别表示合同的开始时间和结束时间。PERIOD FOR p (start_date, end_date) 子句告诉 MySQL 这两个字段组成一个时间段，并给这个时间段起个别名 p。

3.2 插入数据

插入数据时需要手动指定 start_date 和 end_date 的值：

INSERT INTO contract (contract_id, employee_id, salary, start_date, end_date) VALUES
(1, 1, 5000, '2023-01-01', '2023-12-31'),
(2, 2, 6000, '2023-02-01', '2023-12-31');

3.3 更新数据

更新数据时也需要手动更新 start_date 和 end_date 的值。通常的做法是先插入一条新的记录，然后把旧记录的 end_date 设置为当前时间。

例如，要把 contract_id 为 1 的合同的 salary 修改为 5500，可以这样做：

-- 插入一条新的记录
INSERT INTO contract (contract_id, employee_id, salary, start_date, end_date) VALUES
(3, 1, 5500, '2024-01-01', '2024-12-31');

-- 把旧记录的 end_date 设置为 '2023-12-31'
UPDATE contract SET end_date = '2023-12-31' WHERE contract_id = 1;

3.4 查询历史数据

查询历史数据需要用到 PERIOD_OVERLAPS 函数。这个函数可以判断两个时间段是否重叠。

SELECT * FROM contract WHERE PERIOD_OVERLAPS(PERIOD(start_date, end_date), PERIOD('2023-06-01', '2023-07-01'));

这条语句会查询在 2023-06-01 到 2023-07-01 这个时间段内有效的合同。

3.5 应用时间段表的优缺点

优点：
- 可以手动指定数据的有效时间段。
- 不需要额外的历史表，节省存储空间。
缺点：
- 需要应用程序维护历史数据，比较麻烦。
- 查询历史数据需要用到 PERIOD_OVERLAPS 函数，比较复杂。

第四部分：双时态表 (Bi-temporal tables)

双时态表结合了系统版本表和应用时间段表的优点，既能自动维护历史数据，又能手动指定数据的有效时间段。

4.1 创建双时态表

创建双时态表需要在 CREATE TABLE 语句中同时指定 WITH SYSTEM VERSIONING 和 PERIOD FOR 子句。

CREATE TABLE product (
    product_id INT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    valid_from DATE,
    valid_to DATE,
    PERIOD FOR p (valid_from, valid_to)
) WITH SYSTEM VERSIONING;

这条语句创建了一个名为 product 的双时态表。valid_from 和 valid_to 字段分别表示产品的有效开始时间和有效结束时间。PERIOD FOR p (valid_from, valid_to) 子句告诉 MySQL 这两个字段组成一个时间段，并给这个时间段起个别名 p。WITH SYSTEM VERSIONING 子句告诉 MySQL 自动维护历史数据。

4.2 插入数据

插入数据时需要手动指定 valid_from 和 valid_to 的值：

INSERT INTO product (product_id, name, price, valid_from, valid_to) VALUES
(1, '苹果', 5.00, '2023-01-01', '2023-12-31'),
(2, '香蕉', 3.00, '2023-02-01', '2023-12-31');

4.3 更新数据

更新数据时也需要手动更新 valid_from 和 valid_to 的值。跟应用时间段表一样，通常的做法是先插入一条新的记录，然后把旧记录的 valid_to 设置为当前时间。

例如，要把 product_id 为 1 的产品的 price 修改为 5.50，可以这样做：

-- 插入一条新的记录
INSERT INTO product (product_id, name, price, valid_from, valid_to) VALUES
(3, '苹果', 5.50, '2024-01-01', '2024-12-31');

-- 把旧记录的 valid_to 设置为 '2023-12-31'
UPDATE product SET valid_to = '2023-12-31' WHERE product_id = 1;

4.4 查询历史数据

查询历史数据需要同时用到 FOR SYSTEM_TIME 子句和 PERIOD_OVERLAPS 函数。

SELECT * FROM product FOR SYSTEM_TIME AS OF '2023-06-01' WHERE PERIOD_OVERLAPS(PERIOD(valid_from, valid_to), PERIOD('2023-06-01', '2023-07-01'));

这条语句会查询在 2023-06-01 这个时间点，2023-06-01 到 2023-07-01 这个时间段内有效的产品。

4.5 双时态表的优缺点

优点：
- 既能自动维护历史数据，又能手动指定数据的有效时间段。
- 查询历史数据方便，只需要同时使用 FOR SYSTEM_TIME 子句和 PERIOD_OVERLAPS 函数。
缺点：
- 需要占用额外的存储空间。
- 更新数据需要手动更新 valid_from 和 valid_to 的值，比较麻烦。

第五部分：一些小技巧和注意事项

合理选择时态表的类型： 根据实际需求选择合适的时态表类型。如果只需要自动维护历史数据，可以选择系统版本表。如果需要手动指定数据的有效时间段，可以选择应用时间段表。如果两者都需要，可以选择双时态表。
注意性能问题： 时态表会占用额外的存储空间，并且查询历史数据可能会比较慢。因此，需要合理设计表结构，并对查询语句进行优化。
定期清理历史数据： 历史数据会不断增长，占用越来越多的存储空间。因此，需要定期清理历史数据，只保留必要的数据。可以使用 ALTER TABLE ... DROP SYSTEM VERSIONING 语句删除历史表。

总结：

MySQL 8.0 的 Temporal Table 是一个非常强大的功能，可以帮助我们更好地管理和查询历史数据。掌握了 Temporal Table，你就可以在数据江湖里横着走了！

表格总结三种时态表的区别：

特性	系统版本表 (System-versioned)	应用时间段表 (Application-time)	双时态表 (Bi-temporal)
历史数据维护	系统自动	应用手动	系统自动 + 应用手动
时间段定义	无需定义	需要定义 `start_date`, `end_date`	需要定义 `start_date`, `end_date`
存储空间	较高 (需要历史表)	较低 (无需历史表)	较高 (需要历史表)
易用性	较高	较低	中等
查询复杂度	较低	较高	中等
适用场景	需要自动记录所有变更	需要手动控制数据有效时间	两者都需要

好了，今天的讲座就到这里。希望大家有所收获，以后在工作中能够灵活运用 Temporal Table，让数据管理更加轻松高效！谢谢大家！

发表回复 取消回复

发表回复取消回复