Mysql 8.0+ 的递归查询WITH RECURSIVE

MySQL with Recursive是一种基于递归思想的MySQL查询方式，可以实现对数据的递归查询和处理，返回符合条件的数据，在MySQL 8.0版本中，该功能被正式引入。

这种递归查询方式用于单表自关联，可以应用在很多场景下，比如对于树形结构、层级结构的数据处理，以及对数据进行分类汇总等。比如在开发中常见的有部门层级查询，省市区结构查询。

一、基本递归语法
WITH recursive r as (
    -- 递归基：由此开始递归
    select id,parent_id,name from category where id = 1
    union ALL
    -- 递归步：关联查询
    select c.id,c.parent_id,c.name
    from category c inner join r 
    -- r作为父表，c作为子表，所以查询条件是c的parent_id=r.id
    where r.id = c.parent_id
)
select id,parent_id,name from r

 

二、添加层级 path、与树形自关联结构（用部门id联结）

WITH RECURSIVE department_tree (department_id, department_name, parent_department_id, depth, path) AS (
        SELECT 
            department_id, 
            department_name, 
            parent_department_id, 
            1 AS depth, 
            CAST(department_id AS CHAR(200)) AS path
        FROM company_department
        WHERE parent_department_id IS NULL
        UNION ALL
        SELECT 
            cd.department_id, 
            cd.department_name, 
            cd.parent_department_id, 
            dt.depth + 1 AS depth, 
            CONCAT(dt.path, ',', cd.department_id) AS path
        FROM company_department cd
            JOIN department_tree dt ON cd.parent_department_id = dt.department_id
    )
SELECT 
    department_id, department_name, parent_department_id, depth, path
FROM department_tree
ORDER BY path;