分層資料 Hierarchical Data 探索 (2.鄰接表模型) 無限極分類

arunfung發表於2019-05-07

分層資料Hierarchical Data探索（例如：無限級分類、多級選單、省份城市）

引言

第一篇分層資料Hierarchical Data探索(1.遞迴)已經介紹了分層資料以及使用遞迴演算法實現了無限極分類，但是遞迴即浪費時間，又浪費空間（記憶體）,尤其是在資料量大的情況下效率顯著下降。
那麼，在MySQL中如何處理分層資料呢？下面我們來說一說資料模型鄰接表模型

鄰接表模型(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;

刪除節點並提升其子節點
1. 首先，parent_id將節點的直接子節點更新為id新父節點的子節點。
2. 然後，刪除該節點。

例如，要刪除 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;

參考資源

連結：http://mikehillyer.com/articles/managing-h...
著作權歸作者所有。商業轉載請聯絡作者獲得授權，非商業轉載請註明出處。

本作品採用《CC 協議》，轉載必須註明作者和本文連結

著作權歸作者所有。商業轉載請聯絡作者獲得授權，非商業轉載請註明出處。
文章來源https://blog.arunfung.com

分層資料 Hierarchical Data 探索 (2.鄰接表模型)
2019-05-07
模型
分層資料 Hierarchical Data 探索 (3.巢狀集合模型) 無限極分類
2019-05-16
巢狀模型
分層資料 Hierarchical Data 探索 (1.遞迴) 無限極分類
2019-05-06
遞迴
在MySQL中管理分層資料---鄰接表模型和巢狀集模型
2022-02-26
MySql模型巢狀
laravel模型無限分類
2021-07-14
Laravel模型
php無限級分類函式（無極限）
2021-09-09
PHP函式
PHP實現無限極分類
2018-12-02
PHP
Laravel 框架實現無限極分類
2020-06-08
Laravel框架
選單的無限極分類實現
2019-02-16
不用遞推實現無限極分類
2021-04-14
thinkphp5資料的無限分類
2020-10-06
PHP
分享一個無限極分類的函式
2020-04-11
函式
php 無限分類遞迴和無限分類引入的效能差距
2020-09-25
PHP遞迴
PHP 快速掃描列表建立無限極分類樹
2019-06-20
PHP
MVC無限級分類01,分層架構,引入快取，完成領域模型與檢視模型的對映
2014-04-19
MVC架構快取模型
哪位大師能用匿名遞迴下這個無限極分類？
2020-07-28
遞迴
FreeSql 使用 ToTreeList/AsTreeCte 查詢無限級分類表
2020-06-28
SQLAST
好大夫資料安全分類分級實踐探索
2022-11-28
鄰接表
2024-04-12
PHP中的無限級分類、無限巢狀評論
2019-03-03
PHP巢狀
PHP 無限級分類最佳實踐
2019-02-16
PHP
mysql 無限級分類實現思路
2021-09-09
MySql
Javascript無限分類演算法(轉)
2007-08-15
JavaScript演算法
分享一個用 ORM 寫無限極分類的帖子, 大佬請跳過.
2020-04-10
ORM
利用定積分求極限
2024-03-30
高效能無限級分類實現思路
2021-03-27
無限級分類---有關查詢問題??
2004-10-27
Adjacent List Model 與 Nested Set Model 兩種無限分類模型的對比
2019-02-28
模型
資料分類
2007-08-06
分庫分表就能無限擴容嗎，解釋得太好了！
2022-12-05
文字分類模型
2020-10-28
文字分類模型
PHP + MySQL 無限分類實現的2種方法
2015-03-02
PHPMySql
資料治理與資料分類分級！
2023-12-15
POJ 3159-Candies（差分約束系統-SPFA+鄰接表）
2017-04-26
分庫分表插入資料
2020-12-09
資料庫分庫分表
2016-11-04
資料庫
2. STL容器結構與分類
2020-11-24
圖的儲存結構——鄰接矩陣與鄰接表
2017-04-18
矩陣

分層資料 Hierarchical Data 探索 (2.鄰接表模型) 無限極分類

分層資料Hierarchical Data探索（例如：無限級分類、多級選單、省份城市）

引言

鄰接表模型(Adjacency List Model)

參考資源

相關文章