网站导航
> 技术文档 > MYSQL的（WITH RECURSIVE）递归查询_mysql recursive

MYSQL的（WITH RECURSIVE）递归查询_mysql recursive

网友: 技术文档 2025-08-08 13:59:16

文章目录

  • 一、前言
  • 二、语法
  • 三、示例
    • 3.1、创建测试表和数据
    • 3.2、查询所有子节点（以 id=1 为例）
    • 3.3、查询所有父节点（以 id=5 为例）
    • 3.4、查询所有子节点（向下递归）并显示全链路路径及层级
    • 3.5、查询所有父节点（向上递归）并显示全链路路径及层级
    • 3.6、高级功能：双向递归查询（同时获取祖先和后代）
  • 四、扩展
    • 4.1、注意事项：
    • 4.2、避免死循环
    • 4.3、结果排序技巧

一、前言

从MySQL 8.0开始，可以使用WITH RECURSIVE来创建递归公用表表达式（Common Table Expressions , CTE）。

递归查询在各种应用场景中都很常见，例如：

  • 组织架构查询： 获取公司内部的组织结构信息，从CEO到普通员工的层级关系。
  • 产品分类查询： 从顶级分类到子分类的层级查找。
  • 目录结构查询： 在文件系统中，从根目录到子目录的路径查找。

二、语法

WITH RECURSIVE cte_name (column_list) AS (SELECTinitial_query -- 初始查询UNION [ALL]recursive_query -- 递归查询)SELECT * FROM cte_name;

其中：

  • CTE 名称（cte_name）用于标识递归查询的临时结果集。
  • 列名列表（column_list）定义了 CTE 结果集中包含的列及其名称。
  • 初始查询（initial_query）提供递归过程的起点，即第一次迭代时使用的数据。
  • 递归部分（递归子查询）定义了如何将前一次迭代的结果作为输入，计算出下一次迭代的数据。
  • recursive_query:表示递归查询语句，应当与column_list中的列名对应。
  • SELECT * FROM cte_name：表示最终返回的查询结果集，可以通过cte_name查询表中的列名进行指定。

1.递归查询的结构
递归查询通常由两部分构成：初始化查询（非递归部分）和递归子查询（递归部分）。

  • 初始化查询： 定义递归开始时的基础数据集，通常是与递归逻辑相关的最顶层数据或边界条件。
  • 递归子查询： 定义如何根据前一次迭代的结果生成下一次迭代的数据。递归子查询通常包含对自身 CTE 名称的引用，以递归地应用相同的操作。

2.连接操作符：

  • 递归查询的初始化查询和递归查询通常通过 UNION 或 UNION ALL 连接起来，形成一个完整的递归查询表达式。
  • UNION 会去除结果集中的重复行，而 UNION ALL 不会去除重复，根据实际需求选择合适的连接操作符。

3.终止条件

  • 递归查询必须有一个明确的终止条件，否则会无限循环下去。终止条件通常隐含在递归子查询的 WHERE 子句或其他逻辑中，当满足特定条件时，不再产生新的结果。

三、示例

通过以下目录树示例查询父节点、子节点、及全链路等信息。
MYSQL的（WITH RECURSIVE）递归查询_mysql recursive

3.1、创建测试表和数据

CREATE TABLE tree_table ( id INT PRIMARY KEY, name VARCHAR(50), parent_id INT);INSERT INTO tree_table (id, name, parent_id) VALUES(1, \'A\', NULL),(2, \'B\', 1),(3, \'C\', 1),(4, \'D\', 2),(5, \'E\', 3);(6, \'F\', 3);(7, \'G\', 5);

3.2、查询所有子节点（以 id=1 为例）

SQL语句：

WITH RECURSIVE cte AS ( SELECT id, parent_id, name FROM tree_table WHERE id = 1 UNION ALL SELECT t.id, t.parent_id, t.name FROM tree_table t INNER JOIN cte ON t.parent_id = cte.id)SELECT * FROM cte;

结果：
MYSQL的（WITH RECURSIVE）递归查询_mysql recursive

3.3、查询所有父节点（以 id=5 为例）

SQL:

WITH RECURSIVE cte AS ( SELECT id, parent_id, name FROM tree_table WHERE id = 5 UNION ALL SELECT t.id, t.parent_id, t.name FROM tree_table t INNER JOIN cte ON t.id = cte.parent_id)SELECT * FROM cte;

注意：
子节点与父节点的区别在于join的关联条件on的不同。
MYSQL的（WITH RECURSIVE）递归查询_mysql recursive

结果：
MYSQL的（WITH RECURSIVE）递归查询_mysql recursive

3.4、查询所有子节点（向下递归）并显示全链路路径及层级

查询节点下的所有子节点，并且可以看到全链路及层级，如A->B->C

SQL:

WITH RECURSIVE cte AS ( SELECT id,parent_id, name, name AS path, -- 初始路径 1 AS level -- 起始层级 FROM tree_table WHERE id = 1 -- 查询ID=1的所有子节点 UNION ALL SELECT t.id,t.parent_id,t.name, CONCAT(cte.path, \'->\', t.name), -- 路径拼接 cte.level + 1  -- 层级递增 FROM tree_table t INNER JOIN cte ON t.parent_id = cte.id)SELECT * FROM cte;

结果如下：
MYSQL的（WITH RECURSIVE）递归查询_mysql recursive

3.5、查询所有父节点（向上递归）并显示全链路路径及层级

查询节点的所有父节点，并且可以看到全链路及层级，如A->B->C

SQL:

WITH RECURSIVE cte AS ( SELECT id, parent_id, name, CAST(name AS CHAR(255)) AS path, -- 路径初始化 1 AS level -- 起始层级 FROM tree_table WHERE id = 6 -- 查询ID=6的所有父节点 UNION ALL SELECT t.id, t.parent_id, t.name, CONCAT(t.name, \'->\', cte.path), -- 向前拼接路径 cte.level + 1  -- 层级递增 FROM tree_table t INNER JOIN cte ON t.id = cte.parent_id)SELECT * FROM cteORDER BY level ASC; -- 按层级升序排列

结果如下：
MYSQL的（WITH RECURSIVE）递归查询_mysql recursive

3.6、高级功能：双向递归查询（同时获取祖先和后代）

SQL:

-- 获取ID=5的所有祖先和后代WITH RECURSIVEdown AS ( /* 向下递归获取后代 */ SELECT id, parent_id, name, name AS path, 1 AS level FROM tree_table WHERE id = 5 UNION ALL SELECT t.id, t.parent_id, t.name,  CONCAT(down.path, \'->\', t.name),  down.level + 1 FROM tree_table t INNER JOIN down ON t.parent_id = down.id),up AS ( /* 向上递归获取祖先 */ SELECT id, parent_id, name, name AS path, 1 AS level FROM tree_table WHERE id = 5 UNION ALL SELECT t.id, t.parent_id, t.name,  CONCAT(t.name, \'->\', up.path),  up.level + 1 FROM tree_table t INNER JOIN up ON t.id = up.parent_id)SELECT * FROM up WHERE id != 5 -- 排除重复的当前节点UNION ALLSELECT * FROM down;

结果如下：
MYSQL的（WITH RECURSIVE）递归查询_mysql recursive
上图示例中同时查询了父节点与子节点，可能从返回结果中不能区别出哪些是父节点哪些是子节点。这里改造一下：

-- 获取ID=5的所有祖先和后代WITH RECURSIVEdown AS ( /* 向下递归获取后代 */ SELECT id, parent_id, name, name AS path, 1 AS level,\'down\' as type FROM tree_table WHERE id = 5 UNION ALL SELECT t.id, t.parent_id, t.name,  CONCAT(down.path, \'->\', t.name),  down.level + 1,  \'down\' as type FROM tree_table t INNER JOIN down ON t.parent_id = down.id),up AS ( /* 向上递归获取祖先 */ SELECT id, parent_id, name, name AS path, 1 AS level,\'up\' as type FROM tree_table WHERE id = 5 UNION ALL SELECT t.id, t.parent_id, t.name,  CONCAT(t.name, \'->\', up.path),  up.level + 1,  \'up\' as type FROM tree_table t INNER JOIN up ON t.id = up.parent_id)SELECT * FROM up WHERE id != 5 -- 排除重复的当前节点UNION ALLSELECT * FROM down;

添加type字段区分父节点、子节点。
MYSQL的（WITH RECURSIVE）递归查询_mysql recursive

四、扩展

4.1、注意事项：

  1. 递归深度限制： MySQL 默认递归深度为 1000，超限会报错。可通过设置会话变量调整：
SET SESSION cte_max_recursion_depth = 10000; -- 修改递归深度

  1. 避免死循环： 确保数据中没有循环引用（如 A→B→A）。

  2. 性能： 大数据量时确保 parent_id 字段有索引：

CREATE INDEX idx_parent_id ON tree_table(parent_id);
  1. 路径长度限制：
CAST(name AS CHAR(255)) -- 根据实际需要调整长度

4.2、避免死循环

避免死循环： 确保数据中没有循环引用（如 A→B→A）

如果遇到循环引用会导致递归查询无限循环查询，从而引发数据库异常报错。

解决方法：

  • 方法一：避免死循环，确保数据中没有循环引用（如 A→B→A）
  • 方法二：限制递归层级，查询时设置递归层级，如小于10级

4.3、结果排序技巧

  • 按层级排序：ORDER BY level ASC

  • 按路径排序：ORDER BY full_path ASC

  • 按树形结构排序：

    ORDER BY LENGTH(full_path) - LENGTH(REPLACE(full_path, \'->\', \'\')), full_path

创作不易，欢迎打赏，你的鼓励将是我创作的最大动力。

在这里插入图片描述

网络标签：

相关问题