mysql递归查询语法WITH RECURSIVE的使用_Mysql

with recursive 是 sql 中用于执行递归查询的语法，特别适合于处理层级结构或递归数据（如树形结构、图结构）。递归查询可以反复引用自己来查询多层次的数据，而无需写多个嵌套查询。

基本语法结构：

with recursive cte_name as (
    -- 基础查询部分 (非递归部分)
    select column1, column2, ...
    from table_name
    where condition

    union all

    -- 递归查询部分
    select column1, column2, ...
    from table_name t
    join cte_name cte on t.column = cte.column
    where condition
)
select * from cte_name;

关键部分解析：

with recursive：
- with 用于创建公共表表达式（cte），recursive 关键字标识这是一个递归查询。
- cte_name 是你为公共表表达式（cte）起的名字，后续查询可以引用它。
基础查询（非递归部分）：
- 这是递归查询的起点，用于查询层级结构中的根数据（通常是最上层或最初始的数据）。
- 通常，这部分查询会返回一个起始集合或基础条件，如树形结构中的根节点。
递归查询部分：
- 递归查询部分通常会参考（引用）上面基础查询的结果，形成一个不断迭代的过程。
- 在递归查询部分中，常常会使用 join 或者自连接来与 cte_name（即递归查询的结果集）进行连接，查找下级数据。
- 递归查询会逐步深入，直到没有更多的数据为止。
union all：
- union all 用于将基础查询（非递归部分）和递归查询部分合并成一个完整的结果集。
- union all 不会去重（不同于 union），通常用于递归查询，以保持所有结果。
最终查询：
- 查询 cte_name，得到递归查询的最终结果。
- 递归查询的结果会返回所有层次的数据，直到没有更多的层级为止。

递归查询的工作流程：

第一次迭代：
- 执行基础查询部分，返回初始的数据集（通常是最顶层的数据）。
第二次及后续迭代：
- 递归查询部分会基于前一次查询的结果继续进行，查找下一级的数据（比如查找所有根节点的子节点）。
- 每一轮迭代都会向结果集中添加新的行。
停止条件：
- 当递归查询找不到更多符合条件的行时，递归查询停止，返回最终的结果。

示例：员工与经理的层级关系

假设有一个员工表，每个员工有一个 manager_id 字段指向他们的经理，我们希望查询某个员工及其所有上级经理，直到最顶层的经理为止。

with recursive employeehierarchy as (
    -- 基础查询部分：查找某个特定员工
    select id, name, manager_id
    from employees
    where id = :employee_id  -- 查找指定员工

    union all

    -- 递归查询部分：查找员工的经理
    select e.id, e.name, e.manager_id
    from employees e
    join employeehierarchy eh on e.id = eh.manager_id
)
-- 返回所有员工及其上级经理
select * from employeehierarchy;

解释：

基础查询部分：
- 查找特定员工（通过 id = :employee_id）。
递归查询部分：
- 通过自连接 join employeehierarchy eh on e.id = eh.manager_id 查找该员工的经理（manager_id 字段指向的员工）。
union all：
- 合并基础查询部分（初始员工）和递归查询部分（逐级向上查找经理）。
查询最终结果：
- 返回递归查询的结果，即该员工及其所有上级经理。

示例：树形结构的数据（如分类）

假设有一个包含分类的表 categories，每个分类有一个 parent_id 字段指向其父分类。我们希望查询某个分类及其所有的子分类。

with recursive categoryhierarchy as (
    -- 基础查询部分：查找某个特定分类
    select id, name, parent_id
    from categories
    where id = :category_id  -- 查找指定分类

    union all

    -- 递归查询部分：查找分类的子分类
    select c.id, c.name, c.parent_id
    from categories c
    join categoryhierarchy ch on c.parent_id = ch.id
)
-- 返回所有分类及其子分类
select * from categoryhierarchy;

解释：

基础查询部分：
- 查找指定的分类（通过 id = :category_id）。
递归查询部分：
- 查找所有子分类，join 操作通过 c.parent_id = ch.id 来连接父分类和子分类。
union all：
- 合并基础查询和递归查询部分，逐层查找所有子分类。

递归查询的特性：

递归深度限制：
- 大多数数据库系统（如 postgresql、mysql 等）会对递归查询的深度进行限制，防止无限递归。mysql 默认为 1000 层深度，但可以通过配置来调整此值。
性能问题：
- 递归查询可能会消耗较多的资源，特别是当层级较多或数据量庞大时。需要小心使用，避免导致性能瓶颈。
迭代过程：
- 递归查询通过每一轮的迭代逐步向下查询，直到没有更多数据。每一轮迭代的结果都会在下次查询中被引用。