Mysql 8 重要新特性 - CTE 通用表表达式

前言

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 的语法

看几个简单的示例：

WITH qn AS (SELECT a FROM t1)
SELECT * from qn;

WITH qn AS (SELECT a+2 AS a, b FROM t1)
UPDATE t1, qn SET t1.a=qn.a + 10 WHERE t1.a - qn.a = 0; 

WITH qn(a, b) AS (SELECT a+2, b FROM t2)
DELETE t1 FROM t1, qn WHERE t1.a - qn.a = 0;

INSERT INTO t2
WITH qn AS (SELECT 10*a AS a FROM t1) 
SELECT * from qn;

SELECT * FROM t1 WHERE t1.a IN
(WITH cte as (SELECT * FROM t1 AS t2 LIMIT 1)
SELECT a + 0 FROM 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，这个经验会帮您节省一些时间。