原文:http://bbs.blueidea.com/viewthread.php?tid=2780498&highlight=
說明：本文雖然是以mysql為例子來作的介紹，但同樣適用於其他資料庫。
同樣介紹嵌套集合模型的文章：http://www.nirvanastudio.org/php/hierarchical-data-database.html
本文作者：Yimin http://liyimin.net/blog
加深閱讀Celko Joe - Trees and Hierarchies in SQL for Smarties
MYSQL中分層資料的管理
By Mike Hillyer
引言
大多數使用者都曾在資料庫中處理過分層資料(hierarchical data)，認為分層資料的管理不是關聯式資料庫的目的。之所以這麼認為，是因為關聯式資料庫中的表沒有層次關係，只是簡單的平面化的列表；而分層資料具有父 －子關係，顯然關聯式資料庫中的表不能自然地表現出其分層的特性。
我們認為，分層資料是每項只有一個父項和零個或多個子項（根項除外，根項沒有父項）的資料集合。分層資料存在於許多基於資料庫的應用程式中，包括論壇和郵件清單中的分類、商業組織圖表、內容管理系統的分類、產品分類。我們打算使用下面一個虛構的電子商店的產品分類：
這些分類層次與上面提到的一些例子中的分類層次是相類似的。在本文中我們將從傳統的鄰接表(adjacency list)模型出發，闡述2種在MySQL中處理分層資料的模型。
鄰接表模型
上述例子的分類資料將被儲存在下面的資料表中（我給出了全部的資料表建立、資料插入的代碼，你可以跟著做）：
CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL);
INSERT INTO category
VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),
(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);
SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | ELECTRONICS | NULL |
| 2 | TELEVISIONS | 1 |
| 3 | TUBE | 2 |
| 4 | LCD | 2 |
| 5 | PLASMA | 2 |
| 6 | PORTABLE ELECTRONICS | 1 |
| 7 | MP3 PLAYERS | 6 |
| 8 | FLASH | 7 |
| 9 | CD PLAYERS | 6 |
| 10 | 2 WAY RADIOS | 6 |
+-------------+-----------------------+------+
10 rows in set (0.00 sec)
在鄰接表模型中，資料表中的每項包含了指向其父項的指標。在此例中，最上層項的父項為空白值(NULL)。鄰接表模型的優勢在於它很簡單，可以很容易地看 出FLASH是MP3 PLAYER的子項，哪個是portable electronics的子項，哪個是electronics的子項。雖然，在用戶端編碼中鄰接表模型處理起來也相當的簡單，但是如果是純SQL編碼的 話，該模型會有很多問題。
檢索整樹
通常在處理分層資料時首要的任務是，以某種縮排形式來呈現一棵完整的樹。為此，在純SQL編碼中通常的做法是使用自串連(self-join)：
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';
+-------------+----------------------+--------------+-----+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+--------------+------+
| ELECTRONICS | TELEVISIONS | TUBE | NULL |
| ELECTRONICS | TELEVISIONS | LCD | NULL |
| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |
+-------------+----------------------+--------------+------+
6 rows in set (0.00 sec)
檢索所有葉子節點
我們可以用左串連(LEFT JOIN)來檢索出樹中所有葉子節點(沒有孩子節點的節點）：
SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;
+--------------+
| name |
+--------------+
| TUBE |
| LCD |
| PLASMA |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+--------------+
檢索單一路徑
通過自串連，我們也可以檢索出單一路徑：
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';
+-------------+----------------------+-------------+------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+-------------+--------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
+-------------+----------------------+-------------+-------+
1 row in set (0.01 sec)
這種方法的主要局限是你需要為每層資料添加一個自串連，隨著層次的增加，自串連變得越來越複雜，檢索的效能自然而然的也就下降了。
鄰接表模型的局限性
用純SQL編碼實現鄰接表模型有一定的難度。在我們檢索某分類的路徑之前，我們需要知道該分類所在的層次。另外，我們在刪除節點的時候要特別小心，因為潛 在的可能會孤立一棵子樹（當刪除portable electronics分類時，所有他的子分類都成了孤兒）。部分局限性可以通過使用用戶端代碼或者預存程序來解決，我們可以從樹的底部開始向上迭代來獲 得一顆樹或者單一路徑，我們也可以在刪除節點的時候使其子節點指向一個新的父節點，來防止孤立子樹的產生。
嵌套集合(Nested Set)模型
我想在這篇文章中重點闡述一種不同的方法，俗稱為嵌套集合模型。在嵌套集合模型中，我們將以一種新的方式來看待我們的分層資料，不再是線與點了，而是嵌套容器。我試著以嵌套容器的方式畫出了electronics分類圖：
從上圖可以看出我們依舊保持了資料的層次，父分類包圍了其子分類。在資料表中，我們通過使用表示節點的嵌套關係的左值(left value)和右值(right value)來表現嵌套集合模型中資料的分層特性：
CREATE TABLE nested_category (
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL
);
INSERT INTO nested_category
VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
(4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),
(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
(9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);
SELECT * FROM nested_category ORDER BY category_id;
+--------------+----------------------+-----+------+
| category_id | name | lft | rgt |
+--------------+----------------------+-----+------+
| 1 | ELECTRONICS | 1 | 20 |
| 2 | TELEVISIONS | 2 | 9 |
| 3 | TUBE | 3 | 4 |
| 4 | LCD | 5 | 6 |
| 5 | PLASMA | 7 | 8 |
| 6 | PORTABLE ELECTRONICS | 10 | 19 |
| 7 | MP3 PLAYERS | 11 | 14 |
| 8 | FLASH | 12 | 13 |
| 9 | CD PLAYERS | 15 | 16 |
| 10 | 2 WAY RADIOS | 17 | 18 |
+--------------+----------------------+-----+----+
我們使用了lft和rgt來代替left和right，是因為在MySQL中left和right是保留字。
http://dev.mysql.com/doc/mysql/en/reserved-words.html，有一份詳細的MySQL保留字清單。
那麼，我們怎樣決定左值和右值呢。我們從外層節點的最左側開始，從左至右編號：
這樣的編號方式也同樣適用於典型的樹狀結構：
當我們為樹狀的結構編號時，我們從左至右，一次一層，為節點賦右值前先從左至右遍曆其
子節點給其子節點賦左右值。這種方法被稱作改進的先序遍曆演算法。
檢索整樹
我們可以通過自串連把父節點串連到子節點上來檢索整樹，是因為子節點的lft值總是在其
父節點的lft值和rgt值之間：
SELECT node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;
+---------------------+
| name |
+---------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS|
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+---------------------+
不像先前鄰接表模型的例子，這個查詢語句不管樹的層次有多深都能很好的工作。在BETWEEN的子句中我們沒有去關心node的rgt值，是因為使用node的rgt值得出的父節點總是和使用lft值得出的是相同的。
檢索所有葉子節點
檢索出所有的葉子節點，使用嵌套集合模型的方法比鄰接表模型的LEFT JOIN方法簡單多了。如果你仔細得看了nested_category表，你可能已經注意到葉子節點的左右值是連續的。要檢索出葉子節點，我們只要尋找滿足rgt=lft+1的節點：
SELECT name
FROM nested_category
WHERE rgt = lft + 1;
+---------------+
| name |
+---------------+
| TUBE |
| LCD |
| PLASMA |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+---------------+
檢索單一路徑
在嵌套集合模型中，我們可以不用多個自串連就可以檢索出單一路徑：
SELECT parent.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
ORDER BY node.lft;
+------------------------+
| name |
+------------------------+
| ELECTRONICS |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
+------------------------+
檢索節點的深度
我們已經知道怎樣去呈現一棵整樹，但是為了更好的標識出節點在樹中所處層次，我們怎樣才能檢索出節點在樹中的深度呢。我們可以在先前的查詢語句上增加COUNT函數和GROUP BY子句來實現：
SELECT node.name, (COUNT(parent.name) 1)
AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+----------------------+--------+
| name | depth |
+----------------------+---+
| ELECTRONICS | 0 |
| TELEVISIONS | 1 |
| TUBE | 2 |
| LCD | 2 |
| PLASMA | 2 |
| PORTABLE ELECTRONICS | 1 |
| MP3 PLAYERS | 2 |
| FLASH | 3 |
| CD PLAYERS | 2 |
| 2 WAY RADIOS | 2 |
+----------------------+----+
我們可以根據depth值來縮排分類名字，使用CONCAT和REPEAT字串函數1：
SELECT CONCAT( REPEAT(' ', COUNT(parent.name) 1),
node.name) AS name
FROM nested_category AS node,
nested_category AS parent
1 [譯註] 縮排在phpMyAdmin下顯示會有出入，建議在MySQL命令列下執行查詢語句。
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
+---------------------------+
| name |
+---------------------------+
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
+---------------------------+
當然，在用戶端應用程式中你可能會用depth值來直接展示資料的層次。Web開發人員會遍曆該樹，隨著depth值的增加和減少來添加<li></li>和<ul></ul>標籤。
檢索子樹的深度當我們需要子樹的深度資訊時，我們不能限制自串連中的node或parent，因為這麼做會打亂資料集的順序。因此，我們添加了第三個自串連作為子查詢，來得出子樹新起點的深度值：
SELECT node.name, (COUNT(parent.name) (
sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) 1)
AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| FLASH | 2 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+---+
這個查詢語句可以檢索出任一節點子樹的深度值，包括根節點。這裡的深度值跟你指定的節點有關。
檢索節點的直接子節點
可以想象一下，你在零售網站上呈現電子產品的分類。當使用者點擊分類後，你將要呈現該分類下的產品，同時也需列出該分類下的直接子分類，而不是該分類下的全 部分類。為此，我們只呈現該節點及其直接子節點，不再呈現更深層次的節點。例如，當呈現PORTABLEELECTRONICS分類時，我們同時只呈現 MP3 PLAYERS、CD PLAYERS和2 WAY RADIOS分類，而不呈現FLASH分類。要實現它非常的簡單，在先前的查詢語句上添加HAVING子句：
SELECT node.name, (COUNT(parent.name) (
sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) 1)
AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;
+----------------------+-------+
| name | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS | 0 |
| MP3 PLAYERS | 1 |
| CD PLAYERS | 1 |
| 2 WAY RADIOS | 1 |
+----------------------+----+
如果你不希望呈現父節點，你可以更改HAVING depth <= 1為HAVING depth = 1。
