MySQL8功能详解——Common table expression (CTE)

Common table expression (CTE)
通用表表达式是
MySQL8
推出的新功能。它是一种临时表，使用“
WITH
”命令，可以执行递归查询。

先看一下如何使用WITH语句：

WITH

  cte1 AS (SELECT a, b FROM table1),

  cte2 AS (SELECT c, d FROM table2)

SELECT b, d FROM cte1 JOIN cte2

WHERE cte1.a= cte2.c;

看起来是不是像是将派生表放在了前面？是的，使用
WITH 语句，可以使你的查询看起来清晰明了，更加易读，但好处不止是这个，
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;

也可以在其他
CTE 中引用
CTE 名称，从而使
CTE 能够基于其他
CTE 进行定义。例如：

WITH d1 AS(SELECT … FROM …),

     d2 AS (SELECT … FROM d1 …)

SELECT

FROM d1, d2 …

此外，
CTE 可以引用自身来定义递归
CTE 。递归
CTE 常见于生成序列，层次或树状结构的遍历。例如：

打印
1 到
10:

WITHRECURSIVE qn AS

   ( SELECT 1 AS a

     UNION ALL

     SELECT 1+a FROM qn WHERE a<10

   )

SELECT *FROM qn;

插入
1 到
10:

INSERT INTOnumbers

WITHRECURSIVE qn AS

   ( SELECT 1 AS a

     UNION ALL

     SELECT 1+a FROM qn WHERE a<10

   )

SELECT *FROM qn;

层次遍历：

CREATE TABLEemployees (

      id INT PRIMARY KEY,

      name VARCHAR(100),

      manager_id INT,

      FOREIGN KEY (manager_id)   REFERENCES employees(id) );

借用一下“蜀国”的人物充当一下员工

INSERT INTOemployees VALUES

(333, "刘备", NULL), # 

(198, "关羽", 333), #

(692, "张飞", 333),

(29, "兵甲", 198),

(4610,"兵乙", 29),

(72, "兵丁", 29),

(123, "兵己", 692);

执行一下查询：

WITHRECURSIVE 

emp_ext (id,name, path) AS (

   SELECT id, name, CAST(id AS CHAR(200))

   FROM employees

   WHERE manager_id IS NULL

 UNION ALL

   SELECT s.id, s.name,

   CONCAT(m.path, ",", s.id)

   FROM emp_ext m JOIN  employees s

   ON m.id=s.manager_id )

   SELECT * FROM emp_ext ORDER BY path;

结果如下：

使用CTE，除了上述的好处之外，还会带来性能的提升。原因在于，如果使用派生表进行多次参照，将会多次物化相同的表。更多的空间，更多的时间，更长的锁等等会引起性能问题，类似于视图引用。而CTE不论使用了几次参照，仅物化一次。

有关CTE的使用就介绍到这里， 关于CTE的更多细节，请参照官网手册：
https://dev.mysql.com/doc/refman/8.0/en/with.html

希望能为从事开发工作的您带来帮助。
感谢您关注MySQL！

扫码加入MySQL技术Q群

（群号：


650149401）

点“在看”给我一朵小黄花