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

浅浅的花香味﹌ 2023-06-27 05:52 7阅读 0赞

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;

结果如下：

![format_png][]

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

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

希望能为从事开发工作的您带来帮助。

感谢您关注MySQL！

![分割线箭头动态][format_png 1]

扫码加入MySQL技术Q群

**（群号：****650149401）**

![format_png 2][]

点“在看”给我一朵小黄花![format_png 3][]

![format_png 4][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy95T0YwZVBRU2RrOTBPdTBTZmZxV2ZGc0V2WEFtQURMalAxM1I4VkV0MUdvZnd4SzJxOWlhNFh6ck1EZ0VaRVp6UllaZFNMOWNjMmN4ZlVKbGlhU3l0aWNuUS82NDA?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi83aDRpY0o0aWNzSkJnOUVoVzhhWFlzZ0FpY2xQMWljbFFpY1hVc09Wa0paSlREUUNOSlBjV0tDUXJUcWNUeHFpY0FDZWMwbzBXVWlidlp5T1FRdkVPdlQ1MjVuQXcvNjQw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy83aDRpY0o0aWNzSkJqZHlLNEFBWDhDcWhuSkluc0szOGlheGpiaWI5NGF4SlA0Y3NXaWJUdDRwNGJuM2p6SFNLY3RHV3AxaWFpYnIxaWFGVUtiMU9rMUJJbFJ3UmpBLzY0MA?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL3N6X21tYml6X2dpZi83aDRpY0o0aWNzSkJnZUQyQ2pNaWJ0VThQRXROcW5wSGFpY3FKZHlIUFE0VWxBa1Y4aWF6b1BsRDJITEFOaWFZMWNMN2pVOUtuekhiNFVPNGszOThLazBUdDViQS82NDA?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL3N6X21tYml6X3BuZy83aDRpY0o0aWNzSkJnZUQyQ2pNaWJ0VThQRXROcW5wSGFpY3FqSGtXWHBPcGttZG5HMlFXSWxPbjJVeE9KUkNFdDNTVWdGY1FiVTlvNFdHc1p2OTVvRGhZMVEvNjQw?x-oss-process=image/format,png