【SQL揭秘】有多少種數(shù)據(jù)庫,就有多少類CTE

Common Table Expression

Common table expression簡稱CTE，由SQL:1999標(biāo)準(zhǔn)引入，可以認(rèn)為是在單個(gè) SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句的執(zhí)行范圍內(nèi)定義的臨時(shí)結(jié)果集。CTE 與派生表類似，具體表現(xiàn)在不存儲(chǔ)為對(duì)象，并且只在查詢期間有效。與派生表的不同之處在于，CTE 可自引用，還可在同一查詢中引用多次。

目前支持CTE的數(shù)據(jù)庫有Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), MariaDB (since 10.2), SQLite (since 3.8.3), HyperSQL and H2 (experimental), MySQL8.0.

CTE的語法如下：

1、Non-recursive CTEs?

2、Recursive CTEs?

CTE的使用

CTE使語句更加簡潔

例如以下兩個(gè)語句表達(dá)的是同一語義，使用CTE比未使用CTE的嵌套查詢更簡潔明了。

1) 使用嵌套子查詢

2) 使用CTE

CTE 可以進(jìn)行樹形查詢

初始化這顆樹

1) 層序遍歷

2) 深度優(yōu)先遍歷

Oracle

Oracle從9.2才開始支持CTE, 但只支持non-recursive with, 直到Oracle 11.2才完全支持CTE。但oracle 之前就支持connect by 的樹形查詢，recursive with 語句可以與connect by語句相互轉(zhuǎn)化。 一些相互轉(zhuǎn)化案例可以參考這里.

Oracle recursive with 語句不需要指定recursive關(guān)鍵字，可以自動(dòng)識(shí)別是否recursive.Oracle 還支持CTE相關(guān)的hint,

“MATERIALIZE”告訴優(yōu)化器產(chǎn)生一個(gè)全局的臨時(shí)表保存結(jié)果，多次引用CTE時(shí)直接訪問臨時(shí)表即可。而”INLINE”則表示每次需要解析查詢CTE。

PostgreSQL

PostgreSQL從8.4開始支持CTE，PostgreSQL還擴(kuò)展了CTE的功能， CTE的query中支持DML語句，例如

MariaDB

MariaDB從10.2開始支持CTE。10.2.1 支持non-recursive CTE, 10.2.2開始支持recursive CTE。 目前的GA的版本是10.1.

MySQL

MySQL從8.0開始支持完整的CTE。MySQL8.0還在development
階段，RC都沒有，GA還需時(shí)日。

AliSQL

AliSQL基于mariadb10.2， port了no-recursive CTE的實(shí)現(xiàn)，此功能近期會(huì)上線。

以下從源碼主要相關(guān)函數(shù)簡要介紹其實(shí)現(xiàn)，

//解析識(shí)別with table引用?
find_table_def_in_with_clauses

//檢查依賴關(guān)系，比如不能重復(fù)定義with table名字?
With_clause::check_dependencies

// 為每個(gè)引用clone一份定義?
With_element::clone_parsed_spec

//替換with table指定的列名?
With_element::rename_columns_of_derived_unit

此實(shí)現(xiàn)對(duì)于多次引用CTE，CTE會(huì)解析多次，因此此版本CTE有簡化SQL的作用，但效率上沒有效提高。

select count(*) from t1 where c2 !='z';

+----------+

| count(*) |

+----------+

| ? ?65536 |

+----------+

1 row in set (0.25 sec)

//從執(zhí)行時(shí)間來看是進(jìn)行了3次全表掃描

?with t as (select count(*) from t1 where c2 !='z')

? ? ?select * from t union select * from t union select * from t;

+----------+

| count(*) |

+----------+

| ? ?65536 |

+----------+

1 row in set (0.59 sec)

?select count(*) from t1 where c2 !='z'

? ? ?union

? ? ?select count(*) from t1 where c2 !='z'

? ? ?union

? ? select count(*) from t1 where c2 !='z';

+----------+

| count(*) |

+----------+

| ? ?65536 |

+----------+

1 row in set (0.57 sec)

explain ?select count(*) from t1 where c2 !='z'

? ? union

? ? select count(*) from t1 where c2 !='z'

? ? union

? ? select count(*) from t1 where c2 !='z';

以下是MySQL8.0 只掃描一次的執(zhí)行計(jì)劃

以下是PostgreSQL9.4 只掃描一次的執(zhí)行計(jì)劃

AliSQL還有待改進(jìn)。