將Closure Table翻譯成閉包表不知道是否合適，閉包表的思路和物化路徑差不多，都是空間換時間，Closure Table，一種更為徹底的全路徑結構，分別記錄路徑上相關結點的全展開形式。能明晰任意兩結點關係而無須多餘查詢，級聯刪除和結點移動也很方便。但是它的儲存開銷會大一些，除了表示結點的Meta資訊，還需要一張專用的關係表。
以下圖舉例資料舉例：
建立主表：
|
1 2 3 4 5 |
CREATE TABLE nodeInfo ( node_id INT NOT NULL AUTO_INCREMENT, node_name VARCHAR (255), PRIMARY KEY (`node_id`) ) DEFAULT CHARSET = utf8; |
建立關係表：
|
1 2 3 4 5 6 |
CREATE TABLE nodeRelationship ( ancestor INT NOT NULL, descendant INT NOT NULL, distance INT NOT NULL, PRIMARY KEY (ancestor, descendant) ) DEFAULT CHARSET = utf8; |
其中
- Ancestor代表祖先節點
- Descendant代表後代節點
- Distance 祖先距離後代的距離
新增資料（建立儲存過程）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE DEFINER = `root`@`localhost` PROCEDURE `AddNode`(`_parent_name` varchar(255),`_node_name` varchar(255)) BEGIN DECLARE _ancestor INT; DECLARE _descendant INT; DECLARE _parent INT; IF NOT EXISTS(SELECT node_id From nodeinfo WHERE node_name = _node_name) THEN INSERT INTO nodeinfo (node_name) VALUES(_node_name); SET _descendant = (SELECT node_id FROM nodeinfo WHERE node_name = _node_name); INSERT INTO noderelationship (ancestor,descendant,distance) VALUES(_descendant,_descendant,0); IF EXISTS (SELECT node_id FROM nodeinfo WHERE node_name = _parent_name) THEN SET _parent = (SELECT node_id FROM nodeinfo WHERE node_name = _parent_name); INSERT INTO noderelationship (ancestor,descendant,distance) SELECT ancestor,_descendant,distance+1 from noderelationship where descendant = _parent; END IF; END IF; END; |
完成後2張表的資料大致是這樣的：（注意：每個節點都有一條到其本身的記錄。）
查詢Fruit下所有的子節點：
|
1 2 3 4 5 6 7 8 9 |
SELECT n3.node_name FROM nodeinfo n1 INNER JOIN noderelationship n2 ON n1.node_id = n2.ancestor INNER JOIN nodeinfo n3 ON n2.descendant = n3.node_id WHERE n1.node_name = 'Fruit' AND n2.distance != 0 |
查詢Fruit下直屬子節點：
|
1 2 3 4 5 6 7 8 9 |
SELECT n3.node_name FROM nodeinfo n1 INNER JOIN noderelationship n2 ON n1.node_id = n2.ancestor INNER JOIN nodeinfo n3 ON n2.descendant = n3.node_id WHERE n1.node_name = 'Fruit' AND n2.distance = 1 |
查詢Fruit所處的層級：
|
1 2 3 4 5 6 7 8 9 10 |
SELECT n2.*, n3.node_name FROM nodeinfo n1 INNER JOIN noderelationship n2 ON n1.node_id = n2.descendant INNER JOIN nodeinfo n3 ON n2.ancestor = n3.node_id WHERE n1.node_name = 'Fruit' ORDER BY n2.distance DESC |
另外要刪除節點也非常的簡單，這裡就不再做過多的闡述。
參考連結：
- https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql