mysql实现邻接表

邻接表（Adjacency List）

常用场景: 多级菜单栏以及多级上下部门

使用mysql8 新增特性 CTES,可以很好的完成该功能

数据初始化

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


DROP TABLE  IF EXISTS employees;

 CREATE TABLE IF NOT EXISTS employees (
  id INT AUTO_INCREMENT,
  ename VARCHAR (100),
  job VARCHAR (100),
  parent_id INT,
  PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET = UTF8;


DESCRIBE Employees


-- 插入数据 --
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('老王','高管','0');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('老宋','产品部主管','1');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('老牛','高管','1');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小吴','高管','2');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小李','高管','2');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小欢','高管','3');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小小','高管','3');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小天','高管','4');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('肖丽','高管','4');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十号','高管','5');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十一号','高管','5');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十二号','高管','6');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十三号','高管','6');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十四号','高管','7');
INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小黑十五','高管','7');

查询所有上级

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


WITH RECURSIVE tree AS (
   SELECT id,
          ename,
          parent_id,
          1 as level
   FROM employees
   WHERE id = 8

   UNION ALL

   SELECT p.id,
          p.ename,
          p.parent_id,
          t.level + 1
   FROM employees p
     JOIN tree t ON t.parent_id = p.id
)
SELECT *
FROM tree

查询所有下级

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


WITH RECURSIVE tree AS (
   SELECT id,
          ename,
          parent_id,
          1 as level
   FROM employees
   WHERE id=2

   UNION ALL

   SELECT p.id,
          p.ename,
          p.parent_id,
          t.level + 1
   FROM employees p
     JOIN tree t ON t.id = p.parent_id
)
SELECT *
FROM tree

移动子树的根节点,只需更新第二级别的parent_id既可

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


WITH RECURSIVE tree AS (
   SELECT id,
          ename,
          parent_id,
          1 as level
   FROM employees
   WHERE id=2

   UNION ALL

   SELECT p.id,
          p.ename,
          p.parent_id,
          t.level + 1 level
   FROM employees p
     JOIN tree t ON t.id = p.parent_id
)
UPDATE employees set parent_id=1 WHERE id in (SELECT id FROM tree WHERE level=2);

MySQL树形结构的数据库表设计和查询_X-Dragon烟雨任平生的博客-CSDN博客_mysql树形结构查询

数据库表设计（邻接表、路径枚举、嵌套集、闭包表） - boboxing2017 - 博客园

mysql - Find highest level of a hierarchical field: with vs without CTEs - Database Administrators Stack Exchange

文章目录