0. 背景
最近接触到的业务中需要通过mysql查询部门的组织架构层级关系，最一开始的思路是想通过自定义函数来完成，但是查询效率真的是“感人”。又另辟蹊径找到mysql的递归查询，最终很好的实现了业务诉求。回过头来记录一下。
1. 公用表表达式-CTE
公用表表达式是一个命名的临时结果集，不作为对象存储，只在执行期间存在。
CTE基本语法：
with cite_name as
(
query
)
select *
from cite_name
;
2. CTE的递归查询
CTE的递归查询就是需要不断的去“引用”CTE本身。
基本语法：
with recursive cte_name as
(
initial_query -- anchor member
union all
recursive_query -- 引用CTE名称的递归成员
)
select *
from cte_name
;
CTE递归查询主要有三部分：
① 初始查询，形成递归查询的初始结果集
② 递归查询部分，引用CTE名称的查询
③ 终止条件，确保查询在不满足条件时终止
WITH RECURSIVE cte_count (n)
AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM cte_count
WHERE n < 3
)
SELECT n
FROM cte_count;
输出结果：
+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.01 sec)
注意：
递归部分，不能使用 聚合函数，distinct ，group by 子句， order by 子句，limit子句。
3. 测试数据
drop table if exists recursive_test;
create table recursive_test(
id int(8) AUTO_INCREMENT PRIMARY KEY comment '自增主键',
dept_id int(8) comment '部门ID',
dept_name varchar(40) comment '部门名称',
parent_id int(8) comment '父级部门ID'
)engine=InnoDB DEFAULT charset=utf8mb4 comment '部门表'
;
insert into recursive_test(dept_id, dept_name, parent_id) values(1, '宇宙总公司', 0);
insert into recursive_test(dept_id, dept_name, parent_id) values(2, '湖北分公司', 1);
insert into recursive_test(dept_id, dept_name, parent_id) values(3, '北京分公司', 1);
insert into recursive_test(dept_id, dept_name, parent_id) values(4, '上海分公司', 1);
insert into recursive_test(dept_id, dept_name, parent_id) values(5, '杭州分公司', 1);
insert into recursive_test(dept_id, dept_name, parent_id) values(123456, '上海黄浦办事处', 4);
insert into recursive_test(dept_id, dept_name, parent_id) values(123457, '上海长宁办事处', 4);
insert into recursive_test(dept_id, dept_name, parent_id) values(123458, '上海杨浦办事处', 4);
insert into recursive_test(dept_id, dept_name, parent_id) values(123459, '上海静安办事处', 4);
insert into recursive_test(dept_id, dept_name, parent_id) values(1234567, '上海黄浦办事处-1', 123456);
insert into recursive_test(dept_id, dept_name, parent_id) values(1234568, '上海黄浦办事处-2', 123456);
insert into recursive_test(dept_id, dept_name, parent_id) values(1234569, '上海黄浦办事处-3', 123456);
表结构如下：
从recursive_test表结构中较难对这些部门的层级关系有清晰的认识，对表中的组织层级关系绘制出来就一目了然了：
graph TB 宇宙总公司 --> 上海分公司 宇宙总公司 --> 湖北分公司 宇宙总公司 --> 北京分公司 宇宙总公司 --> 杭州分公司 上海分公司 --> 上海长宁办事处 上海分公司 --> 上海杨浦办事处 上海分公司 --> 上海黄浦办事处 上海分公司 --> 上海静安办事处 上海黄浦办事处 --> 上海黄浦办事处-1 上海黄浦办事处 --> 上海黄浦办事处-2 上海黄浦办事处 --> 上海黄浦办事处-3如果我们想将 宇宙总公司——>上海分公司——>上海黄浦办事处——>上海黄浦办事处-1 这些组织层级关系串起来，在mysql中用一个字段来表示该如何处理呢？
4. 解决思路
① 通过left join去关联表实现
当组织架构的层级是固定且层级数较少（建议最多3层）可以通过该方式实现，一旦层级数过多，这种方式不再适用。
② 自定义函数
通过自定义函数，可以先获取当前部门所在链路上的上级部门ID，然后对每个部门ID进行部门名称匹配（可在自定义函数中直接输出部门名称，自行尝试）
但是有个较大的缺点是 自定义函数的查询效率真的很低很低！！！因测试样本数据较少，运行效率还OK。
SET GLOBAL log_bin_trust_function_creators = 1;
delimiter $$
drop function if exists find_parent_list_test $$
create function find_parent_list_test(son_id varchar(20)) returns varchar(256)
begin
declare parent_list varchar(256);
select group_concat(a.departmentid) into parent_list
from
(
select
(select @dept_id := parent_id from recursive_test where dept_id=@dept_id) as departmentid
from recursive_test as a, (select @dept_id := son_id) as b
)as a
where a.departmentid is not null;
return parent_list;
end
$$
delimiter ;
③ 递归查询
根据根部门ID信息，遍历出所有的组织链路，通过 concat函数拼接起来。整体查询效率和便捷性还是很不错的。
with recursive party_detail as
(
select dept_id, dept_name, dept_name as dept_structure
from recursive_test
where dept_id=1 -- 根部门ID
union
select a.dept_id ,a.dept_name, concat(b.dept_structure, ' > ', a.dept_name) as dept_structure
from party_detail as b
join recursive_test as a on b.dept_id=a.parent_id
)
select *
from party_detail
;
参考来源：
https://www.begtut.com/mysql/mysql-recursive-cte.html