需求背景

根据一个部门查询这个部门的全部子级部门

方案 1: 正常设计

字段 备注
id 部门 id
dept_name 部门名称
parent_id 上级部门 id

这种设计方案是符合第三范式的最简单方案。可以完美描述树状结构,并且没有任何冗余数据。但缺点也是显而易见的。如果跨层级查询就要递归查询

方案 2: 冗余路径

字段 备注
id 部门 id
dept_name 部门名称
parent_id 上级部门 id
path 从最上级到该节点的路径 以 / 分割

数据示例

id dept_name parent_id path
1 xx 科技有限公司 /1
2 研发部 1 /1/2
3 后端 2 /1/2/3
4 前端 2 /1/2/4
5 产品设计部 1 /1/5
6 PD 5 /1/5/6
7 UED 5 /1/5/7

在这样表设计的情况下,查询全部子集就变成了对 path 的前缀匹配。比如我要查询所有研发部的子部门就可以这样写 sql

1
select * from dept_info where path like '/1/2%';

这样的设计可以比较简单的完成查询需求,但是对于插入和更新就需要重新计算path,并且更新全部下级的path并且path字段不可以无限扩展,因为受到 mysql 字段大小的约束,当然可以在一开始把这个字段开的足够大。

移动部门
如果需要把产品设计部移动到研发部下面

1
2
3
update dept_info set parent_id = '2' where id = '5';

update dept_info set path = replace(path,'/1/5','1/2/5');

方案 3: 闭包表

方案 2 对于绝大部分的场景下已经足够用了。但是如果真的需要解决无限嵌套的问题该怎么办呢。现在之所以不能无限嵌套的原因在于path的字段长度有限。可以把path这个字段拿出来单独拆分成一张表。

把 path 字段拍平。记录全部根节点到叶子结点的关系。

字段 备注
id
ancestor 祖先 id
descendant 后代 id
depth 层级深度

但是这样层级太多的时候会带来大量的关联操作。而且在新增和编辑节点的时候操作相对复杂。其实得不偿失。