业务中经常有一些树形结构设计，比如公司的组织架构、服务调用链路等，每个节点都有子节点和父节点，树的深度又是不确定的，这时候sql结构该怎么设计呢？

1、路径枚举模型

在这种模型中，我们为每个节点存储一个字符串路径，表示从根节点到该节点的路径。例如，1/3/7 表示节点7是节点3的子节点，而节点3又是节点1的子节点。

create table org (
id int(11),
name varcher(32),
parent_id int(11),
path varchar(100)
);

这种方法简单直观，但当树变得很大或很深时，路径可能会变得非常长。

1.1 查询某个节点的所有子节点

SELECT * FROM org WHERE path LIKE '1/2/%/'
// 1/2/ 为某个节点的path路径

1.2 查询某个节点的所有父节点

// 查询1/2/10的所有祖先
SELECT * FROM org WHERE "1/2/10/" LIKE path + '%' 

1.3 更新节点的路径

//组织结构变更，
UPDATE org SET path = REPLACE(path, '1/2/', '1/2/10/') WHERE path LIKE '1/2/%';

2、闭包模型

闭包模型是一个专门为存储树形结构而设计的表。
包含一张节点基础信息表，以及 一个包含节点与其所有祖先节点之间关系的关系列表；

这种方法允许你高效地查询任何两个节点之间的所有路径。

create table org(
id int(11),
name varcher(32)
);

create table org_node (
id int(11),
parent_id int(11),
child_id int(11),
depth int(4) commit '从祖先到后代的深度'
);

2.1 删除节点的所有子节点

DELETE FROM org_node WHERE parent_id = 2

2.2 查询节点的所有子节点

SELECT n.id, n.name
FROM org n
JOIN org_node c ON n.id = c.child_id 
WHERE c.parent_id = 1 AND c.depth > 0;

2.3 新增节点

//如果要给CTO增加一个MGR2成员，则需要进行以下操作：

//1、为MGR2分配一个唯一的ID，并将其添加到nodes表中。
INSERT INTO org (name) VALUES ('MGR2'); //ID=10

//2、在closure表中插入自身的节点
INSERT INTO org_node (10, 10, 0)
//3、添加新的记录到closure表中，以捕获CTO与MGR2之间的关系。在此示例中，CTO是祖先，MGR2是后代，因此我们需要在org_node表中添加一条记录，其中parent_id是CTO的ID，child_id是MGR2的ID，depth为2

INSERT INTO org_node (parend_id, child_id, depth)
SELECT a.parend_id, d.child_id, a.depth + d.depth + 1
FROM org_node a, org_node d
WHERE a.child_id = 2 AND d.parend_id = 10;

3、嵌套集模型

就是最简单的树形结构，org表包含节点的id、名称等属性。关系表org_node 用来存储节点之间的关系，其中每一行记录包含一个祖先节点和一个后代节点的id，它们之间建立了一条从祖先到后代的路径。

create table org(
id int(11),
name varcher(32)
);

create table org_node (
id int(11),
parent_id int(11),
child_id int(11),
);

3.1 统计一个节点的所有子节点

SELECT child.*
FROM org AS parent
JOIN org_node relationships ON parent.id = relationships.parent_id
JOIN nodes AS child ON relationships.child_id= child.id
WHERE parent.id = ?

3.2 新插入节点

// 新建一个节点
INSERT INTO org (name) VALUES ('new_node');

// 添加新节点和其父节点之间的关系
INSERT INTO org_node(parent_id, child_id)
SELECT parent.id, new_node.id
FROM org  AS parent
WHERE parent.name = 'parent_node'
CROSS JOIN org  AS new_node
WHERE new_node.name = 'new_node';

3.2 查看一个节点下所有下属的节点

//需要递归查询

3.3 删除一个节点的所有后续节点

//递归删除 

4、 优缺点对比

4.1 路径枚举

• 优点
  查询节点的所有祖先和后代节点更高效；
  层级深度易于确定；

• 缺点
  更新节点需要更新大量的关系数据；
  数据增加时占用存储空间更大；

数据完整性无法保证；

4.2 闭包

• 优点
  不需要递归操作；
  查询节点的所有祖先和后代节点更高效；
  层级深度易于确定；

• 缺点
  更新节点需要更新大量的关系数据；
  层级深度大时，数据增加时占用存储空间更大

4.3 嵌套集

• 优点
  简单易懂，易于实现；
  节点查询和遍历相对容易；

• 缺点
  删除或更新节点需要递归操作，性能可能不佳；
  节点的层级深度不易确定；

从工作中实践来看，对于节点关系比较复杂的场景（比如，对于不同节点权限的控制），使用嵌套集模型是最优的，不需要像嵌套集一样总是递归操作，也不用像闭包模型一样考虑数量太大的问题。