MySQL 8.0 Common Table Expressions (CTEs) 的生命周期与可见性 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 8.0 CTEs：昙花一现的惊艳与默默无闻的限制 (一场关于生命周期与可见性的深度解剖)

各位观众，各位老铁，早上好/下午好/晚上好！欢迎来到今天的“MySQL八卦大讲堂”，我是你们的老朋友，人称“数据库小灵通”的灵通兄。今天我们要聊的是MySQL 8.0中一个非常有趣，但又常常被忽略的家伙：Common Table Expressions，也就是大家常说的CTE，中文名儿，公共表表达式。

大家可能对CTE并不陌生，甚至每天都在用。但今天灵通兄要带大家深入挖掘一下，看看这个“公共”的家伙，到底有多“公共”，它的生命周期有多长，以及它在MySQL的世界里，到底能看到多远的地方。

一、 CTE：惊鸿一瞥的优雅转身 (CTE的定义与基本用法)

首先，让我们先回顾一下CTE是什么。简单来说，CTE就是一个命名的临时结果集，它只在一个查询语句的执行范围内存在。你可以把它想象成一个临时的视图，但它不需要像视图那样永久存储在数据库中，用完就消失，像昙花一现，惊艳却短暂。

它的语法结构很简单：

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

就像你在舞台上临时搭建了一个小舞台，这个舞台叫做cte_name，上面展示了一些精彩的表演（SELECT column1, column2 FROM table1 WHERE condition），然后你基于这个小舞台上的表演，又进行了后续的表演（SELECT column1, column2 FROM cte_name WHERE another_condition）。表演结束，舞台就拆了，一切回归原点。

举个栗子🌰：

假设我们有一个employees表，包含员工姓名(name)、部门(department)和薪水(salary)。我们想找到每个部门薪水最高的员工。用CTE，代码可以写成这样：

WITH RankedEmployees AS (
    SELECT
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
    FROM
        employees
)
SELECT
    name,
    department,
    salary
FROM
    RankedEmployees
WHERE
    rank = 1;

在这个例子中，RankedEmployees就是一个CTE，它为每个部门的员工按照薪水降序排列，并赋予一个排名。然后，我们从这个CTE中选取排名第一的员工，也就是每个部门薪水最高的员工。

是不是很优雅？代码简洁易懂，可读性极高。

二、 CTE的生命周期：昙花一现，用完即焚 (CTE的存活时间)

现在，我们来聊聊CTE的生命周期。这是今天最重要的主题之一。

CTE的生命周期非常短，短到什么程度呢？它只存在于定义它的那个查询语句的执行过程中。一旦这个查询语句执行完毕，CTE就烟消云散，不留一丝痕迹。

你可以把它想象成一个临时的变量，在函数内部声明，函数执行完毕，变量也就消失了。

重点来了！敲黑板！

CTE不能跨查询使用！ 这是一个非常重要的限制。你不能在一个查询中定义一个CTE，然后在另一个查询中使用它。
CTE不能被存储！ CTE不是视图，不能被持久化到数据库中。

举个反例：

以下代码是错误的，无法执行：

WITH MyCTE AS (
    SELECT * FROM my_table WHERE id > 10
);

SELECT * FROM MyCTE WHERE name LIKE '%test%';

这段代码试图在定义MyCTE的查询语句之外使用它，这显然是不允许的。MySQL会告诉你：“你谁啊？MyCTE是什么玩意儿？我没见过！”

那么，如果我真的需要在多个查询中使用相同的逻辑怎么办呢？

别担心，灵通兄早就为你准备好了解决方案：

使用视图 (View): 视图是一个存储在数据库中的查询语句，可以像表一样被多次引用。
使用临时表 (Temporary Table): 临时表也是存储在数据库中的表，但它只在当前会话中有效，会话结束就会自动删除。
复制粘贴 (Copy & Paste): 这是最简单粗暴的方法，但也是最不可取的。复制粘贴会导致代码冗余，难以维护。灵通兄不推荐这种做法。

用表格总结一下：

特性	CTE	视图 (View)	临时表 (Temporary Table)
生命周期	单个查询语句	永久 (除非手动删除)	当前会话
存储	无	存储在数据库中	存储在数据库中
可重用性	单个查询语句	多个查询语句	多个查询语句
性能	通常比视图略好 (优化器)	可能影响性能 (优化器)	可能影响性能 (优化器)
适用场景	复杂查询的分解、递归查询	需要重复使用的查询逻辑	需要临时存储数据的场景

三、 CTE的可见性：近在咫尺，远在天边 (CTE的作用域)

接下来，我们来聊聊CTE的可见性。

CTE的可见性也受到严格的限制。它只能在定义它的WITH子句所在的查询语句中可见。

这意味着：

CTE不能在WITH子句之外使用！ (同生命周期)
CTE可以嵌套使用！ 你可以在一个CTE中定义另一个CTE，形成一个嵌套结构。
CTE可以互相引用！ 如果两个CTE在同一个WITH子句中定义，它们可以互相引用。

举个栗子🌰：

WITH
    EmployeesInDepartment AS (
        SELECT name, department FROM employees WHERE department = 'IT'
    ),
    SalariesInDepartment AS (
        SELECT name, salary FROM salaries WHERE department = 'IT'
    ),
    CombinedData AS (
        SELECT
            e.name,
            e.department,
            s.salary
        FROM
            EmployeesInDepartment e
        JOIN
            SalariesInDepartment s ON e.name = s.name
    )
SELECT * FROM CombinedData;

在这个例子中，EmployeesInDepartment和SalariesInDepartment两个CTE都可以在CombinedData这个CTE中被引用，因为它们都在同一个WITH子句中定义。

再举个嵌套的例子：

WITH
  DepartmentSalaries AS (
    SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department
  ),
  AboveAverageDepartments AS (
    WITH OverallAverage AS (
      SELECT AVG(avg_salary) AS overall_avg FROM DepartmentSalaries
    )
    SELECT department FROM DepartmentSalaries WHERE avg_salary > (SELECT overall_avg FROM OverallAverage)
  )
SELECT * FROM AboveAverageDepartments;

注意这里AboveAverageDepartments中嵌套了一个CTE OverallAverage。内部的CTE只能在外部CTE的作用域内使用。

四、递归CTE：无限可能的奇妙世界 (Recursive CTEs)

CTE还有一个非常强大的功能：递归 (Recursive)。递归CTE可以用来处理具有层次结构的数据，例如组织结构、产品分类、族谱等等。

递归CTE的语法稍微复杂一些：

WITH RECURSIVE cte_name AS (
    -- 初始查询 (Anchor Member)
    SELECT initial_column1, initial_column2
    FROM initial_table
    WHERE initial_condition

    UNION ALL

    -- 递归查询 (Recursive Member)
    SELECT recursive_column1, recursive_column2
    FROM recursive_table
    WHERE recursive_condition AND cte_name.column1 = recursive_table.column1
)
SELECT column1, column2
FROM cte_name
WHERE final_condition;

递归CTE由两部分组成：

初始查询 (Anchor Member): 这是递归的起点，它返回第一批数据。
递归查询 (Recursive Member): 这是递归的核心，它从前一次递归的结果中获取数据，并生成下一批数据。

举个栗子🌰：

假设我们有一个employees表，包含员工姓名(name)和上级领导姓名(manager)。我们想找到所有员工的层级关系。用递归CTE，代码可以写成这样：

WITH RECURSIVE EmployeeHierarchy AS (
    -- 初始查询：找到所有没有上级领导的员工 (root节点)
    SELECT name, manager, 0 AS level
    FROM employees
    WHERE manager IS NULL

    UNION ALL

    -- 递归查询：找到所有直接或间接下属
    SELECT e.name, e.manager, eh.level + 1
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.manager = eh.name
)
SELECT name, level FROM EmployeeHierarchy ORDER BY level;

在这个例子中，EmployeeHierarchy是一个递归CTE。初始查询找到所有没有上级领导的员工，递归查询找到所有直接或间接下属。通过不断递归，我们可以找到所有员工的层级关系。

需要注意的是，递归CTE必须有一个终止条件，否则会导致无限循环。 MySQL 8.0默认限制递归深度为1000，可以通过设置max_recursive_iterations参数来修改。

五、 CTE的性能考量：有利有弊，谨慎使用 (Performance Considerations)

CTE在某些情况下可以提高查询性能，但在某些情况下也可能降低查询性能。

优点：

提高代码可读性： CTE可以将复杂的查询分解成多个逻辑块，使代码更易于理解和维护。
避免代码重复： 如果相同的逻辑需要在查询中多次使用，可以使用CTE来避免代码重复。
可能提高性能： 在某些情况下，MySQL优化器可以更好地优化CTE，从而提高查询性能。

缺点：

可能降低性能： 在某些情况下，MySQL优化器可能无法很好地优化CTE，从而降低查询性能。
过度使用CTE会导致代码复杂化： 如果CTE嵌套层数过多，会导致代码难以理解和维护。

建议：

谨慎使用CTE： 在使用CTE之前，应该仔细考虑是否真的需要。
测试CTE的性能： 在生产环境中使用CTE之前，应该先在测试环境中测试其性能。
避免过度使用CTE： 尽量保持CTE的简洁和清晰。

六、总结：昙花虽短，余香犹存 (Conclusion)

今天，我们深入探讨了MySQL 8.0中CTE的生命周期与可见性。

我们了解到，CTE是一个强大的工具，可以帮助我们编写更简洁、更易于理解的SQL查询。但同时，CTE也有其局限性，例如生命周期短、可见性有限等等。

在使用CTE时，我们需要充分了解其特性和限制，才能更好地发挥其作用。

希望今天的讲解对大家有所帮助。记住，数据库的世界充满了乐趣，让我们一起努力，不断探索，不断学习，成为真正的数据库大师！

感谢大家的观看，我们下期再见！👋

MySQL 8.0 CTEs：昙花一现的惊艳与默默无闻的限制 (一场关于生命周期与可见性的深度解剖)

发表回复 取消回复

发表回复取消回复