本篇是一个补充知识点, 目的是为了下篇的后台管理系统中, 菜单权限的接口进行铺垫一下.
同时也是做个笔记, 因为在很多地方都会用这种 "树结构" 来实现很多权限, 层级, 菜单的处理哈.
在表设计层面通常是通过 id 和pid 来体现层级关系.
- id 表示表的每行菜单的唯一标识
- pid 标识这一行的
上级菜单id是谁, 这个 id 一定是在 所有 id 中的 - 假设我们约定,
pid = 0是顶级菜单
表结构设计
于是表设计就可以这样:
-- 菜单树的表结构 drop table if exists test_tree; create table test_tree ( id int auto_increment primary key comment '自增id' , pid int not null default 0 comment '父级id' , name varchar(100) not null comment '名称' , orders int not null default 0 comment '排序号' ); -- 插入数据 INSERT INTO test_tree (id, pid, name, orders) VALUES (1, 0, 'A1', 10), (2, 1, 'A1-1', 20), (3, 1, 'A1-2', 20), (4, 3, 'A1-2-1', 30), (5, 3, 'A1-2-2', 30), (6, 0, 'B1', 10), (7, 6, 'B1-1', 20), (8, 7, 'B1-1-1', 30), (9, 8, 'B1-1-1-1', 40); -- 递归查询某个 id 及其子节点 WITH RECURSIVE subordinates AS ( SELECT id, pid, name, orders FROM test_tree WHERE ID = 1 UNION ALL SELECT t.ID, t.PID, t.Name, t.`Orders` FROM test_tree t INNER JOIN subordinates s ON t.PID = s.ID ) SELECT * FROM subordinates; id, pid, orders 1 0 A1 10 2 1 A1-1 20 3 1 A1-2 20 4 3 A1-2-1 30 5 3 A1-2-2 30 拼接为 json 树
目的是为了方便前端渲染层级菜单, 通过 children 来进行拓展.
Python版
from typing import List, Dict def build_tree(menu_items: List[Dict], id='id', pid='pid') -> List[Dict]: """将菜单层级数据的 id, pid 平铺为 json 方式的""" menu_dict = { menu.get(id): menu for menu in menu_items } tree = [] for menu in menu_items: if not menu.get(pid): tree.append(menu) # 根节点 else: # 非根节点, 将其添加到父节点的 child 中 parent_menu = menu_dict.get(menu[pid]) print(parent_menu) if parent_menu: if 'children' not in parent_menu: parent_menu['children'] = [] parent_menu['children'].append(menu) return tree Go版
package main import ( "encoding/json" "fmt" ) type Menu struct { ID int `json:"id"` PID int `json:"parent_id"` Name string `json:"name"` Order int `json:"order"` Children []*Menu `json:"children"` } func BuildMenuTree(items []*Menu) []*Menu { nodeMap := make(map[int]*Menu) for _, node := range items { nodeMap[node.ID] = node } var tree []*Menu for _, node := range items { // 已约定 pid = 0 则为顶层节点 if node.PID == 0 { tree = append(tree, node) } else { // 找到父节点,将其挂载到其 children 中 if parent, exist := nodeMap[node.PID]; exist { parent.Children = append(parent.Children, node) } } } return tree } Go 也是一样的逻辑, 只是代码编写上要复杂一点, 原因在于,
- 它是静态编译型语言, 要确定类型, 同时结构体和 json 之间需要用到反射
reflect - Go 中数组是
值类型, 切片是对它的引用, 在处理中需要用到指针, 不然会进行节点重复创建
// 继续上面的测试 func main() { items := []*Menu{ {ID: 1, PID: 0, Name: "A1", Order: 10}, {ID: 2, PID: 1, Name: "A1-1", Order: 20}, {ID: 3, PID: 1, Name: "A1-2", Order: 20}, {ID: 4, PID: 3, Name: "A1-2-1", Order: 30}, {ID: 5, PID: 3, Name: "A1-2-2", Order: 30}, {ID: 6, PID: 0, Name: "B1", Order: 10}, {ID: 7, PID: 6, Name: "B1-1", Order: 20}, {ID: 8, PID: 7, Name: "B1-1-1", Order: 30}, {ID: 9, PID: 8, Name: "B1-1-1-1", Order: 40}, } tree := BuildMenuTree(items) // 将树结构体 (指针, 切片, 数组, map 等) 转为 json // prefix = "" 表示不用加前缀; indent = " " 表示每层缩进2空格 jsonData, err := json.MarshalIndent(tree, "", " ") if err != nil { fmt.Println("转换j son 失败: ", err) return } fmt.Println(string(jsonData)) } 输出:
[ { "id": 1, "parent_id": 0, "name": "A1", "order": 10, "children": [ { "id": 2, "parent_id": 1, "name": "A1-1", "order": 20, "children": null }, { "id": 3, "parent_id": 1, "name": "A1-2", "order": 20, "children": [ { "id": 4, "parent_id": 3, "name": "A1-2-1", "order": 30, "children": null }, { "id": 5, "parent_id": 3, "name": "A1-2-2", "order": 30, "children": null } ] } ] }, { "id": 6, "parent_id": 0, "name": "B1", "order": 10, "children": [ { "id": 7, "parent_id": 6, "name": "B1-1", "order": 20, "children": [ { "id": 8, "parent_id": 7, "name": "B1-1-1", "order": 30, "children": [ { "id": 9, "parent_id": 8, "name": "B1-1-1-1", "order": 40, "children": null } ] } ] } ] } ] 用的频率还是蛮高的, 但凡涉及这种树的结构, 基本都会用到这种 id + parent_id 的方式, 同时也是 SQL 的一个必备知识点, 即 自关联 + 子查询, 这个技能必须要拿下. 真的是自从有了 AI , 似乎理解知识点都是轻而易举呢.
