MySQL树形结构的数据库表设计和查询

时间：2022-04-12 20:22:08

1、邻接表（Adjacency List）

  • 实例：现在有一个要存储一下公司的人员结构，大致层次结构如下：

MySQL树形结构的数据库表设计和查询

那么怎么存储这个结构？并且要获取以下信息：

  • 1.查询小天的直接上司。
  • 2.查询老宋管理下的直属员工。
  • 3.查询小天的所有上司。
  • 4.查询老王管理的所有员工。
 方案一、(Adjacency List)只存储当前节点的父节点信息。

 -- -- MySQL树结构 --

 -- Author: xielong  Email:cnxielong@gmail.com --

  -- 建表 --

  DROP TABLE  IF EXISTS Employees;

  CREATE TABLE IF NOT EXISTS Employees (

   id INT AUTO_INCREMENT,

   ename VARCHAR (),

   job VARCHAR (),

   parent_id INT,

   PRIMARY KEY(id)

 ) ENGINE = INNODB DEFAULT CHARSET = UTF8;

 DESCRIBE Employees

 -- 插入数据 --

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('老王','高管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('老宋','产品部主管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('老牛','高管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小吴','高管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小李','高管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小欢','高管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小小','高管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小天','高管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('肖丽','高管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十号','高管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十一号','高管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十二号','高管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十三号','高管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('十四号','高管','');

 INSERT INTO `employees` ( `ename`, `job`, `parent_id`) VALUES('小黑十五','高管','');

数据库结构信息：

MySQL树形结构的数据库表设计和查询

  • 好的，现在开始进入回答环节：

  • 1.查询小天的直接上司： 
        -- 查询小天的上级Id  隐式内连接 关键条件父节点 --
    
     SELECT e2.id, e2.ename, e2.job FROM Employees e1, Employees e2 WHERE e1.`parent_id` = e2.id AND e1.id=

     -- 查询小天的上级Id  显示内连接 关键条件父节点 --
    
     SELECT e2.id, e2.ename, e2.job FROM Employees e1 INNER JOIN Employees e2 WHERE e1.`parent_id` = e2.id AND e1.id=

    MySQL树形结构的数据库表设计和查询

2.查询老宋管理下的直属员工：

  -- 查询老宋管理下的直属员工：隐式内连接 关键条件父子节点 老宋ID= --

     SELECT e2.id, e2.ename, e2.job FROM Employees e1, Employees e2 WHERE e1.id= AND e2.`parent_id` = e1.id

     -- 查询小天的上级Id  显示内连接 关键条件父子节点 老宋.ename='老宋' --

     SELECT e2.id, e2.ename, e2.job FROM Employees e1 INNER JOIN Employees e2 WHERE e2.`parent_id` = e1.id AND e1.ename='老宋'

MySQL树形结构的数据库表设计和查询

3.查询小天的所有上司。

  • 这里肯定没法直接查，只能用循环进行循环查询，先查直接上司，再查直接上司的直接上司，依次循环，这样麻烦的事情，还是得先建立一个存储过程：
  • 睁大眼睛看仔细了，接下来是骚操作环节：
 --  、查询小天的所有上级 --

 -- 删除 --

    DROP FUNCTION IF EXISTS getSuperiors; 

 -- 创建 --

 DELIMITER $$

 CREATE DEFINER=`root`@`localhost` FUNCTION `getSuperiors` (`uid` INT) RETURNS VARCHAR()

 BEGIN

     DECLARE superiors VARCHAR() DEFAULT '';

     DECLARE sTemp INTEGER DEFAULT uid;

     DECLARE tmpName VARCHAR();

     WHILE (sTemp>) DO

         SELECT parent_id INTO sTemp FROM employees WHERE id = sTemp;

         SELECT ename INTO tmpName FROM employees WHERE id = sTemp;

         IF(sTemp>)THEN

             SET superiors = CONCAT(tmpName,',',superiors);

         END IF;

     END WHILE;

         SET superiors = LEFT(superiors,CHARACTER_LENGTH(superiors)-);

     RETURN superiors;

 END $$

 -- 调用 --

 SELECT   getSuperiors() 上司;

  • 这一段存储过程可以查询子节点的所有父节点，来试验一下　

  • 好的，骚操作完成。

  • 显然，这样。获取子节点的全部父节点的时候很麻烦。。

MySQL树形结构的数据库表设计和查询

4.查询老王管理的所有员工。

　　思路如下：先获取所有父节点为老王id的员工id，然后将员工姓名加入结果列表里，在调用一个神奇的查找函数，即可进行神奇的查找：

   --  查询老王管理的所有员工 --

 -- 删除 --

    DROP PROCEDURE IF EXISTS getSubordinate 

 -- 创建 --

    DELIMITER $$

 CREATE DEFINER = `root` @`localhost` FUNCTION `getSubordinate` (`uid` INT) RETURNS VARCHAR ()

 BEGIN

   DECLARE str VARCHAR ();

   DECLARE cid VARCHAR ();

   DECLARE result VARCHAR ();

   DECLARE tmpName VARCHAR ();

   SET str = '$';

   SET cid = CAST(uid AS CHAR());

   WHILE

     cid IS NOT NULL DO

     SET str = CONCAT(str, ',', cid);

     SELECT  GROUP_CONCAT(id) INTO cid  FROM  employees  WHERE FIND_IN_SET(parent_id, cid);

   END WHILE;

   SELECT

     GROUP_CONCAT(ename) INTO result   FROM  employees  WHERE FIND_IN_SET(parent_id, str);

   RETURN result;

 END $$

 -- 调用 --

 SELECT   getSubordinate()

看神奇的结果：

MySQL树形结构的数据库表设计和查询 　

  • 虽然搞出来了，但说实话，真是不容易。。。

  • 这种方法的优点是存储的信息少，查直接上司和直接下属的时候很方便，缺点是多级查询的时候很费劲。所以当只需要用到直接上下级关系的时候，用这种方法还是不错的，可以节省很多空间。

2、继承关系驱动的设计表和基于左右值编码的设计

参考链接: https://blog.csdn.net/lj1314ailj/article/details/52074216

参考：MySQL 实现树形的遍历

MySQL 实现树形的遍历（关于多级菜单栏以及多级上下部门的查询问题）

参考链接: https://blog.csdn.net/mchdba/article/details/39277301 ---------------------

相关文章