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)
点“在看”给我一朵小黄花