業(yè)務中經常有一些樹形結構設計，比如公司的組織架構、服務調用鏈路等，每個節(jié)點都有子節(jié)點和父節(jié)點，樹的深度又是不確定的，這時候sql結構該怎么設計呢？
1、路徑枚舉模型
在這種模型中，我們?yōu)槊總€節(jié)點存儲一個字符串路徑，表示從根節(jié)點到該節(jié)點的路徑。例如，1/3/7 表示節(jié)點7是節(jié)點3的子節(jié)點，而節(jié)點3又是節(jié)點1的子節(jié)點。
create table org (
id int(11),
name varcher(32),
parent_id int(11),
path varchar(100)
);
這種方法簡單直觀，但當樹變得很大或很深時，路徑可能會變得非常長。
1.1 查詢某個節(jié)點的所有子節(jié)點
SELECT * FROM org WHERE path LIKE '1/2/%/'
// 1/2/ 為某個節(jié)點的path路徑
1.2 查詢某個節(jié)點的所有父節(jié)點
// 查詢1/2/10的所有祖先
SELECT * FROM org WHERE "1/2/10/" LIKE path + '%'
1.3 更新節(jié)點的路徑
//組織結構變更，
UPDATE org SET path = REPLACE(path, '1/2/', '1/2/10/') WHERE path LIKE '1/2/%';
2、閉包模型
閉包模型是一個專門為存儲樹形結構而設計的表。
包含一張節(jié)點基礎信息表，以及 一個包含節(jié)點與其所有祖先節(jié)點之間關系的關系列表；
這種方法允許你高效地查詢任何兩個節(jié)點之間的所有路徑。
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 刪除節(jié)點的所有子節(jié)點
DELETE FROM org_node WHERE parent_id = 2
2.2 查詢節(jié)點的所有子節(jié)點
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 新增節(jié)點
//如果要給CTO增加一個MGR2成員，則需要進行以下操作：
//1、為MGR2分配一個唯一的ID，并將其添加到nodes表中。
INSERT INTO org (name) VALUES ('MGR2'); //ID=10
//2、在closure表中插入自身的節(jié)點
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表包含節(jié)點的id、名稱等屬性。關系表org_node 用來存儲節(jié)點之間的關系，其中每一行記錄包含一個祖先節(jié)點和一個后代節(jié)點的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 統(tǒng)計一個節(jié)點的所有子節(jié)點
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 新插入節(jié)點
// 新建一個節(jié)點
INSERT INTO org (name) VALUES ('new_node');
// 添加新節(jié)點和其父節(jié)點之間的關系
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 查看一個節(jié)點下所有下屬的節(jié)點
//需要遞歸查詢
3.3 刪除一個節(jié)點的所有后續(xù)節(jié)點
//遞歸刪除
4、 優(yōu)缺點對比
4.1 路徑枚舉
優(yōu)點
查詢節(jié)點的所有祖先和后代節(jié)點更高效；
層級深度易于確定；缺點
更新節(jié)點需要更新大量的關系數(shù)據(jù)；
數(shù)據(jù)增加時占用存儲空間更大；
數(shù)據(jù)完整性無法保證；
4.2 閉包
優(yōu)點
不需要遞歸操作；
查詢節(jié)點的所有祖先和后代節(jié)點更高效；
層級深度易于確定；缺點
更新節(jié)點需要更新大量的關系數(shù)據(jù)；
層級深度大時，數(shù)據(jù)增加時占用存儲空間更大
4.3 嵌套集
優(yōu)點
簡單易懂，易于實現(xiàn)；
節(jié)點查詢和遍歷相對容易；缺點
刪除或更新節(jié)點需要遞歸操作，性能可能不佳；
節(jié)點的層級深度不易確定；
從工作中實踐來看，對于節(jié)點關系比較復雜的場景（比如，對于不同節(jié)點權限的控制），使用嵌套集模型是最優(yōu)的，不需要像嵌套集一樣總是遞歸操作，也不用像閉包模型一樣考慮數(shù)量太大的問題。
