Py学习  »  DATABASE

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

老叶茶馆 • 4 年前 • 354 次点击  

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 cte2WHERE 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(SELECTFROM …),     d2 AS (SELECTFROM d1 …)SELECTFROM d1, d2 …

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

打印110:

WITHRECURSIVE qn AS   ( SELECT 1 AS a     UNION ALL     SELECT 1+a FROM qn WHERE a<10   )SELECT *FROM qn;

插入110:

INSERT INTOnumbersWITHRECURSIVE 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 idnameCAST(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)
点“在看”给我一朵小黄花
Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/52397
 
354 次点击