背景
- 经常看到 SQL-92，SQL-03 的词汇，但是不了解其中的差异，比如 SQL-03 比 SQL-92 多了些什么?
- 知道不同的数据库有不同的方言，但是不了解哪些是 SQL 标准中定义的大家都遵守的。
- 开发一个类 SQL 语言时，选择支持哪些语法好呢？
本文从 ANSI SQL 切入，来调研一下 ANSI SQL 标准(下面简称为 SQL 标准)的演变，以及不同数据库对 SQL 标准的支持程度。
历程
通过 SQL Wikipedia 上了解到：
由于不同的数据库产商的 SQL 语法不兼容，因此 1986 年开始，ANSI 和 ISO 标准化组织采纳了 SQL 语言标准。并且在随后的年份陆续公布了新的标准。
具体年份和名称如下：
| 年份 | 名称 | 备注 |
|---|---|---|
| 1986 | SQL-86 | 第一版 |
| 1989 | SQL-89 | 小修改 |
| 1992 | SQL-92 | 大修改 |
| 1999 | SQL:1999 | 加入嵌套表(nested table[4]), 加入正则表达式, 递归查询，触发器，过程和控制流，数组类型和一些结构化类型 |
| 2003 | SQL:2003 | 加入 XML 相关特征，窗口函数，标准化序列(standardized sequences)，自动生成值的列 |
| 2006 | SQL:2006 | 支持导入，存储和操作 XML 数据，加入 XQuery |
| 2008 | SQL:2008 | 支持 cursor 定义外的 ORDER BY?, 增加 INSTEAD OF 触发器，增加 TRUNCATE，增加 FETCH |
| 2011 | SQL:2011 | 增加 temporal data，增强 window functions 和 FETCH |
| 2016 | SQL:2016 | 增加列特征匹配(row pattern matching), 多态表函数(polymorphic table functions), JSON |
标准公开出来后，不同的产商的实现情况也是不兼容的，即不完全遵守标准。主要是日期类型，时间类型，字符串拼接，NULL 和大小写敏感。
只有 PostgreSQL 和 Mimer SQL 是紧跟标准的。
变化
请找一个充足的时间，打开 Modern SQL: Slides，从头到尾看一遍。
或者打开 Modern SQL in Open Source and Commercial Databases，下载下来看。
这里做一下读书笔记
LATERAL
来自 SQL:1999
依旧拿 PostgreSQL 初体验中的示例数据举例，现在有两张表 cities 和 weather 如下：
mydb=# select * from cities;
name | location
---------------+-----------
San Francisco | (-194,53)
(1 row)
mydb=# select * from weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)
JOIN LATERAL
现在两张表要做一个 JOIN
在 SQL:1999 之前，只能这么写：
SELECT *
FROM cities
JOIN (
SELECT *
FROM weather
) inline_view
ON inline_view.city = cities.name
执行结果
name | location | city | temp_lo | temp_hi | prcp | date
---------------+-----------+---------------+---------+---------+------+------------
San Francisco | (-194,53) | San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | (-194,53) | San Francisco | 46 | 50 | 0.25 | 1994-11-27
(2 rows)
在 SQL:1999 之后，可以这样写：
SELECT *
FROM cities
JOIN lateral(
SELECT *
FROM weather
WHERE weather.city = cities.name
) inline_view ON true
结果
name | location | city | temp_lo | temp_hi | prcp | date
---------------+-----------+---------------+---------+---------+------+------------
San Francisco | (-194,53) | San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | (-194,53) | San Francisco | 46 | 50 | 0.25 | 1994-11-27
(2 rows)
注意两点：
- 在
inline_view中，引用了外部的 cities - JOIN 使用了 join lateral 关键字，而不仅仅是 join
好奇一下内部的执行过程是不是一样的，使用 explain 来看一下
EXPLAIN SELECT *
FROM cities
JOIN (
SELECT *
FROM weather
) inline_view
ON inline_view.city = cities.name;
结果
QUERY PLAN
------------------------------------------------------------------------
Hash Join (cost=18.10..55.28 rows=648 width=388)
Hash Cond: ((cities.name)::text = (weather.city)::text)
-> Seq Scan on cities (cost=0.00..13.60 rows=360 width=194)
-> Hash (cost=13.60..13.60 rows=360 width=194)
-> Seq Scan on weather (cost=0.00..13.60 rows=360 width=194)
(5 rows)
EXPLAIN SELECT *
FROM cities
JOIN lateral(
SELECT *
FROM weather
WHERE weather.city = cities.name
) inline_view ON true;
结果
QUERY PLAN
------------------------------------------------------------------------
Hash Join (cost=18.10..55.28 rows=648 width=388)
Hash Cond: ((cities.name)::text = (weather.city)::text)
-> Seq Scan on cities (cost=0.00..13.60 rows=360 width=194)
-> Hash (cost=13.60..13.60 rows=360 width=194)
-> Seq Scan on weather (cost=0.00..13.60 rows=360 width=194)
(5 rows)
这两个的实际执行过程是一样的。
CROSS JOIN LATERAL
使用 cross join lateral 也可以实际同样的效果，同时可以不加末尾的 on true
SELECT *
FROM cities
CROSS JOIN lateral(
SELECT *
FROM weather
WHERE weather.city = cities.name
) inline_view;
结果
name | location | city | temp_lo | temp_hi | prcp | date
---------------+-----------+---------------+---------+---------+------+------------
San Francisco | (-194,53) | San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | (-194,53) | San Francisco | 46 | 50 | 0.25 | 1994-11-27
(2 rows)
内部执行过程仍然是相同的
EXPLAIN SELECT *
FROM cities
CROSS JOIN lateral(
SELECT *
FROM weather
WHERE weather.city = cities.name
) inline_view;
结果
QUERY PLAN
------------------------------------------------------------------------
Hash Join (cost=18.10..55.28 rows=648 width=388)
Hash Cond: ((cities.name)::text = (weather.city)::text)
-> Seq Scan on cities (cost=0.00..13.60 rows=360 width=194)
-> Hash (cost=13.60..13.60 rows=360 width=194)
-> Seq Scan on weather (cost=0.00..13.60 rows=360 width=194)
(5 rows)
意义
这种写法有什么意义呢？比原来的写法的优越性在哪里呢？
一方面是 LATERAL 和 table functions 可以联合使用。这个目前是 PostgreSQL 中特有的，本文略过。
LATERAL & LIMIT
另一方面是 可以在 inline view 中加入 LIMIT 操作，比如：
SELECT *
FROM cities
CROSS JOIN lateral(
SELECT *
FROM weather
WHERE weather.city = cities.name
ORDER BY temp_lo
LIMIT 1
) inline_view;
结果
name | location | city | temp_lo | temp_hi | prcp | date
---------------+-----------+---------------+---------+---------+------+------------
San Francisco | (-194,53) | San Francisco | 43 | 57 | 0 | 1994-11-29
(1 row)
如果不使用 LATERAL 的话，是无法实现只取某一列的前 LIMIT 行的。
LATERAL & Multi-Source Top-N
场景：用户找出每个分类下最新的 10 条新闻。
SELECT n.*
FROM news n
JOIN subscriptions s
ON n.topic = s.topic
WHERE s.user = ?
ORDER BY n.created DESC
LIMIT 10
这样会把每个 subscriptions 下的所有的 news 都找出来，但是是没有必要的。
使用 LATERAL 的写法如下：
SELECT n.*
FROM subscriptions s
JOIN LATERAL(
SELECT *
FROM news n
WHERE n.topic = s.topic
ORDER BY n.created DESC
LIMIT 10
) top_news ON true
WHERE s.user_id = ?
ORDER BY n.created DESC
LIMIT 10;
这样可以做到只取每个 news 下的前 10 条新闻.
小结
- SQL 中的"FOR EACH"
- 适合与 OUTER JOINS 联用
- 适合取子查询中的 Top-N
- 可以与 table functions 作 join
支持的数据库
- DB2 LUW 9.1+
- Oracle 12c+
- PostgreSQL 9.3+
- SQL Server 2005 部分支持
- MySQL 不支持
- SQLite 不支持
WITH (Common Table Expressions)
来自 SQL:1999
WITH 是解决什么问题的呢？
背景
OK，先来看一个在 SQL:1999 之前的场景：
当遇到多层嵌套的 SQL 时，我们会这样写。
SELECT ...
FROM (SELECT ...
FROM t1
JOIN (SELECT ... FROM ...
) a ON (...)
) b
JOIN (SELECT ... FROM ...
) c ON (...)
下面的 SQL 是为了示例而示例：
SELECT *
FROM (
SELECT *
FROM cities
) a
JOIN (
SELECT *
FROM weather
) b
ON a.name = b.city;
结果
name | location | city | temp_lo | temp_hi | prcp | date
---------------+-----------+---------------+---------+---------+------+------------
San Francisco | (-194,53) | San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | (-194,53) | San Francisco | 46 | 50 | 0.25 | 1994-11-27
(2 rows)
当去理解这样的 SQL 语句时，需要先理解最里面的 SELECT，再理解次里面的 SELECT，一层一层的向外看，最后再理解最外层的 SELECT。在 SQL92 时，就是这样来做的。
如果有了 WITH CTEs，就可以用更易理解的方式来写 SQL 了。
语法
一个 CTE
WITH
a (c1, c2, c3)
AS (SELECT c1, c2, c3 FROM ...)
SELECT ...
两个 CTEs
WITH
a (c1, c2, c3)
AS (SELECT c1, c2, c3 FROM ...),
b (c4, ...)
AS (SELECT c4, ...
FROM t1
JOIN a
ON(...)
)
SELECT ...
- 多个 CTE 之间由逗号连接，只有第一个 CTE 前有 WITH 关键字，后面的 CTE 不需要 WITH 关键字。
- 在第二个 CTE 中可以引用第一个 CTE，即在后面的 CTE 可以引用前面的 CTE。
- 最后一个 CTE 后没有逗号，表明后面跟着的是查询语句。
这样的话，看 SQL 时就可以从上往下来看了。
将上面的示例重写一下：
WITH a (name, location) AS (
SELECT name, location
FROM cities
),
b (city, temp_lo, temp_hi, prcp, date) AS (
SELECT city, temp_lo, temp_hi, prcp, date
FROM weather
)
SELECT *
FROM a
JOIN b ON a.name = b.city;
结果如下：
name | location | city | temp_lo | temp_hi | prcp | date
---------------+-----------+---------------+---------+---------+------+------------
San Francisco | (-194,53) | San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | (-194,53) | San Francisco | 46 | 50 | 0.25 | 1994-11-27
(2 rows)
小结
- WITH 是 SQL 中的"私有方法 private methods"
- WITH 视图可以多次被引用
- WITH 通过定义多个 CTE，来实现嵌套
- 可以用 SELECT 的地方，就可以用 WITH，如
INSERT INTO tbl WITH ... SELECT
坑
下推(push down)
需要注意的是，在 PostgreSQL 中，WITH 视图更像是一个物化视图，没有把查询中的过滤条件下推到视图中。
在数据量大时，可能会有性能问题。使用时请用 EXPLAIN 来仔细检查一下。
但是在 PostgreSQL 中使用 inline 视图时，会有下推的操作的。
上推(push up)
PostgreSQL 9.1+ 中，INSERT, UPDATE 和 DELETE 中也支持 WITH。
如将指定行从一个表移动到另一个表
WITH deleted_rows AS(
DELETE FROM source_tbl
RETURNING *
)
INSERT INTO destination_tbl
SELECT * FROM deleted_rows
支持的数据库
- DB2 LUW 7+
- Oracle 9iR2+
- PostgreSQL 8.4+
- SQL Server 2005+
- SQLite 3.8.3+
- MySQL 8.0+
WITH RECURSIVE (Common Table Expressions)
来自 SQL:1999
从一个例子来看起。
生成 1 到 3 的数字
WITH RECURSIVE cte (n)
AS (SELECT 1
UNION ALL
SELECT n+1
FROM cte
WHERE n < 3)
SELECT * FROM cte;
结果
n
---
1
2
3
(3 rows)
这个是怎么实现的呢？
首先其中的 SELECT 中有两项，由 UNION (ALL)分开: 初始项和递归项，见 SELECT 初始项 UNION ALL 递归项
初始时，由初始项产生临时表
随时，临时表做为递归项的输入表，再生产新的临时表
直到新的临时表为空时结束
这样，就形成了一个循环。
来看更多的例子
生成 2 到 4 的数字
WITH RECURSIVE cte (n)
AS (SELECT 2
UNION ALL
SELECT n + 1
FROM cte
WHERE n < 4)
SELECT * FROM cte;
结果
n
---
2
3
4
(3 rows)
生成 1,3,5 的数字
WITH RECURSIVE cte (n)
AS (SELECT 1
UNION ALL
SELECT n + 2
FROM cte
WHERE n < 5)
SELECT * FROM cte;
结果
n
---
1
3
5
(3 rows)
阶乘
再看一个求阶乘的例子
WITH RECURSIVE factorial(F,n) AS (
SELECT 1 F, 4 n
UNION ALL
SELECT F*n F, n-1 n
FROM factorial
WHERE n > 1)
SELECT * FROM factorial;
结果
f | n
----+---
1 | 4
4 | 3
12 | 2
24 | 1
(4 rows)
即 4 的阶乘为 24。
求树的子节点
构造一颗树，每一个节点有自己编号 id, 和其父节点的编号 parent_id
建表
CREATE TABLE t (
id NUMERIC NOT NULL,
parent_id NUMERIC,
PRIMARY KEY (id)
)
构造数据
INSERT INTO t VALUES (1, null);
INSERT INTO t VALUES (2, 1);
INSERT INTO t VALUES (3, 1);
INSERT INTO t VALUES (4, 1);
INSERT INTO t VALUES (5, 1);
INSERT INTO t VALUES (6, 2);
INSERT INTO t VALUES (7, 3);
INSERT INTO t VALUES (8, 3);
INSERT INTO t VALUES (9, 4);
INSERT INTO t VALUES (10, 5);
INSERT INTO t VALUES (11, 6);
INSERT INTO t VALUES (12, 8);
INSERT INTO t VALUES (13, 8);
INSERT INTO t VALUES (14, 8);
INSERT INTO t VALUES (15, 8);
INSERT INTO t VALUES (16, 10);
查看一下表中的数据
mydb=# select * from t;
id | parent_id
----+-----------
1 |
2 | 1
3 | 1
4 | 1
5 | 1
6 | 2
7 | 3
8 | 3
9 | 4
10 | 5
11 | 6
12 | 8
13 | 8
14 | 8
15 | 8
16 | 10
(16 rows)
现在来找到节点 3 其及所有子节点。
传统写法
可以使用多个 LEFT JOIN 来写成，同时自己的结果再进行二次处理。
SELECT d0.*, d1.*, d2.*
FROM t AS d0
LEFT JOIN t AS d1
ON (d1.parent_id = d0.id)
LEFT JOIN t AS d2
ON (d2.parent_id = d1.id)
WHERE d0.id = 3
结果如下：
id | parent_id | id | parent_id | id | parent_id
----+-----------+----+-----------+----+-----------
3 | 1 | 8 | 3 | 12 | 8
3 | 1 | 8 | 3 | 13 | 8
3 | 1 | 8 | 3 | 14 | 8
3 | 1 | 8 | 3 | 15 | 8
3 | 1 | 7 | 3 | |
(5 rows)
WITH RECURSIVE CTEs 写法
WITH RECURSIVE subtree (id, parent_id) AS
(
SELECT id, parent_id
FROM t
WHERE id = 3
UNION ALL
SELECT t.id, t.parent_id
FROM subtree
LEFT JOIN t
ON (t.parent_id = subtree.id)
WHERE t.id <= 20
)
SELECT * FROM subtree;
结果
id | parent_id
----+-----------
3 | 1
7 | 3
8 | 3
12 | 8
13 | 8
14 | 8
15 | 8
(7 rows)
小结
- SQL 中的 while 循环
- 列生成器
- 图处理器
- 可以将数据传递到下一个迭代中
- 需要有终止条件，否则容易死循环
支持的数据库
- DB2 LUV 7+
- Oracle 11.gR2+
- PostgreSQL 8.4+
- SQL Server 2005+
- SQLite 3.8.3+
- MySQL 不支持
OVER/PARTITION BY
来自 SQL:2003
先从一个场景来说起，如何实现部分收入百分比呢？
构造数据
CREATE TABLE empsalary(
depname varchar,
empno bigint,
salary int,
enroll_date date
);
INSERT INTO empsalary VALUES('develop',10, 5200, '2007/08/01');
INSERT INTO empsalary VALUES('sales', 1, 5000, '2006/10/01');
INSERT INTO empsalary VALUES('personnel', 5, 3500, '2007/12/10');
INSERT INTO empsalary VALUES('sales', 4, 4800, '2007/08/08');
INSERT INTO empsalary VALUES('sales', 6, 5500, '2007/01/02');
INSERT INTO empsalary VALUES('personnel', 2, 3900, '2006/12/23');
INSERT INTO empsalary VALUES('develop', 7, 4200, '2008/01/01');
INSERT INTO empsalary VALUES('develop', 9, 4500, '2008/01/01');
INSERT INTO empsalary VALUES('sales', 3, 4800, '2007/08/01');
INSERT INTO empsalary VALUES('develop', 8, 6000, '2006/10/01');
INSERT INTO empsalary VALUES('develop', 11, 5200, '2007/08/15');
SQL:2003 之前解法
在 SQL:2003 之前
WITH total_salary_by_department
AS (SELECT depname, SUM(salary) total
FROM empsalary
GROUP BY depname)
SELECT ts.depname, empno, salary,
100 * salary/ts.total "% of dep"
FROM empsalary
JOIN total_salary_by_department ts
ON (empsalary.depname = ts.depname)
ORDER BY depname, "% of dep" desc;
结果
develop | 8 | 6000 | 23
develop | 10 | 5200 | 20
develop | 11 | 5200 | 20
develop | 9 | 4500 | 17
develop | 7 | 4200 | 16
personnel | 2 | 3900 | 52
personnel | 5 | 3500 | 47
sales | 6 | 5500 | 27
sales | 1 | 5000 | 24
sales | 3 | 4800 | 23
sales | 4 | 4800 | 23
如果只看某一个部门的呢？
WITH total_salary_by_department
AS (SELECT depname, SUM(salary) total
FROM empsalary
GROUP BY depname)
SELECT ts.depname, empno, salary,
100 * salary/ts.total "% of dep"
FROM empsalary
JOIN total_salary_by_department ts
ON (empsalary.depname = ts.depname)
WHERE empsalary.depname = 'develop'
ORDER BY depname, "% of dep" desc;
结果
develop | 8 | 6000 | 23
develop | 10 | 5200 | 20
develop | 11 | 5200 | 20
develop | 9 | 4500 | 17
develop | 7 | 4200 | 16
好吧，这个又是上面提到的 PostgreSQL 的性能问题。
在 SQL:2003 以前，想实现 Aggregation 的话，只有通过 DISTINCT 和 GROUP BY 这两种方法。如果不想选项被合并的话，那就只有 GROUP BY 一种方法。
SQL:2003
在 SQL:2003 中，引入了 OVER(PARTITION BY)语法，来解法此类问题。
如上面的 SQL，可以写为：
SELECT depname,
empno,
salary,
100 * salary / SUM(salary)
OVER(PARTITION BY depname) "% of depname"
FROM empsalary
ORDER BY
depname,
"% of depname" DESC;
结果
develop | 8 | 6000 | 23
develop | 10 | 5200 | 20
develop | 11 | 5200 | 20
develop | 9 | 4500 | 17
develop | 7 | 4200 | 16
personnel | 2 | 3900 | 52
personnel | 5 | 3500 | 47
sales | 6 | 5500 | 27
sales | 1 | 5000 | 24
sales | 3 | 4800 | 23
sales | 4 | 4800 | 23
这里先选择了 salary 列，然后选择对 salary 列做 SUM，同时通过 PARTIITON BY 关键字指定了 SUM 的窗口是针对 dep 列。
小结
- OVER 可以与任何聚合函数搭配作用
- 略
- 略
- OVER (PARTITION BY X)可以实现类似 GROUP BY 的效果
OVER/ORDER BY
收支场景：
有一张表 transactions 记录着自己的收支情况。现在需要显示收支的明细和对应的余额。
构造数据
CREATE TABLE transactions(
txid int,
value numeric
);
INSERT INTO transactions VALUES(1, +10);
INSERT INTO transactions VALUES(2, +20);
INSERT INTO transactions VALUES(3, -10);
INSERT INTO transactions VALUES(4, +50);
INSERT INTO transactions VALUES(5, -30);
INSERT INTO transactions VALUES(6, -20);
查看如下
mydb=# select * from transactions;
1 | 10
2 | 20
3 | -10
4 | 50
5 | -30
6 | -20
SQL:2003 之前
SELECT txid,
value,
(SELECT SUM(value)
FROM transactions tx2
WHERE tx2.txid <= tx1.txid) balance
FROM transactions tx1
ORDER BY txid;
结果
1 | 10 | 10
2 | 20 | 30
3 | -10 | 20
4 | 50 | 70
5 | -30 | 40
6 | -20 | 20
SQL:2003
SELECT txid,
value,
SUM(value)
OVER(ORDER BY txid
ROWS
BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) balance
FROM transactions tx1
ORDER BY txid;
结果
1 | 10 | 10
2 | 20 | 30
3 | -10 | 20
4 | 50 | 70
5 | -30 | 40
6 | -20 | 20
新的方法
ROW_NUMBER
排序方法
RANKDENSE_RANKPERCENT_RANKCUME_DIST
支持的数据库
- DB2 LUW: 7+
- Oracle: 8i+
- PostgreSQL: 8.4+
- SQL Server: 2005+
- MySQL: 不支持
- SQLite: 不支持
OVER/LAG
SQL:2008
场景：显示与前一项的差值。和上面的场景正好相反。
SQL:2008 前
显示 ROW_NUMBER
SELECT *, ROW_NUMBER() OVER (ORDER BY txid) rn FROM transactions;
1 | 10 | 1
2 | 20 | 2
3 | -10 | 3
4 | 50 | 4
5 | -30 | 5
6 | -20 | 6
然后使用上面提到的 SQL:2003 的 WITH 方法，来求差值。
WITH numbered_data AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY txid) rn
FROM transactions
)
SELECT cur.txid, cur.value, cur.value - prev.value
FROM numbered_data cur
LEFT JOIN numbered_data prev
ON (cur.rn - 1 = prev.rn);
结果
1 | 10 |
2 | 20 | 10
3 | -10 | -30
4 | 50 | 60
5 | -30 | -80
6 | -20 | 10
SQL:2008
SELECT *,
value - LAG(value)
OVER (ORDER BY txid)
FROM transactions;
结果：
1 | 10 |
2 | 20 | 10
3 | -10 | -30
4 | 50 | 60
5 | -30 | -80
6 | -20 | 10
其它
同样的还有：
LEAD / LAG
FIRST_VALUE / LAST_VALUE
MIN_VALUE(col, n) FROM FIRST/LAST
RESPECT/IGNORE NULLS
支持的数据库
- DB2 LUW 9.5+
- Oracle: 11gR2+
- PostgreSQL 8.4+
- SQL Server 2012+
- MySQL: 不支持
FETCH FIRST
SQL:2008
场景：找到选定行的前有限列
SQL:2008 之前
SELECT *
FROM (SELECT *,
ROW_NUMBER() OVER (ORDER BY txid) rn
FROM transactions) numbered_data
WHERE rn <= 3;
结果
1 | 10 | 1
2 | 20 | 2
3 | -10 | 3
当然也可以使用非标准的语法，如 LIMIT, TOP。
SQL:2008 之后
SELECT *
FROM transactions
ORDER BY txid
FETCH FIRST 3 ROWS ONLY;
结果
1 | 10
2 | 20
3 | -10
支持的数据库
- DB2 LUW 7+
- MySQL 3.19.3+
- Oracle 12c
- PostgreSQL 8.4+
- SQL Server 2012+
- SQLite 2.1.0+
OFFSET
SQL: 2011
这个很常见了，但是只是在 SQL:2011 中才引入。
支持的数据库
- DB2 LUW 9.7+
- MySQL 4.0.6+
- Oracle 12c+
- PostgreSQL 6.5+
- SQL Server 2012+
- SQLite 2.1.0+
AS OF
场景：记录数据记录的产生时间
SQL:2011 中
CREATE TABLE t( ...,
start_ts TIMESTAMP(9) GENERATED
ALWAYS AS ROW START,
end_ts TIMESTAMP(9) GENERATED
ALWAYS AS ROW END,
PERIOD FOR SYSTEM TIME (start_ts, end_ts)
) WITH SYSTEM VERSIONING
写入
INSERT ... (ID, DATA) VALUES (1, 'X')
SQL:2011
其中的 start_ts 和 end_ts 会自动修改，对应用透明。
支持的数据库
- DB2 LUW 10.1+
- Oracle 10gR1+
- MySQL 不支持
- PostgreSQL 不支持
- SQL Server 不支持
- SQLite 不支持
WITHOUT OVERLAPS
场景：每行日志有起止时间，要求约束起止时间不重叠。
SQL:2011
SQL:2011 中引入了 temporal 类型和 bi-temporal 类型，如：
PRIMARY KEY (id, period WITHOUT OVERLAPS)
支持的数据库
- DB2 LUW 10.1+
- PostgreSQL 9.2+
- MySQL 不支持
- Oracle 不支持
- SQL Server 不支持
- SQLite 不支持
欢迎来到这里！
我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。
注册 关于