临时表
适用于 ❌ 开源版 ✅ Express 版 ✅ 专业版 ✅ 企业版
SQL:2011 标准化了一个名为时间有效性的特性，它通过时间表以两种形式实现
- 系统版本表
- 应用程序版本表
包括 DB2、MariaDB、Oracle、SQL Server 在内的一些方言，通过标准或供应商特定的语法实现了一种或另一种，或两种类型的时间表。
系统版本表
系统版本表可用于自动备份或审计表中所有内容。 记录的每个“版本”都有一个有效期，直到记录更新或删除，此时将创建一个新的记录“版本”。
考虑以下表格（请参阅数据库手册以获取实际语法。对数据类型以及历史表的管理方式存在限制。）
CREATE TABLE product_price ( product_id BIGINT NOT NULL PRIMARY KEY, price DECIMAL NOT NULL, start_ts TIMESTAMP GENERATED ALWAYS AS ROW START, end_ts TIMESTAMP GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (start_ts, end_ts) );
使用上述（简化）语法定义的表现在可以如下在 DML 语句中使用
-- At time T1, a new product is created: INSERT INTO product (product_id, price) VALUES (1, 100.00); -- Later, at time T2, the price is updated: UPDATE product SET price = 200.00 WHERE product_id = 1;
create.insertInto(PRODUCT, PRODUCT.PRODUCT_ID, PRODUCT.PRICE)
.values(1, new BigDecimal("100.00"))
.execute();
create.update(PRODUCT)
.set(PRODUCT.PRICE, new BigDecimal("200.00"))
.where(PRODUCT.PRODUCT_ID.eq(1))
.execute();
由于系统版本控制，UPDATE 语句不再是“破坏性的”，这意味着包含 (1, 100.00) 价格信息的原始行仍然可以从存档中获得。 我们可以如下查询 PRODUCT 表及其存档（请参阅下面的示例查询结果）
-- 1. Get the current version by default SELECT * FROM product; -- 2. Get the version at a given time SELECT * FROM product FOR system_time AS OF :t1; -- 3. Get several versions overlapping a time range [t1, t2] SELECT * FROM product FOR system_time BETWEEN :t1 AND :t2; -- 4. Get several versions overlapping a time range [t1, t2) SELECT * FROM product FOR system_time FROM :t1 TO :t2; -- 5. Get several versions included in a time range [t1, t2] SELECT * FROM product FOR system_time CONTAINED IN (:t1, :t2); -- 6. Get all versions SELECT * FROM product FOR system_time ALL;
// Get the current version by default create.selectFrom(product).fetch(); create.selectFrom(product.for_( systemTime().asOf(t1) )).fetch(); create.selectFrom(product.for_( systemTime().between(t1).and(t2)) )).fetch(); create.selectFrom(product.for_( systemTime().from(t1).to(t2) )).fetch(); create.selectFrom(product.for_( systemTime().containedIn(t1, t2) )).fetch(); create.selectFrom(product.for_( systemTime().all() )).fetch();
上述查询的结果可能如下所示
+----------+------------+-------+----------+--------+ | QUERY_NO | PRODUCT_ID | PRICE | START_TS | END_TS | +----------+------------+-------+----------+--------+ | 1 | 1 | 200 | T2 | | +----------+------------+-------+----------+--------+ +----------+------------+-------+----------+--------+ | QUERY_NO | PRODUCT_ID | PRICE | START_TS | END_TS | +----------+------------+-------+----------+--------+ | 2, 4, 5 | 1 | 100 | T1 | T2 | +----------+------------+-------+----------+--------+ +----------+------------+-------+----------+--------+ | QUERY_NO | PRODUCT_ID | PRICE | START_TS | END_TS | +----------+------------+-------+----------+--------+ | 3, 6 | 1 | 100 | T1 | T2 | | 3, 6 | 1 | 200 | T2 | | +----------+------------+-------+----------+--------+
如果底层方言也原生支持，jOOQ 3.13 仅支持上述语法。 未来的 jOOQ 版本也可能在其他方言中模拟语法，或者在不支持特定子句的地方模拟语法。
应用程序版本表
虽然系统版本表允许实现备份和审计日志，但从业务角度来看，某些数据自然是版本化的。 也许，我们可能希望指定给定价格在给定产品上有效的有效期范围，而不是仅仅存档定价信息。 这样，如果我们需要出于报告或会计原因，我们可以准确地恢复旧期间的旧价格。 （简化的）语法与系统版本表几乎相同，除了我们可以使用我们自己的（或者如果是 DB2，则使用 BUSINESS_TIME），而不是使用“魔术” SYSTEM_TIME 期间名称。 请再次查阅您的方言手册，以了解确切的语法
CREATE TABLE product_price ( product_id BIGINT NOT NULL PRIMARY KEY, price DECIMAL NOT NULL, start_ts TIMESTAMP, end_ts TIMESTAMP, PERIOD FOR validity (start_ts, end_ts) );
使用上述（简化）语法定义的表现在可以如下在 DML 语句中使用
-- A new product is created INSERT INTO product (product_id, price) VALUES (1, 100.00); -- For the time between [t1, t2], a discount is applied UPDATE product FOR PORTION OF validity FROM t1 TO t2 SET price = 50.00 WHERE product_id = 1;
create.insertInto(PRODUCT, PRODUCT.PRODUCT_ID, PRODUCT.PRICE)
.values(1, new BigDecimal("100.00"))
.execute();
create.update(PRODUCT.forPortionOf(
period(unquotedName("validity")).from(t1).to(t2)))
.set(PRODUCT.PRICE, new BigDecimal("50.00"))
.where(PRODUCT.PRODUCT_ID.eq(1))
.execute();
如果未与系统版本控制结合使用，这又是一个破坏性的 UPDATE 语句，它有效地转换为多个语句。 生成的表格内容现在如下所示
+------------+-------+----------+--------+ | PRODUCT_ID | PRICE | START_TS | END_TS | +------------+-------+----------+--------+ | 1 | 100.0 | | T1 | | 1 | 50.0 | T1 | T2 | | 1 | 100.0 | T2 | | +------------+-------+----------+--------+
根据您的方言，您可以在 SELECT 语句中重用之前的 FOR 子句，例如
-- 2. Get the version at a given time SELECT * FROM product FOR validity AS OF :t1;
create.selectFrom(product.for_(
period(unquotedName("validity")).asOf(t1)
)).fetch();
这将生成您的属性值，给定它们在给定时间戳 T1 的有效性
+------------+-------+----------+--------+ | PRODUCT_ID | PRICE | START_TS | END_TS | +------------+-------+----------+--------+ | 1 | 50.0 | T1 | T2 | +------------+-------+----------+--------+
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见！