经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库/运维 » MySQL » 查看文章
树形结构的菜单表设计与查询
来源:cnblogs  作者:废物大师兄  时间:2021/5/6 17:51:09  对本文有异议

开发中经常会遇到树形结构的场景,比如:导航菜单、组织机构等等,但凡是有这种父子层级结构的都是如此,一级类目、二级类目、三级类目。。。

对于这种树形结构的表要如何设计呢?接下来一起探讨一下

首先,想一个问题,用非关系型数据库存储可不可以?

答案是肯定可以的,比如用mongoDB,直接将整棵树存成json。但是,这样不利于按条件查询,当然也取决于具体的需求,抛开需求谈设计都是耍流氓。

在菜单这个场景下,一般还是用关系型数据库存储,可以将最终的查询结构缓存起来。

常用的方法有四种:

  • 每一条记录存parent_id
  • 每一条记录存整个tree path经过的node枚举
  • 每一条记录存 nleft 和 nright
  • 维护一个表,所有的tree path作为记录进行保存

 

第一种:每条记录存储parent_id

这种方式简单明了,但是想要查询某个节点的所有父级和子级的时候比较困难,势必需要用到递归,在mysql里面就得写存储过程,太麻烦了。

当然,如果只有两级的话就比较简单了,自连接就搞定了,例如:

第四种:单独用一种表保存节点之间的关系

CREATE TABLE `city`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4;

CREATE TABLE `city_tree_path_info`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city_id` int(11) NOT NULL,
  `ancestor_id` int(11) NOT NULL COMMENT '祖先ID',
  `level` tinyint(4) NOT NULL COMMENT '层级',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4;

上面这个例子中,city表代表城市,city_tree_path_info代表城市之间的层级关系,ancestor_id表示父级和祖父级ID,level是当前记录相对于ancestor_id而言的层级。这样就把整个层级关系保存到这张表中了,以后想查询某个节点的所有父级和子级就很容易了。

最后,我发现构造这种层级树最简单的还是用java代码

java递归生成菜单树

Menu.java 

 1 package com.example.demo.model;
 2 
 3 import lombok.AllArgsConstructor;
 4 import lombok.Data;
 5 import lombok.NoArgsConstructor;
 6 
 7 import java.util.List;
 8 
 9 @AllArgsConstructor
10 @NoArgsConstructor
11 @Data
12 public class Menu {
13 
14     /**
15      * 菜单ID
16      */
17     private Integer id;
18 
19     /**
20      * 父级菜单ID
21      */
22     private Integer pid;
23 
24     /**
25      * 菜单名称
26      */
27     private String name;
28 
29     /**
30      * 菜单编码
31      */
32     private String code;
33 
34     /**
35      * 菜单URL
36      */
37     private String url;
38 
39     /**
40      * 菜单图标
41      */
42     private String icon;
43 
44     /**
45      * 排序号
46      */
47     private int sort;
48 
49     /**
50      * 子级菜单
51      */
52     private List<Menu> children;
53 
54     public Menu(Integer id, Integer pid, String name, String code, String url, String icon, int sort) {
55         this.id = id;
56         this.pid = pid;
57         this.name = name;
58         this.code = code;
59         this.url = url;
60         this.icon = icon;
61         this.sort = sort;
62     }
63 
64 }

Test.java

 1 package com.example.demo.model;
 2 
 3 import com.fasterxml.jackson.core.JsonProcessingException;
 4 import com.fasterxml.jackson.databind.ObjectMapper;
 5 
 6 import java.util.ArrayList;
 7 import java.util.Comparator;
 8 import java.util.List;
 9 import java.util.stream.Collectors;
10 
11 public class Hello {
12     public static void main(String[] args) throws JsonProcessingException {
13         List<Menu> allMenuList = new ArrayList<>();
14         allMenuList.add(new Menu(1, 0, "湖北", "HuBei", "/a", "a", 3));
15         allMenuList.add(new Menu(2, 0, "河南", "HeNan", "/b", "b", 2));
16         allMenuList.add(new Menu(3, 1, "宜昌", "YiChang", "/c", "c", 2));
17         allMenuList.add(new Menu(4, 2, "信阳", "XinYang", "/d", "d", 1));
18         allMenuList.add(new Menu(5, 1, "随州", "SuiZhou", "/e", "e", 1));
19         allMenuList.add(new Menu(6, 5, "随县", "SuiXian", "/f", "f", 2));
20         allMenuList.add(new Menu(7, 3, "枝江", "ZhiJiang", "/g", "g", 2));
21 
22         //  一级菜单
23         List<Menu> parentList = allMenuList.stream().filter(e->e.getPid()==0).sorted(Comparator.comparing(Menu::getSort)).collect(Collectors.toList());
24         //  递归调用,为所有一级菜单设置子菜单
25         for (Menu menu : parentList) {
26             menu.setChildren(getChild(menu.getId(), allMenuList));
27         }
28 
29         ObjectMapper objectMapper = new ObjectMapper();
30         System.out.println(objectMapper.writeValueAsString(parentList));
31     }
32 
33     /**
34      * 递归查找子菜单
35      * @param id    当前菜单ID
36      * @param allList   查找菜单列表
37      * @return
38      */
39     public static List<Menu> getChild(Integer id, List<Menu> allList) {
40         //  子菜单
41         List<Menu> childList = new ArrayList<>();
42         for (Menu menu : allList) {
43             if (menu.getPid().equals(id)) {
44                 childList.add(menu);
45             }
46         }
47 
48         //  为子菜单设置子菜单
49         for (Menu nav : childList) {
50             nav.setChildren(getChild(nav.getId(), allList));
51         }
52 
53         //  排序
54         childList = childList.stream().sorted(Comparator.comparing(Menu::getSort)).collect(Collectors.toList());
55 
56         if (childList.size() == 0) {
57 //            return null;
58             return new ArrayList<>();
59         }
60         return childList;
61     }
62 }

结果:

 1 [
 2     {
 3         "id":2,
 4         "pid":0,
 5         "name":"河南",
 6         "code":"HeNan",
 7         "url":"/b",
 8         "icon":"b",
 9         "sort":2,
10         "children":[
11             {
12                 "id":4,
13                 "pid":2,
14                 "name":"信阳",
15                 "code":"XinYang",
16                 "url":"/d",
17                 "icon":"d",
18                 "sort":1,
19                 "children":[]
20             }
21         ]
22     },
23     {
24         "id":1,
25         "pid":0,
26         "name":"湖北",
27         "code":"HuBei",
28         "url":"/a",
29         "icon":"a",
30         "sort":3,
31         "children":[
32             {
33                 "id":5,
34                 "pid":1,
35                 "name":"随州",
36                 "code":"SuiZhou",
37                 "url":"/e",
38                 "icon":"e",
39                 "sort":1,
40                 "children":[
41                     {
42                         "id":6,
43                         "pid":5,
44                         "name":"随县",
45                         "code":"SuiXian",
46                         "url":"/f",
47                         "icon":"f",
48                         "sort":2,
49                         "children":[]
50                     }
51                 ]
52             },
53             {
54                 "id":3,
55                 "pid":1,
56                 "name":"宜昌",
57                 "code":"YiChang",
58                 "url":"/c",
59                 "icon":"c",
60                 "sort":2,
61                 "children":[
62                     {
63                         "id":7,
64                         "pid":3,
65                         "name":"枝江",
66                         "code":"ZhiJiang",
67                         "url":"/g",
68                         "icon":"g",
69                         "sort":2,
70                         "children":[]
71                     }
72                 ]
73             }
74         ]
75     }
76 ]

参考:

https://www.cnblogs.com/w2206/p/10490208.html

https://www.cnblogs.com/mokingone/p/9109021.html

https://www.cnblogs.com/makai/p/12301707.html

https://www.cnblogs.com/zhifengge/p/6910881.html 

 

原文链接:http://www.cnblogs.com/cjsblog/p/14714444.html

 友情链接:直通硅谷  点职佳