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