前言

Mysql 8 正式发布了，新增了很多优秀特性，之后我会挑些重点来分享。

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

CTE 是什么

派生表大家都比较熟悉了，CTE 就是针对派生表来的，可以说是增强的派生表，或者说时派生表的替换。

派生表是FROM中的子查询，例如：

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

CTE 就像派生表，但它的声明是在查询块儿之前，而不是在FROM中，例如：

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的使用

• SELECT 1 AS n是初始设置，这一行是用来定义my_cte的列，只有一列，类型为INT，名字为n

• SELECT 1+n FROM my_cte WHERE n<10这句的意思是：从 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，这个经验会帮您节省一些时间。