開發中經常會遇到樹形結構的場景，比如：導航選單、組織機構等等，但凡是有這種父子層級結構的都是如此，一級類目、二級類目、三級類目，，，
對于這種樹形結構的表要如何設計呢？接下來一起探討一下
首先，想一個問題，用非關系型資料庫存盤可不可以？
答案是肯定可以的，比如用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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/281188.html
標籤：MySQL
上一篇：.NET 5/.NET Core使用EF Core 5連接MySQL資料庫寫入/讀取資料示例教程
下一篇：Mysql - 使用入門