一、概述

1. 公用表表達(dá)式（或通用表表達(dá)式）簡(jiǎn)稱(chēng)為CTE（Common Table Expressions）。
2. CTE是一個(gè)命名的臨時(shí)結(jié)果集，作用范圍是當(dāng)前語(yǔ)句。
3. CTE可以理解成一個(gè)可以復(fù)用的子查詢(xún)，當(dāng)然跟子查詢(xún)還是有點(diǎn)區(qū)別的，CTE可以引用其他CTE，但子查詢(xún)不能引用其他子查詢(xún)。所以，可以考慮代替子查詢(xún)。
4. 依據(jù)語(yǔ)法結(jié)構(gòu)和執(zhí)行方式的不同，公用表表達(dá)式分為普通公用表表達(dá)式和遞歸公用表表達(dá)式 2 種。

二、普通公用表表達(dá)式

1、語(yǔ)法結(jié)構(gòu)

普通公用表表達(dá)式的語(yǔ)法結(jié)構(gòu)是：

WITH CTE名稱(chēng) 
AS （子查詢(xún)）
SELECT|DELETE|UPDATE 語(yǔ)句;

普通公用表表達(dá)式類(lèi)似于子查詢(xún)，不過(guò)，跟子查詢(xún)不同的是，它可以被多次引用，而且可以被其他的普通公用表表達(dá)式所引用。

2、案例

舉例：查詢(xún)員工所在的部門(mén)的詳細(xì)信息。

mysql> SELECT * FROM departments
    -> WHERE department_id IN (
    ->                  SELECT DISTINCT department_id
    ->                  FROM employees
    ->                  );
+---------------+------------------+------------+-------------+
| department_id | department_name  | manager_id | location_id |
+---------------+------------------+------------+-------------+
|            10 | Administration   |        200 |        1700 |
|            20 | Marketing        |        201 |        1800 |
|            30 | Purchasing       |        114 |        1700 |
|            40 | Human Resources  |        203 |        2400 |
|            50 | Shipping         |        121 |        1500 |
|            60 | IT               |        103 |        1400 |
|            70 | Public Relations |        204 |        2700 |
|            80 | Sales            |        145 |        2500 |
|            90 | Executive        |        100 |        1700 |
|           100 | Finance          |        108 |        1700 |
|           110 | Accounting       |        205 |        1700 |
+---------------+------------------+------------+-------------+
11 rows in set (0.00 sec)

這個(gè)查詢(xún)也可以用普通公用表表達(dá)式的方式完成：

mysql> WITH emp_dept_id
    -> AS (SELECT DISTINCT department_id FROM employees)
    -> SELECT *
    -> FROM departments d JOIN emp_dept_id e
    -> ON d.department_id = e.department_id;
+---------------+------------------+------------+-------------+---------------+
| department_id | department_name  | manager_id | location_id | department_id |
+---------------+------------------+------------+-------------+---------------+
|            90 | Executive        |        100 |        1700 |            90 |
|            60 | IT               |        103 |        1400 |            60 |
|           100 | Finance          |        108 |        1700 |           100 |
|            30 | Purchasing       |        114 |        1700 |            30 |
|            50 | Shipping         |        121 |        1500 |            50 |
|            80 | Sales            |        145 |        2500 |            80 |
|            10 | Administration   |        200 |        1700 |            10 |
|            20 | Marketing        |        201 |        1800 |            20 |
|            40 | Human Resources  |        203 |        2400 |            40 |
|            70 | Public Relations |        204 |        2700 |            70 |
|           110 | Accounting       |        205 |        1700 |           110 |
+---------------+------------------+------------+-------------+---------------+
11 rows in set (0.00 sec)

1. 例子說(shuō)明，公用表表達(dá)式可以起到子查詢(xún)的作用。
2. 以后如果遇到需要使用子查詢(xún)的場(chǎng)景，你可以在查詢(xún)之前，先定義公用表表達(dá)式，然后在查詢(xún)中用它來(lái)代替子查詢(xún)。
3. 而且，跟子查詢(xún)相比，公用表表達(dá)式有一個(gè)優(yōu)點(diǎn)，就是定義過(guò)公用表表達(dá)式之后的查詢(xún)，可以像一個(gè)表一樣多次引用公用表表達(dá)式，而子查詢(xún)則不能。

三、遞歸公用表表達(dá)式

1、語(yǔ)法結(jié)構(gòu)

遞歸公用表表達(dá)式也是一種公用表表達(dá)式，只不過(guò)，除了普通公用表表達(dá)式的特點(diǎn)以外，它還有自己的特點(diǎn)，就是可以調(diào)用自己。

它的語(yǔ)法結(jié)構(gòu)是：

WITH RECURSIVE
CTE名稱(chēng) AS （子查詢(xún)）
SELECT|DELETE|UPDATE 語(yǔ)句;

1. 遞歸公用表表達(dá)式由 2 部分組成，分別是種子查詢(xún)和遞歸查詢(xún)，中間通過(guò)關(guān)鍵字 UNION [ALL]進(jìn)行連接。
2. 這里的種子查詢(xún)，意思就是獲得遞歸的初始值。這個(gè)查詢(xún)只會(huì)運(yùn)行一次，以創(chuàng)建初始數(shù)據(jù)集，之后遞歸查詢(xún)會(huì)一直執(zhí)行，直到?jīng)]有任何新的查詢(xún)數(shù)據(jù)產(chǎn)生，遞歸返回。

2、案例

案例：針對(duì)于我們常用的employees表，包含employee_id，last_name和manager_id三個(gè)字段。如果a是b的管理者，那么，我們可以把b叫做a的下屬，如果同時(shí)b又是c的管理者，那么c就是b的下屬，是a的下下屬。 下面我們嘗試用查詢(xún)語(yǔ)句列出所有具有下下屬身份的人員信息。

如果用我們之前學(xué)過(guò)的知識(shí)來(lái)解決，會(huì)比較復(fù)雜，至少要進(jìn)行 4 次查詢(xún)才能搞定：

• 第一步，先找出初代管理者，就是不以任何別人為管理者的人，把結(jié)果存入臨時(shí)表；
• 第二步，找出所有以初代管理者為管理者的人，得到一個(gè)下屬集，把結(jié)果存入臨時(shí)表；
• 第三步，找出所有以下屬為管理者的人，得到一個(gè)下下屬集，把結(jié)果存入臨時(shí)表。
• 第四步，找出所有以下下屬為管理者的人，得到一個(gè)結(jié)果集。

如果第四步的結(jié)果集為空，則計(jì)算結(jié)束，第三步的結(jié)果集就是我們需要的下下屬集了，否則就必須繼續(xù)進(jìn)行第四步，一直到結(jié)果集為空為止。比如上面的這個(gè)數(shù)據(jù)表，就需要到第五步，才能得到空結(jié)果集。而且，最后還要進(jìn)行第六步：把第三步和第四步的結(jié)果集合并，這樣才能最終獲得我們需要的結(jié)果集。

如果用遞歸公用表表達(dá)式，就非常簡(jiǎn)單了。我介紹下具體的思路。

• 用遞歸公用表表達(dá)式中的種子查詢(xún)，找出初代管理者。字段 n 表示代次，初始值為 1，表示是第一代管理者。
• 用遞歸公用表表達(dá)式中的遞歸查詢(xún)，查出以這個(gè)遞歸公用表表達(dá)式中的人為管理者的人，并且代次的值加 1。直到?jīng)]有人以這個(gè)遞歸公用表表達(dá)式中的人為管理者了，遞歸返回。
• 在最后的查詢(xún)中，選出所有代次大于等于 3 的人，他們肯定是第三代及以上代次的下屬了，也就是下下屬了。這樣就得到了我們需要的結(jié)果集。

這里看似也是 3 步，實(shí)際上是一個(gè)查詢(xún)的 3 個(gè)部分，只需要執(zhí)行一次就可以了。而且也不需要用臨時(shí)表保存中間結(jié)果，比剛剛的方法簡(jiǎn)單多了。

代碼實(shí)現(xiàn)：

WITH RECURSIVE cte 
AS 
(
SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100 -- 種子查詢(xún)，找到第一代領(lǐng)導(dǎo)
UNION ALL
SELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte
ON (a.manager_id = cte.employee_id) -- 遞歸查詢(xún)，找出以遞歸公用表表達(dá)式的人為領(lǐng)導(dǎo)的人
)
SELECT employee_id,last_name FROM cte WHERE n >= 3; 

總之，遞歸公用表表達(dá)式對(duì)于查詢(xún)一個(gè)有共同的根節(jié)點(diǎn)的樹(shù)形結(jié)構(gòu)數(shù)據(jù)，非常有用。它可以不受層級(jí)的限制，輕松查出所有節(jié)點(diǎn)的數(shù)據(jù)。如果用其他的查詢(xún)方式，就比較復(fù)雜了。

四、注意事項(xiàng)

公用表表達(dá)式的作用是可以替代子查詢(xún)，而且可以被多次引用。遞歸公用表表達(dá)式對(duì)查詢(xún)有一個(gè)共同根節(jié)點(diǎn)的樹(shù)形結(jié)構(gòu)數(shù)據(jù)非常高效，可以輕松搞定其他查詢(xún)方式難以處理的查詢(xún)。但在使用時(shí)需要時(shí)刻注意以下事項(xiàng)：

1. 版本要求： 在開(kāi)始使用公用表表達(dá)式之前，確保您使用的是 MySQL 8 或更高版本。公用表表達(dá)式是 MySQL 8 中引入的新功能，因此在舊版本的 MySQL 中無(wú)法使用。如果您的數(shù)據(jù)庫(kù)還在舊版本上運(yùn)行，請(qǐng)考慮升級(jí)到 MySQL 8。
2. 語(yǔ)法規(guī)則： 公用表表達(dá)式的語(yǔ)法類(lèi)似于子查詢(xún)，但使用 WITH 關(guān)鍵字進(jìn)行定義。了解并熟悉公用表表達(dá)式的語(yǔ)法結(jié)構(gòu)，包括 WITH 子句、CTE 名稱(chēng)、列名和實(shí)際查詢(xún)部分。確保您正確地編寫(xiě)公用表表達(dá)式，并在查詢(xún)中使用它們。
3. CTE 的作用范圍： 公用表表達(dá)式的作用范圍僅限于定義它們的查詢(xún)。這意味著在同一查詢(xún)中，您可以在多個(gè)地方引用相同的 CTE，但在其他查詢(xún)中無(wú)法使用相同的 CTE。在編寫(xiě)查詢(xún)時(shí)，請(qǐng)確保將 CTE 的使用限制在合適的范圍內(nèi)，以避免引起錯(cuò)誤。
4. 遞歸查詢(xún)的使用： 公用表表達(dá)式還可以用于執(zhí)行遞歸查詢(xún)，即查詢(xún)中的表達(dá)式引用自身的情況。遞歸查詢(xún)?cè)谔幚韺哟谓Y(jié)構(gòu)的數(shù)據(jù)時(shí)非常有用，例如組織結(jié)構(gòu)或評(píng)論回復(fù)。然而，遞歸查詢(xún)需要謹(jǐn)慎使用，因?yàn)殄e(cuò)誤的查詢(xún)可能導(dǎo)致無(wú)限循環(huán)。確保您了解遞歸查詢(xún)的工作原理，并遵循最佳實(shí)踐，例如設(shè)置遞歸終止條件和使用適當(dāng)?shù)乃饕?/li>
5. 性能優(yōu)化： 雖然公用表表達(dá)式提供了更簡(jiǎn)潔的查詢(xún)語(yǔ)法，但在處理大型數(shù)據(jù)集時(shí)，性能可能成為一個(gè)關(guān)鍵問(wèn)題。CTE 的執(zhí)行可能涉及到臨時(shí)表的創(chuàng)建和數(shù)據(jù)復(fù)制，這可能導(dǎo)致額外的開(kāi)銷(xiāo)。在編寫(xiě)復(fù)雜的查詢(xún)時(shí)，請(qǐng)?jiān)u估性能影響并進(jìn)行必要的優(yōu)化，例如適當(dāng)?shù)乃饕筒樵?xún)重寫(xiě)。使用 EXPLAIN 語(yǔ)句來(lái)分析查詢(xún)計(jì)劃，并確保查詢(xún)的執(zhí)行效率得到優(yōu)化。
6. 數(shù)據(jù)庫(kù)兼容性： 請(qǐng)注意，公用表表達(dá)式是 MySQL 8 特有的功能，并不一定在其他數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）中可用。如果您計(jì)劃將查詢(xún)遷移到其他 DBMS，您可能需要調(diào)整查詢(xún)語(yǔ)句以適應(yīng)該系統(tǒng)的特定語(yǔ)法和功能。在跨數(shù)據(jù)庫(kù)平臺(tái)的項(xiàng)目中，請(qǐng)確保了解目標(biāo) DBMS 支持的功能和語(yǔ)法。

總結(jié)：

MySQL 8 的公用表表達(dá)式（CTE）是一個(gè)強(qiáng)大的功能，可以提高查詢(xún)的可讀性和編寫(xiě)復(fù)雜查詢(xún)的靈活性。

在使用公用表表達(dá)式時(shí)，請(qǐng)注意版本要求、語(yǔ)法規(guī)則、作用范圍、遞歸查詢(xún)的使用、性能優(yōu)化以及數(shù)據(jù)庫(kù)兼容性等重要事項(xiàng)。

通過(guò)充分了解這些注意事項(xiàng)，您將能夠更好地應(yīng)用 MySQL 8 的公用表表達(dá)式功能，并獲得更好的查詢(xún)性能和結(jié)果。

到此這篇關(guān)于Mysql8公用表表達(dá)式CTE詳解的文章就介紹到這了,更多相關(guān)Mysql8公用表表達(dá)式內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論