分層資料Hierarchical Data探索（例如：無限級分類、多級選單、省份城市）
引言
第一篇 分層資料Hierarchical Data探索(1.遞迴)已經介紹了分層資料以及使用遞迴演算法實現了無限極分類，但是遞迴即浪費時間，又浪費空間（記憶體）,尤其是在資料量大的情況下效率顯著下降。
那麼，在MySQL中如何處理分層資料呢？下面我們來說一說資料模型鄰接表模型
- 分層資料Hierarchical Data探索(1.遞迴 recursion)
- 分層資料Hierarchical Data探索(2.鄰接表模型 Adjacency List Model)
- 分層資料Hierarchical Data探索(3.巢狀集合模型 Nested Set Model)
鄰接表模型(Adjacency List Model)
更多 鄰接表模型（Adjacency List Model）的介紹請見:wiki
# 為了模擬，我們建立一個表category包含三個欄位：id，title，和parent_id如下：
CREATE TABLE category (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL,
parent_id int(10) unsigned DEFAULT NULL,
PRIMARY KEY (id),
FOREIGN KEY (parent_id) REFERENCES category (id)
ON DELETE CASCADE ON UPDATE CASCADE
);
# 插入模擬資料
INSERT INTO category(title,parent_id) VALUES('Electronics',NULL);
INSERT INTO category(title,parent_id) VALUES('Laptops & PC',1);
INSERT INTO category(title,parent_id) VALUES('Laptops',2);
INSERT INTO category(title,parent_id) VALUES('PC',2);
INSERT INTO category(title,parent_id) VALUES('Cameras & photo',1);
INSERT INTO category(title,parent_id) VALUES('Camera',5);
INSERT INTO category(title,parent_id) VALUES('Phones & Accessories',1);
INSERT INTO category(title,parent_id) VALUES('Smartphones',7);
INSERT INTO category(title,parent_id) VALUES('Android',8);
INSERT INTO category(title,parent_id) VALUES('iOS',8);
INSERT INTO category(title,parent_id) VALUES('Other Smartphones',8);
INSERT INTO category(title,parent_id) VALUES('Batteries',7);
INSERT INTO category(title,parent_id) VALUES('Headsets',7);
INSERT INTO category(title,parent_id) VALUES('Screen Protectors',7);
select * from category;
+----+----------------------+-----------+
| id | title | parent_id |
+----+----------------------+-----------+
| 1 | Electronics | NULL |
| 2 | Laptops & PC | 1 |
| 3 | Laptops | 2 |
| 4 | PC | 2 |
| 5 | Cameras & photo | 1 |
| 6 | Camera | 5 |
| 7 | Phones & Accessories | 1 |
| 8 | Smartphones | 7 |
| 9 | Android | 8 |
| 10 | iOS | 8 |
| 11 | Other Smartphones | 8 |
| 12 | Batteries | 7 |
| 13 | Headsets | 7 |
| 14 | Screen Protectors | 7 |
+----+----------------------+-----------+
14 rows in set (0.00 sec)- 檢索根節點
SELECT * FROM category WHERE parent_id IS NULL;
+----+-------------+-----------+
| id | title | parent_id |
+----+-------------+-----------+
| 1 | Electronics | NULL |
+----+-------------+-----------+
1 row in set (0.00 sec)- 檢索所有葉子節點
SELECT
c1.id, c1.title
FROM
category c1
LEFT JOIN
category c2 ON c2.parent_id = c1.id
WHERE
c2.id IS NULL;
+----+-------------------+
| id | title |
+----+-------------------+
| 3 | Laptops |
| 4 | PC |
| 6 | Camera |
| 9 | Android |
| 10 | iOS |
| 11 | Other Smartphones |
| 12 | Batteries |
| 13 | Headsets |
| 14 | Screen Protectors |
+----+-------------------+
9 rows in set (0.00 sec)- 檢索整棵樹的分層路徑
SELECT t1.title AS lev1, t2.title as lev2, t3.title as lev3, t4.title as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent_id = t1.id
LEFT JOIN category AS t3 ON t3.parent_id = t2.id
LEFT JOIN category AS t4 ON t4.parent_id = t3.id
WHERE t1.title = 'Electronics';
-------------+----------------------+-------------------+-------------------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+-------------------+-------------------+
| Electronics | Laptops & PC | Laptops | NULL |
| Electronics | Laptops & PC | PC | NULL |
| Electronics | Cameras & photo | Camera | NULL |
| Electronics | Phones & Accessories | Smartphones | Android |
| Electronics | Phones & Accessories | Smartphones | iOS |
| Electronics | Phones & Accessories | Smartphones | Other Smartphones |
| Electronics | Phones & Accessories | Batteries | NULL |
| Electronics | Phones & Accessories | Headsets | NULL |
| Electronics | Phones & Accessories | Screen Protectors | NULL |
+-------------+----------------------+-------------------+-------------------+
9 rows in set (0.00 sec)- 檢索單一指定路徑
SELECT t1.title AS lev1, t2.title as lev2, t3.title as lev3, t4.title as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent_id = t1.id
LEFT JOIN category AS t3 ON t3.parent_id = t2.id
LEFT JOIN category AS t4 ON t4.parent_id = t3.id
WHERE t1.title = 'Electronics' AND t4.title = 'iOS';
+-------------+----------------------+-------------+------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+-------------+------+
| Electronics | Phones & Accessories | Smartphones | iOS |
+-------------+----------------------+-------------+------+
1 row in set (0.00 sec)以下遞迴公用表示式（CTE）檢索。請注意，MySQL 8.0以上版本，CTE功能已經支援
- CTE 查詢整棵樹
WITH RECURSIVE category_path (id, title, path) AS
(
SELECT id, title, title as path
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.title, CONCAT(cp.path, ' > ', c.title)
FROM category_path AS cp JOIN category AS c
ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY path;
+------+----------------------+----------------------------------------------------------------------+
| id | title | path |
+------+----------------------+----------------------------------------------------------------------+
| 1 | Electronics | Electronics |
| 5 | Cameras & photo | Electronics > Cameras & photo |
| 6 | Camera | Electronics > Cameras & photo > Camera |
| 2 | Laptops & PC | Electronics > Laptops & PC |
| 3 | Laptops | Electronics > Laptops & PC > Laptops |
| 4 | PC | Electronics > Laptops & PC > PC |
| 7 | Phones & Accessories | Electronics > Phones & Accessories |
| 12 | Batteries | Electronics > Phones & Accessories > Batteries |
| 13 | Headsets | Electronics > Phones & Accessories > Headsets |
| 14 | Screen Protectors | Electronics > Phones & Accessories > Screen Protectors |
| 8 | Smartphones | Electronics > Phones & Accessories > Smartphones |
| 9 | Android | Electronics > Phones & Accessories > Smartphones > Android |
| 10 | iOS | Electronics > Phones & Accessories > Smartphones > iOS |
| 11 | Other Smartphones | Electronics > Phones & Accessories > Smartphones > Other Smartphones |
+------+----------------------+----------------------------------------------------------------------+
14 rows in set (0.01 sec)- CTE 查詢指定子樹
查詢id為 7 的 Phone & Accessories 的子樹
WITH RECURSIVE category_path (id, title, path) AS
(
SELECT id, title, title as path
FROM category
WHERE parent_id = 7
UNION ALL
SELECT c.id, c.title, CONCAT(cp.path, ' > ', c.title)
FROM category_path AS cp JOIN category AS c
ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY path;
+------+-------------------+---------------------------------+
| id | title | path |
+------+-------------------+---------------------------------+
| 12 | Batteries | Batteries |
| 13 | Headsets | Headsets |
| 14 | Screen Protectors | Screen Protectors |
| 8 | Smartphones | Smartphones |
| 9 | Android | Smartphones > Android |
| 10 | iOS | Smartphones > iOS |
| 11 | Other Smartphones | Smartphones > Other Smartphones |
+------+-------------------+---------------------------------+
7 rows in set (0.01 sec)- CTE 查詢單個枝葉路徑
從底部 iOS 到 頂部 Electronics 的單個路徑
WITH RECURSIVE category_path (id, title, parent_id) AS
(
SELECT id, title, parent_id
FROM category
WHERE id = 10 -- child node
UNION ALL
SELECT c.id, c.title, c.parent_id
FROM category_path AS cp JOIN category AS c
ON cp.parent_id = c.id
)
SELECT * FROM category_path;
+------+----------------------+-----------+
| id | title | parent_id |
+------+----------------------+-----------+
| 10 | iOS | 8 |
| 8 | Smartphones | 7 |
| 7 | Phones & Accessories | 1 |
| 1 | Electronics | NULL |
+------+----------------------+-----------+
4 rows in set (0.00 sec)- CTE 計算每個節點的層級
根節點為 0，每個子節點等於父節點加 1
WITH RECURSIVE category_path (id, title, lvl) AS
(
SELECT id, title, 0 AS lvl
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.title,cp.lvl + 1
FROM category_path AS cp JOIN category AS c
ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY lvl;
+------+----------------------+------+
| id | title | lvl |
+------+----------------------+------+
| 1 | Electronics | 0 |
| 2 | Laptops & PC | 1 |
| 5 | Cameras & photo | 1 |
| 7 | Phones & Accessories | 1 |
| 4 | PC | 2 |
| 6 | Camera | 2 |
| 8 | Smartphones | 2 |
| 12 | Batteries | 2 |
| 13 | Headsets | 2 |
| 14 | Screen Protectors | 2 |
| 3 | Laptops | 2 |
| 11 | Other Smartphones | 3 |
| 9 | Android | 3 |
| 10 | iOS | 3 |
+------+----------------------+------+
14 rows in set (0.00 sec)- 刪除節點及其子節點
要刪除節點及其子節點，只需刪除節點本身，所有子節點將由 DELETE CASCADE 外來鍵約束自動刪除
例如：要刪除Laptops & PC節點及其子節點
DELETE FROM category WHERE id = 2;-
刪除節點並提升其子節點
- 首先，parent_id將節點的直接子節點更新為id新父節點的子節點。
- 然後，刪除該節點。
例如，要刪除 Smartphones 節點和更新 Android，iOS，Other Smartphones 節點：
兩個語句都應該包含在一個事務中：
BEGIN;
UPDATE category
SET
parent_id = 7 -- Phones & Accessories
WHERE
parent_id = 5; -- Smartphones
DELETE FROM category
WHERE
id = 8;
COMMIT;- 著作權歸作者所有。商業轉載請聯絡作者獲得授權，非商業轉載請註明出處。
- 文章來源https://blog.arunfung.com