什么是CTE（公用表表达式）？
在MySQL 8.0版本中，引入了一种全新的SQL查询模式——CTE（Common Table Expression，公用表表达式）。CTE通过WITH关键字在查询顶部定义一个临时的命名结果集，这个结果集可以被后续的SELECT、INSERT、UPDATE或DELETE语句引用。
与传统的派生表（Derived Table，即SELECT ... FROM (SELECT ...)形式的子查询）相比，CTE不仅提升了SQL代码的可读性，更重要的是它支持递归查询，能够轻松处理层次结构或树状数据。更多相关测试用例可以参考 www.ipipp.com 提供的数据库示例。
一、非递归CTE：替代子查询的优雅方案
非递归CTE是最基础的用法，它的主要作用是将复杂的子查询逻辑提取出来，使代码结构更加清晰，并且同一个CTE可以在主查询中被多次引用，避免重复编写相同的子查询。
基础语法：
WITH cte_name (col1, col2) AS ( SELECT column1, column2 FROM table_name WHERE condition ) SELECT * FROM cte_name;
实战场景：查询各部门平均薪资，并筛选出高于公司整体平均薪资的部门。
WITH dept_avg AS ( -- 第一步：计算各部门平均薪资 SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ), company_avg AS ( -- 第二步：计算公司整体平均薪资 SELECT AVG(salary) AS overall_avg FROM employees ) -- 主查询：关联两个CTE SELECT d.department_id, d.avg_salary FROM dept_avg d CROSS JOIN company_avg c WHERE d.avg_salary > c.overall_avg;
在上述代码中，我们定义了dept_avg和company_avg两个CTE，逻辑分层清晰，避免了多层SELECT嵌套带来的代码臃肿。
二、递归CTE：处理层级数据的利器
递归CTE是CTE最强大的功能，它由两部分组成：基础成员（非递归的初始查询）和递归成员（引用自身的查询），两者通过UNION ALL连接。MySQL通过WITH RECURSIVE关键字来声明递归CTE。
实战场景1：生成连续数字序列
在某些报表或日期填充场景中，我们需要生成一个连续的数字序列。
WITH RECURSIVE number_seq AS ( -- 基础成员：起始点 SELECT 1 AS n UNION ALL -- 递归成员：每次加1，直到达到10 SELECT n + 1 FROM number_seq WHERE n < 10 ) SELECT * FROM number_seq;
该查询会输出1到10的数字。执行时，首先执行基础成员得到n=1，然后不断执行递归成员，直到n<10<>的条件不再满足为止。
实战场景2：组织架构树状查询
假设我们有一张员工表employees，包含id, name, manager_id字段，现在需要查询某个员工及其所有层级的下属。
WITH RECURSIVE subordinates AS ( -- 基础成员：查找起始员工（如ID为1的CEO） SELECT id, name, manager_id, 1 AS level FROM employees WHERE id = 1 UNION ALL -- 递归成员：根据上一层级的结果查找下一层级 SELECT e.id, e.name, e.manager_id, s.level + 1 FROM employees e JOIN subordinates s ON e.manager_id = s.id ) SELECT * FROM subordinates ORDER BY level;
这种方式完美解决了以往需要依赖程序代码递归多次查询数据库的问题，将树状遍历逻辑完全交由数据库引擎一次完成，大幅减少了网络IO开销。
三、CTE的核心优势
1. 提升可读性与可维护性： 逻辑自上而下，符合人类阅读习惯，复杂查询不再像天书。
2. 避免重复计算： 定义的CTE在主查询中被多次引用时，MySQL优化器能够更好地处理，在某些场景下避免了对同一子查询的重复执行。
3. 强大的递归能力： 轻松应对树形结构、图状结构的数据遍历，这是传统SQL语法难以高效实现的。
四、使用注意事项
1. 递归深度限制： 为了防止死循环，MySQL默认限制递归深度为1000次。如果确认数据层级更深，可以通过SET @@cte_max_recursion_depth = 10000;来临时调整。
2. 版本要求： CTE是MySQL 8.0的特性，MySQL 5.7及以下版本无法使用。
3. 性能考量： 虽然CTE很方便，但在处理海量数据时，由于MySQL对CTE的物化策略不同，有时可能不如精心优化的JOIN或临时表高效。建议对复杂CTE使用EXPLAIN分析执行计划。
掌握CTE，特别是递归CTE，是现代MySQL开发者的必备技能。它不仅让代码更加优雅，更是解决复杂数据关系的高效工具。