Adjacent List Model 與 Nested Set Model 兩種無限分類模型的對比

pilishen發表於2019-02-28

原文連結：www.pilishen.com/posts/Adjac…; 歡迎作客我們的php&Laravel學習群：109256050

此文件是 nestedset-無限分類正確姿勢的擴充套件閱讀

我們都曾在資料庫中處理過層級資料-這種資料中的每項都有一個父項和（0或多個）子項，根項除外。比如：論壇和郵件列表中的分類、商業組織結構表、內容管理系統的分類和產品分類等等。
在關係型資料庫中處理層級資料時我們總會覺得關係型資料庫不是為處理層級資料設計的，因為關係型資料庫的資料表不像XML具有層級，而是一個簡單的扁平化的表。所以層級資料的這種父-子關係不能在資料表中自然的展現出來。

下面我們介紹兩種在關係型資料庫中處理層級資料的模型：

Adjacent List Model 鄰接表模型

我們以下圖的電子產品分類為例

通常上面的產品分類會像下面這樣來設計表結構並被儲存：

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)
複製程式碼

在鄰接表模型中，表中的每條記錄都包含一個指向父項id的欄位。根項也就是這裡的electronics的父項id為null，這種表結構非常的簡單，而且我們很清楚的看到：flash的父項是MP3 , MP3的父項是protable electronics、protable electronics 的父項是electronics等等。雖然在客戶端編碼中鄰接表模型處理起來也相當的簡單，但是如果是純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 會使它下面所有的子項變孤立）。其中一些侷限可以通過使用客戶端編碼或Stored Procedure（儲存過程是一組為了完成特定功能的SQL語句集，經編譯後儲存在資料庫中，使用者通過指定儲存過程的名字並給定引數來呼叫執行它）來解決。使用程式導向的語言，我們可以從樹的底部開始，向上迭代返回完整的樹或一條路徑。我們也可以通過提升子項和對剩下的子項重新排序以使之指向新的父項來避免產生孤立的子樹。

The Nested Set Model 巢狀集合模型

本篇文章將聚焦於巢狀集合模型，在這種模型中我們可以以一種新的方式來看待我們的層級資料，不是以節點和節點之間的線，而是以一種巢狀容器的方式，試著以下面的方式來展示我們的電器分類：

注意我們是怎麼實現層級結構的，父項包含它們所有的子節點。我們通過使用左值和右值來在資料表上表示這種形式的層級結構，以此表示專案的巢狀關係

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來表示左值和右值，然而我們怎麼確定這兩個值呢？我們從外出節點最左邊向右邊編號：

這種設計也可以運用在經典樹上:

檢索整個樹

由於一個節點的左值永遠在其父節點的左值和右值之間，所以我們可以通過自連線來檢索一整個樹：

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語句中加上節點右值的判斷語句，因為右值總是與左值處於相同的父項下。

檢索所有的葉子節點

在巢狀集合模型中檢索葉子節點也比在鄰接表模型中使用左連線方法要簡單得多。如果你仔細觀察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 parent.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 |
+----------------------+-------+
複製程式碼

我們可以使用CONCAT和REPEAT字串函式來根據深度縮排分類名稱：

SELECT CONCAT( REPEAT(` `, COUNT(parent.name) - 1), node.name) AS name
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                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+
複製程式碼

當然，你可以利用深度值直接在客戶端應用程式上展現層級結構。Web開發者可以遍歷樹，並根據深度值的變化增加 <li></li>和<ul></ul> 標籤。

子樹深度

當我們需要子樹的深度資訊時，我們不能在自連線中限制節點或父表，因為它會破壞我們的結果。相反，我們新增了一個第三自連線，以及一個子查詢以確定這個深度將作為子樹的新起點：

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 |
+----------------------+-------+
複製程式碼

這個函式可以被運用到任何節點上，包括根節點。查詢的深度值總是相對應該指定節點。

查詢節點的直接後代

假設你正在一個零售網站上展示電子產品分類。當一個使用者點選一個分類後，你想要顯示那個分類下面的產品並列出它們的子分類，而不是它們下面的完整分類樹，為了達到目的，我們需要顯示它的直接子節點。但是不向下更進一步檢索。例如：我們顯示PORTABLE ELECTRONICS 分類，我們想顯示P3 PLAYERS, CD PLAYERS, 和2 WAY RADIOS，但是不想顯示FLASH。

我們可以在之前的語句上新增一條HABING語句來輕鬆實現：

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。

巢狀集合中的聚合函式

讓我們新增一個產品表來演示聚合函式：

CREATE TABLE product
(
        product_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(40),
        category_id INT NOT NULL
);

INSERT INTO product(name, category_id) VALUES(`20" TV`,3),(`36" TV`,3),
(`Super-LCD 42"`,4),(`Ultra-Plasma 62"`,5),(`Value Plasma 38"`,5),
(`Power-MP3 5gb`,7),(`Super-Player 1gb`,8),(`Porta CD`,9),(`CD To go!`,9),
(`Family Talk 360`,10);

SELECT * FROM product;

+------------+-------------------+-------------+
| product_id | name              | category_id |
+------------+-------------------+-------------+
|          1 | 20" TV            |           3 |
|          2 | 36" TV            |           3 |
|          3 | Super-LCD 42"     |           4 |
|          4 | Ultra-Plasma 62"  |           5 |
|          5 | Value Plasma 38"  |           5 |
|          6 | Power-MP3 128mb   |           7 |
|          7 | Super-Shuffle 1gb |           8 |
|          8 | Porta CD          |           9 |
|          9 | CD To go!         |           9 |
|         10 | Family Talk 360   |          10 |
+------------+-------------------+-------------+
複製程式碼

現在讓我們來寫一套查詢語句來檢索帶有各分類產品數量的分類樹：

SELECT parent.name, COUNT(product.name)
FROM nested_category AS node ,
        nested_category AS parent,
        product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.category_id = product.category_id
GROUP BY parent.name
ORDER BY node.lft;

+----------------------+---------------------+
| name                 | COUNT(product.name) |
+----------------------+---------------------+
| ELECTRONICS          |                  10 |
| TELEVISIONS          |                   5 |
| TUBE                 |                   2 |
| LCD                  |                   1 |
| PLASMA               |                   2 |
| PORTABLE ELECTRONICS |                   5 |
| MP3 PLAYERS          |                   2 |
| FLASH                |                   1 |
| CD PLAYERS           |                   2 |
| 2 WAY RADIOS         |                   1 |
+----------------------+---------------------+
複製程式碼

這是我們典型的整樹查詢語句，包含一個COUNT函式和GROUP BY 函式，以及產品表的引用和在WHERE語句中對node和product表的關聯。就像你看到的，每個分類都計數，子分類的產品數量在父分類上反應出來

新增新的節點

既然我們以及學會了怎麼檢索我們的樹，接下來我們來了解下怎麼新增新的節點到我們的樹中，我們再來看看下面的圖表：

如果我想在TELEVISIONS 和PORTABLE ELECTRONICS 中間新增新的節點，那麼這個新的節點將會給予lft 和 rgt 分別為10和11（我們是從左向右編號的），它右邊所有的節點的lft和rgt值都將加2，這些可以使用MySql儲存過程解決：

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = `TELEVISIONS`;

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES(`GAME CONSOLES`, @myRight + 1, @myRight + 2);

UNLOCK TABLES;
複製程式碼

We can then check our nesting with our indented tree query:

SELECT CONCAT( REPEAT( ` `, (COUNT(parent.name) - 1) ), node.name) AS name
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                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+
複製程式碼

如果我們想給一個沒有子節點的節點新增一個子節點，我們需要稍微改改我們的語句。讓我們在 2 WAY RADIOS節點下面新增新的FRS節點：

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft FROM nested_category

WHERE name = `2 WAY RADIOS`;

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;

INSERT INTO nested_category(name, lft, rgt) VALUES(`FRS`, @myLeft + 1, @myLeft + 2);

UNLOCK TABLES;
複製程式碼

在這個例子中我們將我們新的父節點的左值右邊的值全部擴大。然後將改節點插入到父節點左值的右邊：

SELECT CONCAT( REPEAT( ` `, (COUNT(parent.name) - 1) ), node.name) AS name
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                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+
複製程式碼

刪除節點

最後我們瞭解下移除節點。刪除節點時所採取的操作過程取決於節點在層級結構中的位置；刪除葉節點比刪除帶有子節點的節點要容易得多，因為我們必須處理孤立的節點
刪除葉子節點的過程與新增新的節點正好相反，我們刪除該節點並且刪除父節點中它的寬度：

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = `GAME CONSOLES`;

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;
複製程式碼

我們再次執行我們上面的縮排樹的查詢語句來確認我們刪除了節點且沒有破壞層級結構：



SELECT CONCAT( REPEAT( ` `, (COUNT(parent.name) - 1) ), node.name) AS name
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                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+
複製程式碼

這個方法對刪除節點和它的子節點同樣有效：

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = `MP3 PLAYERS`;

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;
複製程式碼

我們再次查詢以驗證我們成功的刪除了整個子樹：

SELECT CONCAT( REPEAT( ` `, (COUNT(parent.name) - 1) ), node.name) AS name
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                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+
複製程式碼

另一個場景是我們需要刪除父節點，但是需要保留它的子節點。有時你可能會僅僅是將它的名稱改為一個佔位符直到新的名稱來替換它，有時這些子節點需要提升到被刪除的父節點的層級：

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = `PORTABLE ELECTRONICS`;

DELETE FROM nested_category WHERE lft = @myLeft;

UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;

UNLOCK TABLES;
複製程式碼

這裡我們改節點右側的節點的左右值全部減去2，該節點的所有子節點的左右值全部減去1，我們再次來驗證下這些元素有沒有被提升：

SELECT CONCAT( REPEAT( ` `, (COUNT(parent.name) - 1) ), node.name) AS name
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          |
+---------------+
| ELECTRONICS   |
|  TELEVISIONS  |
|   TUBE        |
|   LCD         |
|   PLASMA      |
|  CD PLAYERS   |
|  2 WAY RADIOS |
|   FRS         |
+---------------+
複製程式碼

總結

相對於鄰接表模型，巢狀集合模型在層級資料的查詢中具有極大的優勢，而在資料的插入和刪除操作時則根據插入或刪除資料的位置的不同，可能需要更新很多節點甚至是整個樹的左右值，從而影響資料庫的效能。對於客戶端需要頻繁修改表的程式我們應該避免使用巢狀集合模型，而對於客戶端需要頻繁的查詢表的程式我們應當使用它，像商城的產品分類表，表的資料只是在後臺維護，而大量的使用者會產生大量的查詢。

laravel模型無限分類
2021-07-14
Laravel模型
php 無限分類遞迴和無限分類引入的效能差距
2020-09-25
PHP遞迴
無限極分類類
2019-05-11
從線性模型（linear model）衍生出的機器學習分類器（classifier）
2019-03-24
模型機器學習
無限極分類
2018-05-25
waffle.model改進前、後的程式碼對比
2020-10-07
Awesome-LLM: a curated list of Large Language Model
2024-06-03
兩種方式lu一個vue彈窗元件(v-model與promise方式)
2019-03-04
Vue元件Promise
Model類的一個return作用
2019-05-11
php無限級分類函式（無極限）
2021-09-09
PHP函式
Laravel Model查詢結果的3種儲存格式記憶體佔用對比
2020-09-21
Laravel記憶體
CDM（Conceptual Data Model，概念資料模型）和 PDM（Physical Data Model，物理資料模型）
2024-08-03
模型
machine learning model(algorithm model) .vs. statistical model
2018-08-16
MacGo
PHP中的無限級分類、無限巢狀評論
2019-03-03
PHP巢狀
【模型評估與選擇】sklearn.model_selection.KFold
2018-07-03
模型
關於Select Model的兩篇譯文
2024-04-29
無限分類 - 有限級控制
2020-03-30
Hive 表的兩種分類
2020-11-30
Hive
DOM （文件物件模型(Document Object Model)）
2019-04-03
物件模型Object
盒子模型Box Model簡介
2020-12-08
模型
2022年2月特斯拉Model 3全球各地價格對比
2022-02-08
解說pytorch中的model=model.to(device)
2021-07-11
PyTorchdev
Django model select的各種用法詳解
2018-08-13
Django
Django model update的各種用法介紹
2018-07-25
Django
關於無限極分類設計如何分頁？如何設計出高效能的無限極分類？
2021-03-11
PHP實現無限極分類
2018-12-02
PHP
選單的無限極分類實現
2019-02-16
thinkphp5資料的無限分類
2020-10-06
PHP
Attention Model（注意力模型）思想初探
2018-09-29
模型
EF Core預編譯模型Compiled Model
2023-11-20
編譯模型Compile
ASP.NET Core MVC 之模型（Model）
2019-07-20
ASP.NETMVC模型
儲存載入模型model.save()
2020-12-15
模型
有趣的大模型之我見 | Llama AI Model
2024-04-29
大模型AI
django | 根據 model 建立對應的表
2024-06-25
Django
分層資料 Hierarchical Data 探索 (3.巢狀集合模型) 無限極分類
2019-05-16
巢狀模型
分層資料 Hierarchical Data 探索 (2.鄰接表模型) 無限極分類
2019-05-07
模型
ViewPager兩種方式實現無限輪播
2019-06-19
Viewpager
PHP 無限級分類最佳實踐
2019-02-16
PHP

Adjacent List Model 與 Nested Set Model 兩種無限分類模型的對比

Adjacent List Model 鄰接表模型

檢索整個樹

檢索所有的葉子節點

檢索一條路徑

鄰接表模型的侷限性

The Nested Set Model 巢狀集合模型

檢索整個樹

檢索所有的葉子節點

檢索一條路徑

檢索節點的深度

子樹深度

查詢節點的直接後代

巢狀集合中的聚合函式

新增新的節點

刪除節點

總結

相關文章