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;