mysql 存储树形结构

最新推荐文章于 2023-08-14 20:51:18 发布

一个大泥球

最新推荐文章于 2023-08-14 20:51:18 发布

阅读量714

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/A_java_c/article/details/119835087

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

内容来自，其他网页，具体也忘了。

方案一：只存储上下级信息

数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `eid` int(10) NOT NULL,
  `ename` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `position` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `parent_id` int(10) NULL DEFAULT NULL,
  PRIMARY KEY (`eid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees
-- ----------------------------

INSERT INTO `employees` VALUES (1, '老王', '高管', 0);
INSERT INTO `employees` VALUES (2, '老宋', '产品部经理', 1);
INSERT INTO `employees` VALUES (3, '老牛', '产品部经理', 1);
INSERT INTO `employees` VALUES (4, '小吴', '产品A组长', 2);
INSERT INTO `employees` VALUES (5, '小李', '产品A组长', 2);
INSERT INTO `employees` VALUES (6, '小欢', '产品经理', 3);
INSERT INTO `employees` VALUES (7, '小小', '产品经理', 3);
INSERT INTO `employees` VALUES (8, '小天', '产品部员工', 4);
INSERT INTO `employees` VALUES (9, '小里', '产品部员工', 4);
INSERT INTO `employees` VALUES (10, '小黑', '产品部员工', 5);
INSERT INTO `employees` VALUES (11, '小红', '产品部员工', 5);
INSERT INTO `employees` VALUES (12, '小丽', '产品部员工', 6);
INSERT INTO `employees` VALUES (13, '小蓝', '产品部员工', 6);
INSERT INTO `employees` VALUES (14, '小黄', '产品部员工', 7);
INSERT INTO `employees` VALUES (15, '小真', '产品部员工', 7);

查询小天的直接上司

select e1.ename,e1.eid
from employees e1 ,employees e2
where e1.eid =e2.parent_id and e2.ename='小天';

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

select e1.ename,e1.eid
from employees e1 ,employees e2
where e1.parent_id =e2.eid and e2.ename = '老宋'

查询小天的所有上司

CREATE DEFINER=`root`@`localhost` FUNCTION `getSuperiors`(`uid` int) RETURNS varchar(1000) CHARSET gb2312
BEGIN
    DECLARE superiors VARCHAR(1000) DEFAULT '';
    DECLARE sTemp INTEGER DEFAULT uid;
    DECLARE tmpName VARCHAR(20);

    WHILE (sTemp>0) DO
            SELECT parent_id into sTemp FROM employees where eid = sTemp;
            SELECT ename into tmpName FROM employees where eid = sTemp;
            IF(sTemp>0)THEN
                SET superiors = concat(tmpName,',',superiors);
            END IF;
        END WHILE;
    SET superiors = LEFT(superiors,CHARACTER_LENGTH(superiors)-1);
    RETURN superiors;
END;

select getSuperiors(11) 上司;

这里肯定没法直接查，只能用循环进行循环查询，先查直接上司，再查直接上司的直接上司，依次循环，这样麻烦的事情.

获取子节点的全部父节点的时候很麻烦

查询老王管理的所有员工

#  查询某一父节点的所有子节点
CREATE DEFINER=`root`@`localhost` FUNCTION `getSubordinate`(`uid` int) RETURNS varchar(2000) CHARSET gb2312
BEGIN
    DECLARE str varchar(1000);
    DECLARE cid varchar(100);
    DECLARE result VARCHAR(1000);
    DECLARE tmpName VARCHAR(100);
    SET str = '$';
    SET cid = CAST(uid as char(10));
    WHILE cid is not null DO
            SET str = concat(str, ',', cid);
            SELECT group_concat(eid) 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(1);

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

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

方案二：存储路径

数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employees2
-- ----------------------------
DROP TABLE IF EXISTS `employees2`;
CREATE TABLE `employees2`  (
  `eid` int(10) NOT NULL,
  `ename` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `position` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `path` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`eid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees2
-- ----------------------------
INSERT INTO `employees2` VALUES (1, '老王', '高管', '/1');
INSERT INTO `employees2` VALUES (2, '老宋', '产品部经理', '/1/2');
INSERT INTO `employees2` VALUES (3, '老牛', '产品部经理', '/1/3');
INSERT INTO `employees2` VALUES (4, '小吴', '产品A组长', '/1/2/4');
INSERT INTO `employees2` VALUES (5, '小李', '产品A组长', '/1/2/5');
INSERT INTO `employees2` VALUES (6, '小欢', '产品经理', '/1/3/6');
INSERT INTO `employees2` VALUES (7, '小小', '产品经理', '/1/3/7');
INSERT INTO `employees2` VALUES (8, '小天', '产品部员工', '/1/2/4/8');
INSERT INTO `employees2` VALUES (9, '小里', '产品部员工', '/1/2/4/9');
INSERT INTO `employees2` VALUES (10, '小黑', '产品部员工', '/1/2/5/10');
INSERT INTO `employees2` VALUES (11, '小红', '产品部员工', '/1/2/5/11');
INSERT INTO `employees2` VALUES (12, '小丽', '产品部员工', '/1/3/6/12');
INSERT INTO `employees2` VALUES (13, '小蓝', '产品部员工', '/1/3/6/13');
INSERT INTO `employees2` VALUES (14, '小黄', '产品部员工', '/1/3/7/14');
INSERT INTO `employees2` VALUES (15, '小真', '产品部员工', '/1/3/7/15');

SET FOREIGN_KEY_CHECKS = 1;

查询小天的直接上司

思路：因为需要对path字段进行字符串处理，去掉“/”+自身id才是直接上司的path值。

SELECT e1.eid, e1.ename
FROM employees2 e1,
     employees2 e2
WHERE e2.ename = '小天'
  AND e1.path = REPLACE(e2.path, CONCAT('/', e2.eid), '');

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

模糊查询


# 查询 某一节点的直接下属
SELECT e2.eid, e2.ename
FROM employees2 e1,
     employees2 e2
WHERE e1.ename = '老宋'
  AND e2.path REGEXP CONCAT(e1.path, '/[0-9]{1,}$');

查询小天的所有上司

SELECT e1.eid, e1.ename
FROM employees2 e1,
     employees2 e2
WHERE e2.ename = '小天'
  AND e2.path like concat(e1.path, '/%');

查询老王管理的所有员工

# 查询老王管理的所有员工
SELECT e2.eid, e2.ename
FROM employees2 e1,
     employees2 e2
WHERE e1.ename = '老王'
  AND e2.path like concat(e1.path, '/%');

小结一下，存储路径的方式在进行多级查询的时候十分方便，而在查询直接上下级的时候稍微复杂一点。还有一个很明显的缺点，那就是path的大小是指定的，所以理论上是不能进行无限层级的存储的，path值设置的越大，浪费的空间就越多。

方案三：中间表

数据

-- Table structure for employees3
-- ----------------------------
DROP TABLE IF EXISTS `employees3`;
CREATE TABLE `employees3`  (
  `eid` int(11) NULL DEFAULT NULL,
  `ename` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `position` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees3
-- ----------------------------
INSERT INTO `employees3` VALUES (1, '老王', '高管');
INSERT INTO `employees3` VALUES (2, '老宋', '产品部经理');
INSERT INTO `employees3` VALUES (3, '老牛', '产品部经理');
INSERT INTO `employees3` VALUES (4, '小吴', '产品A组长');
INSERT INTO `employees3` VALUES (5, '小李', '产品A组长');
INSERT INTO `employees3` VALUES (6, '小欢', '产品经理');
INSERT INTO `employees3` VALUES (7, '小小', '产品经理');
INSERT INTO `employees3` VALUES (8, '小天', '产品部员工');
INSERT INTO `employees3` VALUES (9, '小里', '产品部员工');
INSERT INTO `employees3` VALUES (10, '小黑', '产品部员工');
INSERT INTO `employees3` VALUES (11, '小红', '产品部员工');
INSERT INTO `employees3` VALUES (12, '小丽', '产品部员工');
INSERT INTO `employees3` VALUES (13, '小蓝', '产品部员工');
INSERT INTO `employees3` VALUES (14, '小黄', '产品部员工');
INSERT INTO `employees3` VALUES (15, '小真', '产品部员工');


----中间表

DROP TABLE IF EXISTS `emp_relations`;
CREATE TABLE `emp_relations`  (
  `eid` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `root_id` int(11) NULL DEFAULT NULL COMMENT '该节点的直接上级',
  `depth` int(11) NULL DEFAULT NULL COMMENT '根节点到这个节点的深度',
  `is_leaf` tinyint(1) NULL DEFAULT NULL COMMENT '是否叶子节点',
  `node_id` int(11) NULL DEFAULT NULL COMMENT '主表的主键',
  PRIMARY KEY (`eid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 48 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp_relations
-- ----------------------------
INSERT INTO `emp_relations` VALUES (1, 1, 0, 0, 1);
INSERT INTO `emp_relations` VALUES (2, 1, 1, 0, 2);
INSERT INTO `emp_relations` VALUES (3, 1, 1, 0, 3);
INSERT INTO `emp_relations` VALUES (4, 1, 2, 0, 4);
INSERT INTO `emp_relations` VALUES (5, 1, 2, 0, 5);
INSERT INTO `emp_relations` VALUES (6, 1, 2, 0, 6);
INSERT INTO `emp_relations` VALUES (7, 1, 2, 0, 7);
INSERT INTO `emp_relations` VALUES (8, 1, 3, 1, 8);
INSERT INTO `emp_relations` VALUES (9, 1, 3, 1, 9);
INSERT INTO `emp_relations` VALUES (10, 1, 3, 1, 11);
INSERT INTO `emp_relations` VALUES (11, 1, 3, 1, 12);
INSERT INTO `emp_relations` VALUES (12, 1, 3, 1, 13);
INSERT INTO `emp_relations` VALUES (13, 1, 3, 1, 14);
INSERT INTO `emp_relations` VALUES (14, 1, 3, 1, 15);
INSERT INTO `emp_relations` VALUES (15, 1, 3, 1, 10);
INSERT INTO `emp_relations` VALUES (16, 2, 0, 0, 2);
INSERT INTO `emp_relations` VALUES (17, 2, 1, 0, 4);
INSERT INTO `emp_relations` VALUES (18, 2, 1, 0, 5);
INSERT INTO `emp_relations` VALUES (19, 2, 2, 1, 9);
INSERT INTO `emp_relations` VALUES (20, 2, 2, 1, 10);
INSERT INTO `emp_relations` VALUES (21, 2, 2, 1, 11);
INSERT INTO `emp_relations` VALUES (22, 2, 2, 1, 8);
INSERT INTO `emp_relations` VALUES (23, 3, 0, 0, 3);
INSERT INTO `emp_relations` VALUES (24, 3, 1, 0, 6);
INSERT INTO `emp_relations` VALUES (25, 3, 1, 0, 7);
INSERT INTO `emp_relations` VALUES (26, 3, 2, 1, 13);
INSERT INTO `emp_relations` VALUES (27, 3, 2, 1, 14);
INSERT INTO `emp_relations` VALUES (28, 3, 2, 1, 15);
INSERT INTO `emp_relations` VALUES (29, 3, 2, 1, 12);
INSERT INTO `emp_relations` VALUES (30, 4, 0, 0, 4);
INSERT INTO `emp_relations` VALUES (31, 4, 1, 1, 8);
INSERT INTO `emp_relations` VALUES (32, 4, 1, 1, 9);
INSERT INTO `emp_relations` VALUES (33, 5, 0, 0, 5);
INSERT INTO `emp_relations` VALUES (34, 5, 1, 1, 10);
INSERT INTO `emp_relations` VALUES (35, 5, 1, 1, 11);
INSERT INTO `emp_relations` VALUES (36, 6, 1, 1, 12);
INSERT INTO `emp_relations` VALUES (37, 6, 1, 1, 13);
INSERT INTO `emp_relations` VALUES (38, 7, 1, 1, 14);
INSERT INTO `emp_relations` VALUES (39, 7, 1, 1, 15);
INSERT INTO `emp_relations` VALUES (40, 8, 0, 1, 8);
INSERT INTO `emp_relations` VALUES (41, 9, 0, 1, 9);
INSERT INTO `emp_relations` VALUES (42, 10, 0, 1, 10);
INSERT INTO `emp_relations` VALUES (43, 11, 0, 1, 11);
INSERT INTO `emp_relations` VALUES (44, 12, 0, 1, 12);
INSERT INTO `emp_relations` VALUES (45, 13, 0, 1, 13);
INSERT INTO `emp_relations` VALUES (46, 14, 0, 1, 14);
INSERT INTO `emp_relations` VALUES (47, 15, 0, 1, 15);

SET FOREIGN_KEY_CHECKS = 1;

查询小天的直接上司

这里只需要在关系表中找到node_id为小天id，depth为1的根节点id即可


# 查询小天的直接上司
SELECT e2.ename BOSS
FROM employees3 e1,
     employees3 e2,
     emp_relations rel
WHERE e1.ename = '小天'
  AND rel.node_id = e1.eid
  AND rel.depth = 1
  AND e2.eid = rel.root_id;

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

只要查询root_id为老宋eid且深度为1的node_id即为其直接下属员工id


# 查询老宋管理下的直属员工
SELECT e1.eid, e1.ename 直接下属
FROM employees3 e1,
     employees3 e2,
     emp_relations rel
WHERE e2.ename = '老宋'
  AND rel.root_id = e2.eid
  AND rel.depth = 1
  AND e1.eid = rel.node_id;

查询小天的所有上司

只要在关系表中找到node_id为小天eid且depth大于0的root_id即可

# 查询小天的所有上司
SELECT e2.eid, e2.ename as 上司
FROM employees3 e1,
     employees3 e2,
     emp_relations rel
WHERE e1.ename = '小天'
  AND rel.node_id = e1.eid
  AND rel.depth > 0
  AND e2.eid = rel.root_id;

查询老王管理的所有员工

只要在关系表中查找root_id为老王eid，depth大于0的node_id即可

#查询老王管理的所有员工
SELECT e1.eid, e1.ename 下属
FROM employees3 e1,
     employees3 e2,
     emp_relations rel
WHERE e2.ename = '老王'
  AND rel.root_id = e2.eid
  AND rel.depth > 0
  AND e1.eid = rel.node_id;

我们可以发现，这四个查询的复杂程度是一样的，这就是这种存储方式的优点，而且可以让另一张表只存储跟节点紧密相关的信息，看起来更简洁。但缺点也显而易见，关系表会很庞大，当层次很深，结构很庞大的时候，关系表数据的增长会越来越快，相当于用空间效率来换取了查找上的时间效率。

总结

方案一：Adjacency List

优点：只存储上级id，存储数据少，结构类似于单链表，在查询相邻节点的时候很方便。添加删除节点都比较简单。

缺点：查询多级结构的时候会显得力不从心。

适用场合：对多级查询需求不大的场景比较适用。

方案二：Path Enumeration

优点：查询多级结构的时候比较方便。查询相邻节点时也比较ok。增加或者删除节点的时候比较简单。

缺点：需要存储的path值可能会很大，甚至超过设置的最大值范围，理论上无法无限扩张。

适用场合：结构相对简单的场景比较适合。

方案三：Closure Table

优点：在查询树形结构的任意关系时都很方便。

缺点：需要存储的数据量比较多，索引表需要的空间比较大，增加和删除节点相对麻烦。

适用场合：纵向结构不是很深，增删操作不频繁的场景比较适用。

例子

期望将树形数据，做成类似页面
在这里插入图片描述
功能点：
1-可根据一级或者二级或者三级进行搜索四级。(模糊查询 fullIdPath）
2-根据名称搜索
3-展示该四级前三级名称

表结构

create table `t_name`(
    `id` bigint primary key ,
      `parent_id` bigint  comment '父id.0表根层',
      `name` varchar(20),
    `level` bigint comment '层级',
    `full_id_path` varchar(200) comment '全路径ID，以,分割'
    )

展示所以层级名称。可收集分页记录中所有 full_id_path 路径转成 set集合，查询id和name 然后转成 hashMap。

遍历 voList，switch进行设置
在这里插入图片描述

一个大泥球

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql 存储树形结构

内容来自，其他网页，具体也忘了。方案一：只存储上下级信息数据SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for employees-- ----------------------------DROP TABLE IF EXISTS `employees`;CREATE TABLE `employees` ( `eid` int(10)
复制链接

扫一扫