MySQL 8.0的通用表表达式（CTE）如何使用？-mysql教程-PHP中文网

MySQL 8.0的通用表表达式（CTE）如何使用？

狼影

发布： 2025-09-11 14:14:01

原创

411人浏览过

mysql 8.0的通用表表达式（cte）如何使用？

MySQL 8.0的通用表表达式（CTE）允许你在一个查询中定义一个命名的临时结果集，然后在该查询中多次引用它。这能简化复杂查询，提高可读性。

使用WITH子句定义CTE，后面跟着CTE的名称和定义。定义可以是任何有效的SELECT语句。然后在主查询中像一个普通表一样引用CTE。

解决方案：

通用表表达式（CTE）本质上是在单个查询中创建临时命名的结果集，这允许你以更模块化和可读的方式构建复杂的查询。MySQL 8.0引入了对CTE的支持，极大地增强了SQL的表达能力。下面是一些关于如何在MySQL 8.0中使用CTE的示例和最佳实践：

CTE的基本语法：

WITH
  cte_name AS (
    SELECT column1, column2
    FROM table1
    WHERE condition
  )
SELECT column1, column2
FROM cte_name
WHERE condition;

登录后复制

这个语法结构清晰地展示了CTE的定义和使用。

WITH

登录后复制

关键字开始，

cte_name

登录后复制

是你给CTE起的名字，

AS

登录后复制

后面的括号里是定义CTE的SELECT语句。之后，你可以在主查询中像引用普通表一样引用

cte_name

登录后复制

。

如何使用递归CTE？

递归CTE允许你查询层次结构数据，例如组织结构或类别树。递归CTE由两部分组成：一个

anchor

登录后复制

成员和一个

recursive

登录后复制

成员。

anchor

登录后复制

成员返回基本结果集，

recursive

登录后复制

成员引用CTE本身并迭代地构建结果集，直到满足终止条件。

一个示例：

假设我们有一个

employees

登录后复制

表，其中包含

id

登录后复制

name

登录后复制

, 和

manager_id

登录后复制

列。要查找所有员工及其经理的姓名，可以使用递归CTE：

WITH RECURSIVE EmployeeHierarchy AS (
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE manager_id IS NULL  -- Anchor member：找到最高层级的员工

  UNION ALL

  SELECT e.id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  JOIN EmployeeHierarchy eh ON e.manager_id = eh.id  -- Recursive member：递归查找下属员工
)
SELECT id, name, manager_id, level
FROM EmployeeHierarchy
ORDER BY level, name;

登录后复制

在这个例子中，

anchor

登录后复制

成员选择所有

manager_id

登录后复制

为

NULL

登录后复制

的员工（即最高级别的员工）。

recursive

登录后复制

成员通过

manager_id

登录后复制

连接

employees

登录后复制

表和

EmployeeHierarchy

登录后复制

CTE，并增加层级

level

登录后复制

。

UNION ALL

登录后复制

合并两个结果集。递归会一直进行，直到没有更多的员工可以添加到层级结构中。

使用递归CTE时需要注意循环依赖。如果数据中存在循环依赖，递归CTE可能会无限循环。MySQL 8.0默认限制递归深度为1000。可以通过设置

max_execution_time

登录后复制

变量来防止无限循环：

SET SESSION max_execution_time = 10000; -- 设置最大执行时间为10秒

登录后复制

CTE和临时表有什么区别？

CTE和临时表都允许你存储中间结果集，但它们在使用方式和生命周期上有所不同。

生命周期： CTE的生命周期仅限于定义它的查询。查询执行完毕后，CTE会自动消失。临时表则在创建后一直存在，直到显式删除或会话结束。
可见性： CTE仅在定义它的查询中可见。临时表可以在创建它的会话中的任何查询中访问。

酷表ChatExcel
北大团队开发的通过聊天来操作Excel表格的AI工具

48

查看详情
语法： CTE使用
```
WITH
```
登录后复制
子句定义，而临时表使用
```
CREATE TEMPORARY TABLE
```
登录后复制
语句创建。
性能： 在某些情况下，CTE可能比临时表更有效率，因为优化器可以更好地优化包含CTE的查询。然而，在其他情况下，临时表可能更适合，特别是当需要在多个查询中重用中间结果集时。

选择CTE还是临时表取决于具体的需求。如果只需要在单个查询中使用中间结果集，CTE通常是更好的选择。如果需要在多个查询中重用中间结果集，或者需要长时间存储中间结果集，临时表可能更适合。

如何在复杂的查询中使用多个CTE？

在MySQL 8.0中，你可以使用逗号分隔多个CTE定义，在一个查询中使用多个CTE。这使得可以分解复杂的查询，使其更易于理解和维护。

例如，假设你有一个

orders

登录后复制

表和一个

customers

登录后复制

表。你想找到所有订单总额超过1000元的客户，并按订单总额排序。可以使用多个CTE来实现：

WITH
  OrderTotals AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
  ),
  HighValueCustomers AS (
    SELECT customer_id
    FROM OrderTotals
    WHERE total_amount > 1000
  )
SELECT c.id, c.name, ot.total_amount
FROM customers c
JOIN HighValueCustomers hvc ON c.id = hvc.customer_id
JOIN OrderTotals ot ON c.id = ot.customer_id
ORDER BY ot.total_amount DESC;

登录后复制

在这个例子中，

OrderTotals

登录后复制

CTE计算每个客户的订单总额。

HighValueCustomers

登录后复制

CTE选择订单总额超过1000元的客户。然后，主查询连接

customers

登录后复制

表和两个CTE，以获取高价值客户的姓名和订单总额，并按订单总额排序。

使用多个CTE可以使复杂的查询更易于理解和维护。每个CTE可以专注于查询的一个特定方面，从而提高代码的可读性。

CTE的性能注意事项

虽然CTE可以简化查询，但需要注意其性能影响。MySQL优化器会尝试优化包含CTE的查询，但在某些情况下，CTE可能会导致性能问题。

物化： 默认情况下，MySQL可能会物化CTE，这意味着它会将CTE的结果集存储在临时表中。这可能会导致额外的I/O开销，特别是对于大型结果集。可以使用
```
MATERIALIZED
```
登录后复制
和
```
NO MATERIALIZED
```
登录后复制
提示来控制CTE的物化行为。
索引： CTE本身不能创建索引。如果CTE返回的结果集很大，并且需要在主查询中进行连接或过滤，可以考虑在主查询中创建索引。
复杂性： 过度复杂的CTE可能会导致优化器难以优化查询。如果查询性能不佳，可以尝试将CTE分解为更小的部分，或者使用临时表代替CTE。

总的来说，CTE是一个强大的工具，可以简化复杂的查询，提高代码的可读性。然而，需要注意其性能影响，并根据具体情况选择合适的实现方式。

以上就是MySQL 8.0的通用表表达式（CTE）如何使用？的详细内容，更多请关注php中文网其它相关文章！