一、概述

1. 公用表表達式（或通用表表達式）簡稱為CTE（Common Table Expressions）。
2. CTE是一個命名的臨時結果集，作用范圍是當前語句。
3. CTE可以理解成一個可以復用的子查詢，當然跟子查詢還是有點區(qū)別的，CTE可以引用其他CTE，但子查詢不能引用其他子查詢。所以，可以考慮代替子查詢。
4. 依據語法結構和執(zhí)行方式的不同，公用表表達式分為普通公用表表達式和遞歸公用表表達式 2 種。

二、普通公用表表達式

1、語法結構

普通公用表表達式的語法結構是：

WITH CTE名稱 
AS （子查詢）
SELECT|DELETE|UPDATE 語句;

普通公用表表達式類似于子查詢，不過，跟子查詢不同的是，它可以被多次引用，而且可以被其他的普通公用表表達式所引用。

2、案例

舉例：查詢員工所在的部門的詳細信息。

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)

這個查詢也可以用普通公用表表達式的方式完成：

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. 例子說明，公用表表達式可以起到子查詢的作用。
2. 以后如果遇到需要使用子查詢的場景，你可以在查詢之前，先定義公用表表達式，然后在查詢中用它來代替子查詢。
3. 而且，跟子查詢相比，公用表表達式有一個優(yōu)點，就是定義過公用表表達式之后的查詢，可以像一個表一樣多次引用公用表表達式，而子查詢則不能。

三、遞歸公用表表達式

1、語法結構

遞歸公用表表達式也是一種公用表表達式，只不過，除了普通公用表表達式的特點以外，它還有自己的特點，就是可以調用自己。

它的語法結構是：

WITH RECURSIVE
CTE名稱 AS （子查詢）
SELECT|DELETE|UPDATE 語句;

1. 遞歸公用表表達式由 2 部分組成，分別是種子查詢和遞歸查詢，中間通過關鍵字 UNION [ALL]進行連接。
2. 這里的種子查詢，意思就是獲得遞歸的初始值。這個查詢只會運行一次，以創(chuàng)建初始數據集，之后遞歸查詢會一直執(zhí)行，直到沒有任何新的查詢數據產生，遞歸返回。

2、案例

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

如果用我們之前學過的知識來解決，會比較復雜，至少要進行 4 次查詢才能搞定：

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

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

如果用遞歸公用表表達式，就非常簡單了。我介紹下具體的思路。

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

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

代碼實現：

WITH RECURSIVE cte 
AS 
(
SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100 -- 種子查詢，找到第一代領導
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) -- 遞歸查詢，找出以遞歸公用表表達式的人為領導的人
)
SELECT employee_id,last_name FROM cte WHERE n >= 3; 

總之，遞歸公用表表達式對于查詢一個有共同的根節(jié)點的樹形結構數據，非常有用。它可以不受層級的限制，輕松查出所有節(jié)點的數據。如果用其他的查詢方式，就比較復雜了。

四、注意事項

公用表表達式的作用是可以替代子查詢，而且可以被多次引用。遞歸公用表表達式對查詢有一個共同根節(jié)點的樹形結構數據非常高效，可以輕松搞定其他查詢方式難以處理的查詢。但在使用時需要時刻注意以下事項：

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

總結：

MySQL 8 的公用表表達式（CTE）是一個強大的功能，可以提高查詢的可讀性和編寫復雜查詢的靈活性。

在使用公用表表達式時，請注意版本要求、語法規(guī)則、作用范圍、遞歸查詢的使用、性能優(yōu)化以及數據庫兼容性等重要事項。

通過充分了解這些注意事項，您將能夠更好地應用 MySQL 8 的公用表表達式功能，并獲得更好的查詢性能和結果。

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

最新評論