表设计

部门表

create table `departments` (
  `id` int primary key comment 'id',
  `parent_id` int comment '父级id',
  `name` varchar(100) comment '部门名称',
  key `idx_parent_id`(`parent_id`)
) comment '部门表';

部门闭包表

create table `departments_closure_table` (
  `ancestor` int comment '祖先',
  `descendant` int comment '后代',
  `depth` int comment '深度，自己到自己则深度为0',
  primary key (`ancestor`, `descendant`, `depth`),
  key `idx_ancestor`(`ancestor`),
  key `idx_descendant`(`descendant`)
) comment '闭包表';

表初始化

初始化部门表

INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (1, '集团总部', NULL);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (2, '华北总部', 1);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (3, '华南总部', 1);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (4, '华东总部', 1);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (5, '华中总部', 1);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (6, '华西总部', 1);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (7, '北京子公司', 2);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (8, '天津子公司', 2);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (9, '河北子公司', 2);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (10, '广东子公司', 3);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (11, '广西子公司', 3);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (12, '海南子公司', 3);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (13, '四川子公司', 6);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (14, '重庆子公司', 6);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (15, '贵州子公司', 6);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (16, '云南子公司', 6);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (17, '成都办事处', 13);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (18, '广元办事处', 13);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (19, '雅安办事处', 13);
INSERT INTO `departments`(`id`, `name`, `parent_id`) VALUES (20, '绵阳办事处', 13);

初始化闭包表

方式一：一层一层初始化

-- 初始化自身关系
INSERT INTO departments_closure_table (ancestor, descendant, depth)
SELECT id, id, 0
FROM departments;

-- 初始化父子关系
INSERT INTO departments_closure_table (ancestor, descendant, depth)
SELECT ct.ancestor, d.id, ct.depth + 1
FROM departments_closure_table AS ct
JOIN departments AS d ON ct.descendant = d.parent_id
where ct.depth + 1 = 1;

-- 初始化爷孙关系
INSERT INTO departments_closure_table (ancestor, descendant, depth)
SELECT ct.ancestor, d.id, ct.depth + 1
FROM departments_closure_table AS ct
JOIN departments AS d ON ct.descendant = d.parent_id
where ct.depth + 1 = 2;

-- 初始化四代关系
INSERT INTO departments_closure_table (ancestor, descendant, depth)
SELECT ct.ancestor, d.id, ct.depth + 1
FROM departments_closure_table AS ct
JOIN departments AS d ON ct.descendant = d.parent_id
where ct.depth + 1 = 3;

方式二：使用 mysql 8.x 数据库递归方式

INSERT INTO departments_closure_table (ancestor, descendant, depth)
WITH RECURSIVE cte AS (
  SELECT id as ancestor, id as descendant, 0 as depth
  FROM departments
  UNION ALL
  SELECT cte.ancestor, departments.id, cte.depth + 1
  FROM cte
  JOIN departments ON cte.descendant = departments.parent_id
)
SELECT ancestor, descendant, depth
FROM cte;

查询

查询所有叶子节点访问路径

SELECT
	descendant AS id,
	GROUP_CONCAT( ancestor ) AS path 
FROM
	( SELECT * FROM departments_closure_table ORDER BY depth DESC ) AS ct 
WHERE
	ct.descendant IN ( SELECT id FROM departments AS t1 WHERE t1.id NOT IN ( SELECT parent_id FROM departments WHERE parent_id IS NOT NULL ) ) 
GROUP BY
	ct.descendant

id	path
4	1,4
5	1,5
7	1,2,7
8	1,2,8
9	1,2,9
10	1,3,10
11	1,3,11
12	1,3,12
14	1,6,14
15	1,6,15
16	1,6,16
17	1,6,13,17
18	1,6,13,18
19	1,6,13,19
20	1,6,13,20

更新

方式一：全量更新

1、先清除整个闭包表数据

2、重新跑一次初始化比报表

方式二：刷新某个部门以及其子孙部门的闭包关系

1、找出当前部门的直接父级部门

select parent_id from departments where id = 当前部门id

2、找出当前部门 id 以及它的所有子孙部门 id

select descendant from departments_closure_table where ancestor = 当前部门id

3、删除当前部门以及它子孙部门的所有闭包记录

delete from departments_closure_table where descendant in (
select descendant from departments_closure_table where ancestor = 当前部门id
)

4、跑初始化闭包表的 sql ，但要额外加上过滤条件

方式一：一层一层

INSERT INTO departments_closure_table (ancestor, descendant, depth)
SELECT id, id, 0
FROM departments where id in (当前部门id, 它的子孙部门id);

-- 初始化父子关系
INSERT INTO departments_closure_table (ancestor, descendant, depth)
SELECT ct.ancestor, d.id, ct.depth + 1
FROM departments_closure_table AS ct
JOIN departments AS d ON ct.descendant = d.parent_id
where ct.depth + 1 = 1 and d.id in (当前部门id, 它的子孙部门id);

-- 初始化爷孙关系
INSERT INTO departments_closure_table (ancestor, descendant, depth)
SELECT ct.ancestor, d.id, ct.depth + 1
FROM departments_closure_table AS ct
JOIN departments AS d ON ct.descendant = d.parent_id
where ct.depth + 1 = 2 and d.id in (当前部门id, 它的子孙部门id);

-- 初始化四代关系
INSERT INTO departments_closure_table (ancestor, descendant, depth)
SELECT ct.ancestor, d.id, ct.depth + 1
FROM departments_closure_table AS ct
JOIN departments AS d ON ct.descendant = d.parent_id
where ct.depth + 1 = 3 and d.id in (当前部门id, 它的子孙部门id);

方式二：mysql 8.x 递归

INSERT INTO departments_closure_table (ancestor, descendant, depth)
WITH RECURSIVE cte AS (
  select * from (
      SELECT id as ancestor, id as descendant, 0 as depth FROM departments where id in (当前部门id, 它的子孙部门id)
      UNION
      select * from departments_closure_table where descendant = 当前部门的直接父级部门id
  )
  UNION ALL
  SELECT cte.ancestor, departments.id, cte.depth + 1
  FROM cte
  JOIN departments ON cte.descendant = departments.parent_id
  where departments.id in (当前部门id, 它的子孙部门id)
)
SELECT ancestor, descendant, depth
FROM cte
where descendant != 当前部门的直接父级部门id

参考：优雅的对树形结构进行高性能分页，闭包表才是 yyds