在基於資料庫的一般應用中，查詢的需求總要大於刪除和修改。為了避免對於樹形結構查詢時的“遞迴”過程，基於Tree的前序遍歷設計一種全新的無遞迴查詢、無限分組的左右值編碼方案，來儲存該樹的資料。
第一次看見這種表結構，相信大部分人都不清楚左值（Lft）和右值（Rgt）是如何計算出來的，而且這種表設計似乎並沒有儲存父子節點的繼承關係。但當你用手指指著表中的數字從1數到18，你應該會發現點什麼吧。對，你手指移動的順序就是對這棵樹進行前序遍歷的順序，如下圖所示。當我們從根節點Food左側開始，標記為1，並沿前序遍歷的方向，依次在遍歷的路徑上標註數字，最後我們回到了根節點Food，並在右邊寫上了18。
依據此設計，我們可以推斷出所有左值大於2，並且右值小於11的節點都是Fruit的後續節點，整棵樹的結構通過左值和右值儲存了下來。然而，這還不夠，我們的目的是能夠對樹進行CRUD操作，即需要構造出與之配套的相關演算法。按照深度優先，由左到右的原則遍歷整個樹，從1開始給每個節點標註上left值和right值，並將這兩個值存入對應的name之中。
如何查詢？
1、獲取某個節點下的所有子孫節點，以Fruit為例：
|
1 |
SELECT * FROM Tree WHERE Lft > 2 AND Lft < 11 ORDER BY Lft ASC |
2、獲取子孫節點總數
子孫總數 = (右值–左值–1)/2，以Fruit為例，其子孫總數為：(11–2–1)/2 = 4
3、 獲取節點在樹中所處的層數，以Fruit為例：
|
1 |
SELECT COUNT(*) FROM Tree WHERE Lft <= 2 AND Rgt >=11 |
4、 獲取當前節點所在路徑，以Fruit為例：
|
1 |
SELECT * FROM Tree WHERE Lft <= 2 AND Rgt >=11 ORDER BY Lft ASC |
在日常的處理中我們經常還會遇到的需要獲取某一個節點的直屬上級、同級、直屬下級。為了更好的描述層級關係，我們可以為Tree建立一個檢視，新增一個層次列，該列數值可以編寫一個自定義函式來計算：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE FUNCTION `CountLayer`(`_node_id` int) RETURNS int(11) BEGIN DECLARE _result INT; DECLARE _lft INT; DECLARE _rgt INT; IF EXISTS(SELECT Node_id FROM Tree WHERE Node_id = _node_id) THEN SELECT Lft,Rgt FROM Tree WHERE Node_id = _node_id INTO _lft,_rgt; SET _result = (SELECT COUNT(1) FROM Tree WHERE Lft <= _lft AND Rgt >= _rgt); RETURN _result; ELSE RETURN 0; END IF; END; |
在新增完函式以後，我們建立一個a檢視，新增新的層次列：
|
1 2 |
CREATE VIEW `NewView`AS SELECT Node_id, Name, Lft, Rgt, CountLayer(Node_id) AS Layer FROM Tree ORDER BY Lft ; |
5、 獲取當前節點父節點,以Fruit為例：
|
1 |
SELECT * FROM treeview WHERE Lft <= 2 AND Rgt >=11 AND Layer=1 |
6、 獲取所有直屬子節點，以Fruit為例：
|
1 |
SELECT * FROM treeview WHERE Lft BETWEEN 2 AND 11 AND Layer=3 |
7、 獲取所有兄弟節點，以Fruit為例：
|
1 |
SELECT * FROM treeview WHERE Rgt > 11 AND Rgt < (SELECT Rgt FROM treeview WHERE Lft <= 2 AND Rgt >=11 AND Layer=1) AND Layer=2 |
8、 返回所有葉子節點
|
1 |
SELECT * FROM Tree WHERE Rgt = Lft + 1 |
如何建立樹？如何新增資料？
上面已經介紹瞭如何檢索結果，那麼如何才能增加新的節點呢？Nested set 最重要是一定要有一個根節點作為所有節點的起點，而且通常這個節點是不被使用的。為了便於控制查詢級別，在建表的時候建議新增parent_id配合之聯結列表方式一起使用。
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE IF NOT EXISTS `Tree` ( `node_id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(10) UNSIGNED NOT NULL DEFAULT '0', `name` varchar(255) NOT NULL, `lft` int(11) NOT NULL DEFAULT '0', `rgt` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`node_id`), KEY `idx_left_right` (`lft`,`rgt`) ) DEFAULT CHARSET=utf8; |
|
1 |
INSERT INTO `Tree` (parent_id,name,lft,rgt) VALUES ( 0,'Food',1,2) |
新增子節點（子節點起始處），以在Food下新增子節點Fruit為例：
|
1 2 3 4 5 6 |
LOCK TABLE Tree WRITE; SELECT @parent_id := node_id, @myLeft := lft FROM Tree WHERE name = 'Food'; UPDATE Tree SET rgt = rgt + 2 WHERE rgt > @myLeft; UPDATE Tree SET lft = lft + 2 WHERE lft > @myLeft; INSERT INTO Tree(parent_id, name, lft, rgt) VALUES(@parent_id, 'Fruit', @myLeft + 1, @myLeft + 2); UNLOCK TABLES; |
如需在末尾追加就需要以下方式進行（以在Red下新增Apple為例）：
|
1 2 3 4 5 6 |
LOCK TABLE Tree WRITE; SELECT @parent_id := node_id , @myRight := rgt FROM Tree WHERE name = 'Red'; UPDATE Tree SET rgt = rgt + 2 WHERE rgt >= @myRight; UPDATE Tree SET lft = lft + 2 WHERE lft > @myRight; INSERT INTO Tree(parent_id, name, lft, rgt) VALUES(@parent_id, 'Apple', @myRight, @myRight + 1); UNLOCK TABLES; |
在節點A後面新增同級節點（以在Yellow後面新增Green為例）
|
1 2 3 4 5 6 |
LOCK TABLE Tree WRITE; SELECT @parent_id := parent_id , @myRight := rgt FROM Tree WHERE name = 'Yellow'; UPDATE Tree SET rgt = rgt + 2 WHERE rgt > @myRight; UPDATE Tree SET lft = lft + 2 WHERE lft > @myRight; INSERT INTO Tree(parent_id, name, lft, rgt) VALUES(@parent_id, 'Green', @myRight+1, @myRight+2); UNLOCK TABLES; |
以上討論的新增節點指的都是新增末端節點，即插入的這個節點不是當前已存在節點的父節點。如果需要插入非末端節點要怎麼辦呢？
這個過程可以將流程分為2步，首先新增節點，接下里再將需要的節點移到新增的節點下級。節點移動方法(以將Apple移到Yellow中為例)：
|
1 2 3 4 5 6 7 8 9 10 |
LOCK TABLE Tree WRITE; SELECT @nodeId := node_id , @myLeft := lft , @myRight := rgt FROM Tree WHERE name = 'Apple'; UPDATE Tree SET lft = lft - (@myRight - @myLeft) - 1 WHERE lft > @myRight; UPDATE Tree SET rgt = rgt - (@myRight - @myLeft) - 1 WHERE rgt > @myRight; SELECT @parent_id := node_id , <a href="http://www.jobbole.com/members/wx1863158684">@Left</a> := lft , <a href="http://www.jobbole.com/members/wx3757768826">@Right</a> := rgt FROM Tree WHERE name = 'Yellow'; UPDATE Tree SET lft = lft + (@myRight - @myLeft) + 1 WHERE lft > @Left; UPDATE Tree SET rgt = rgt + (@myRight - @myLeft) + 1 WHERE lft > @Left; UPDATE Tree SET parent_id = @parent_id WHERE name = node_id = @nodeId; UPDATE Tree SET lft = <a href="http://www.jobbole.com/members/wx1863158684">@Left</a> + lft - @myLeft + 1, rgt = <a href="http://www.jobbole.com/members/wx1863158684">@Left</a> + lft - @myLeft + 1 + (@myRight - @myLeft) WHERE lft >= @myLeft AND rgt <= @myRight; UNLOCK TABLES; |
刪除節點（包含子節點）
|
1 2 3 4 5 6 |
LOCK TABLE Tree WRITE; SELECT @myLeft := lft , @myRight := rgt FROM Tree WHERE name = 'Apple'; DELETE Tree WHERE lft >= @myLeft AND rgt <= @myRight; UPDATE Tree SET lft = lft - (@myRight - @myLeft) - 1 WHERE lft > @myRight; UPDATE Tree SET rgt = rgt - (@myRight - @myLeft) - 1 WHERE rgt > @myRight; UNLOCK TABLES; |
如果需要只刪除該節點，子節點自動上移一級如何處理？
|
1 2 3 4 5 6 7 |
LOCK TABLE Tree WRITE; SELECT @parent_id := parent_id , @node_id :=node_id , @myLeft := lft , @myRight := rgt FROM Tree WHERE name = 'Red'; UPDATE Tree SET parent_id = @parent_id WHERE parent_id = @node_id DELETE Tree WHERE lft = @myLeft; UPDATE Tree SET lft = lft - 1,rgt = rgt-1 Where lft > @myLeft AND @rgt < @myRight UPDATE Tree SET lft = lft - 2,rgt = rgt-2 Where lft > @rgt > @myRight UNLOCK TABLES; |
以上為Nested Set的CURD操作，具體在使用時建議結合事務和儲存過程一起使用。本方案的優點時查詢非常的方便，缺點就是每次插入刪除資料涉及到的更新內容太多，如果樹非常大，插入一條資料可能花很長的時間。
參考連結：