前言

Mysql 8 正式發佈了，新增了很多優秀特性，之後我會挑些重點來分享。

下面和大家一起熟悉下CTE（Common Table Expressions）通用表表達式。

CTE 是什麼

派生表大家都比較熟悉了，CTE 就是針對派生表來的，可以說是增強的派生表，或者說時派生表的替換。

派生表是<code>FROM/<code>中的子查詢，例如：

SELECT ... FROM (subquery) AS derived, t1 ...

CTE 就像派生表，但它的聲明是在查詢塊兒之前，而不是在<code>FROM/<code>中，例如：

WITH derived AS (subquery)
SELECT ... FROM derived, t1 ...

CTE 的語法

看幾個簡單的示例：

CTE 的好處

CTE 相較於派生表有4個明顯的優勢：

（1）更好的可讀性

派生表的形式：

SELECT ...
FROM t1 LEFT JOIN ((SELECT ... FROM ...) AS dt JOIN t2 ON ...) ON ...

CTE的形式：

WITH dt AS (SELECT ... FROM ...)
SELECT ...
FROM t1 LEFT JOIN (dt JOIN t2 ON ...) ON ...

（2）可以被多次引用

派生表不能被引用兩次，例如：

SELECT ...
FROM (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) AS d1
JOIN (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) AS d2 ON d1.b = d2.a;

而 CTE 可以，例如：

WITH d AS (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b)
SELECT ... FROM d AS d1 JOIN d AS d2 ON d1.b = d2.a;

（3）可以引用其他的 CTE

派生表不能引用其他派生表，例如：

SELECT ...
FROM (SELECT ... FROM ...) AS d1, (SELECT ... FROM d1 ...) AS d2 ...
ERROR: 1146 (42S02): Table ‘db.d1’ doesn’t exist

CTE 可以引用其他的 CTE，例如：

WITH d1 AS (SELECT ... FROM ...),
 d2 AS (SELECT ... FROM d1 ...)
SELECT
FROM d1, d2 ...

（4）性能的提升

派生表是具體化的，每個派生表都是一個具體化的存在，就會產生性能問題，例如更多的空間、耗費更多的時間……

CTE 只會被創建一次，不管被引用了多少次

示例

（1）生成 1-10 的數字

先從一個簡單的例子開始，生成 1-10 的數字

WITH RECURSIVE my_cte AS
(
 SELECT 1 AS n
 UNION ALL
 SELECT 1+n FROM my_cte WHERE n<10
)
SELECT * FROM my_cte;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+

簡單分析下這個例子：

• 這個CTE名字是my_cte，需要注意的是名字前面多了一個關鍵字RECURSIVE，說明這個CTE是遞歸形式的

• 括號中間是CTE的定義

• SELECT那句是對my_cte的使用

• <code>SELECT 1 AS n/<code>是初始設置，這一行是用來定義my_cte的列，只有一列，類型為INT，名字為n

• <code>SELECT 1+n FROM my_cte WHERE n<10/<code>這句的意思是：從 my_cte 中拿 <10 的行，然後產生一行新記錄，對n進行增加

所以 mysql 會做以下步驟：

• 迭代0：創建初始行，S0:S0={1}

• 迭代1：基於 S0 進行處理，產生新數據 S1={1+1}={2}

• 迭代2：基於 S1，產生 S2={1+2}={3}

• ...

• 迭代9：基於 S8，產生 S9={1+9}={10}

• 迭代10：基於 S9，發現沒有匹配n<10的，所以沒有產出，並使循環終止

• my_cte 的最終結果就是對S0,S1,...,S9進行union

（2）使用 CTE 創建一個表

USE test;
CREATE TABLE numbers
 WITH RECURSIVE my_cte(n) AS
 (
 SELECT 1
 UNION ALL
 SELECT 1+n FROM my_cte WHERE n<6
 )
 SELECT * FROM my_cte;
Query OK, 6 rows affected (0,40 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+

（3）用於 INSERT

INSERT INTO numbers
WITH RECURSIVE my_cte(n) AS
( 

 SELECT 1
 UNION ALL
 SELECT 1+n FROM my_cte WHERE n<6
)
SELECT * FROM my_cte;
Query OK, 6 rows affected (0,12 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+

（4）在 UPDATE 中使用：

WITH RECURSIVE my_cte(n) AS
(
 SELECT 1
 UNION ALL
 SELECT 1+n FROM my_cte WHERE n<6
)
UPDATE numbers, my_cte
SET numbers.n=0
WHERE numbers.n=my_cte.n*my_cte.n;
Query OK, 4 rows affected (0,01 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 0 |
| 2 |
| 3 |
| 0 |
| 5 |
| 6 |
| 0 |
| 2 | 

| 3 |
| 0 |
| 5 |
| 6 |
+------+

（5）在 DELETE 中使用：

DELETE FROM numbers
WHERE numbers.n >
 (
 WITH RECURSIVE my_cte(n) AS
 (
 SELECT 1
 UNION ALL
 SELECT 1+n FROM my_cte WHERE n<6
 )
 # Half the average is 3.5/2=1.75
 SELECT AVG(n)/2 FROM my_cte
 );
Query OK, 4 rows affected (0,07 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 0 |
| 0 |
| 0 |
| 0 |
+------+

Mysql 8 實踐環境搭建

我是使用 docker 安裝的Mysql 8.0.11，我感覺這是最簡單的方式。

啟動正常，但連接mysql時報錯：

MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

意思是caching_sha2_password這個認證插件不能被加載。

網上查了一下，原因是mysql8改變了認證模式，解決方式是在啟動容器時指定參數：

docker run -p 3306:3306 --name mysql8 -e MYSQL_ROOT_PASSWORD=111111 -d mysql:8.0.11 --default-authentication-plugin=mysql_native_password

重點是添加了：

--default-authentication-plugin=mysql_native_password

之後就可以正常登陸了，使用docker mysql作為客戶端登錄的命令：

docker run -it --link mysql8:mysql --rm mysql sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'

這是我遇到的一個問題，如果你也是使用docker來實踐mysql8，這個經驗會幫您節省一些時間。