在MySQL中管理分層資料---鄰接表模型和巢狀集模型

hickey發表於2022-02-26
MySql模型巢狀

背景

在我們的日常開發中，我們一定會接觸到的一種資料就是分層資料。哪些是分層資料呢？業務組織結構圖，內容管理類別，RBAC許可權管理，產品類別等等，這些都是分層資料，以下是一個電子商店的產品類別層次結構：

categories

在本文中，我們將研究在 MySQL 中處理分層資料的兩種模型，從傳統的鄰接表模型開始。

鄰接表模型

通常，上面顯示的示例類別將儲存在如下表中（我把 CREATE 和 INSERT 語句都寫下來，你可以跟著執行）：

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)

在鄰接表模型中，表中的每一項都包含一個指向其父項的指標。最上面的元素，在本例中是 ELECTRONICS ，其父元素是 NULL 值。鄰接表模型的優點是比較簡單，很容易看出 FLASH 是 MP3 PLAYERS 的子級，PORTABLE ELECTRONICS 的子級，ELECTRONICS 的子級。缺點也很明顯，我們要查某個節點的所有上級或者是所有下級都是要遞迴的去查詢的。如果某個業務場景下，分層的層數增加到很多並且葉子節點也在增多，那我們的查詢就會變的很慢。這種我們也是可以最佳化的，就是將每個葉子節點之間的路徑儲存下來。但是這種方法會增加資料的儲存量。那麼有什麼辦法是可以的將我們的整個數儲存在關係型資料庫中的呢。\

巢狀集模型

在巢狀集模型中，我們可以以一種新的方式看待我們的層次結構，而不是作為節點和線，而是作為巢狀容器嘗試以這種方式描繪我們的電子產品類別：
nested_categories

請注意我們的層次結構仍然維持著的的，因為父類別包圍了它們的子類別。我們透過使用左右值來表示節點的巢狀，在表中表示這種形式的層次結構：

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 |
+-------------+----------------------+-----+-----+

那麼我們如何確定左右值呢？我們從外節點最左邊開始編號，繼續向右:

nested_numbered

這種設計也可以應用於典型的樹：

numbered_tree

使用樹時，我們從左到右，一次一層，在分配右手數字並向右移動之前下降到每個節點的子節點。這種方法稱為前序樹遍歷演算法。

檢索完整的樹

我們可以透過使用自連線來檢索完整樹，該自連線將父節點與節點連線起來，基於節點的 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 子句中節點的 rgt 值，因為 rgt 值將始終與 lft 值在同一個父節點內。

參考

mikehillyer.com/articles/managing-h...

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

相關文章