資訊專欄INFORMATION COLUMN
摘要：相反，我們添加了一個第三自連接，以及一個子查詢以確定這個深度將作為子樹的新起點這個函數(shù)可以被運用到任何節(jié)點上，包括根節(jié)點。我們可以在之前的語句上添加一條語句來輕松實現(xiàn)如果想顯示父節(jié)點，將改為。
原文鏈接：http://www.pilishen.com/posts...
我們都曾在數(shù)據(jù)庫中處理過層級數(shù)據(jù)-這種數(shù)據(jù)中的每項都有一個父項和（0或多個）子項，根項除外。比如：論壇和郵件列表中的分類、商業(yè)組織結(jié)構(gòu)表、內(nèi)容管理系統(tǒng)的分類和產(chǎn)品分類等等。
在關(guān)系型數(shù)據(jù)庫中處理層級數(shù)據(jù)時我們總會覺得關(guān)系型數(shù)據(jù)庫不是為處理層級數(shù)據(jù)設(shè)計的，因為關(guān)系型數(shù)據(jù)庫的數(shù)據(jù)表不像XML具有層級，而是一個簡單的扁平化的表。所以層級數(shù)據(jù)的這種父-子關(guān)系不能在數(shù)據(jù)表中自然的展現(xiàn)出來。
下面我們介紹兩種在關(guān)系型數(shù)據(jù)庫中處理層級數(shù)據(jù)的模型：
Adjacent List Model 鄰接表模型我們以下圖的電子產(chǎn)品分類為例
通常上面的產(chǎn)品分類會像下面這樣來設(shè)計表結(jié)構(gòu)并被儲存：
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，這種表結(jié)構(gòu)非常的簡單，而且我們很清楚的看到：flash的父項是MP3 , MP3的父項是protable electronics、protable electronics 的父項是electronics等等。雖然在客戶端編碼中鄰接表模型處理起來也相當(dāng)?shù)暮唵?，但是如果是純SQL編碼的話，該模型會有很多問題。
檢索整個樹檢索整個樹是處理層級數(shù)據(jù)最常見的任務(wù)，為了完成這個最常用的方法是通過自連接（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)檢索所有的葉子節(jié)點
我們可以通過左連接（left-join）檢索出所有的葉子節(jié)點（沒有子節(jié)點的節(jié)點）：
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 | +--------------+檢索一條路徑
自連接同樣可以讓我檢索出層級結(jié)構(gòu)中的一條完整的層級路徑
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)
這個方法的主要局限性在于對于層級結(jié)構(gòu)中的每一層都要自連接，并且隨著層級的增加這種自連接將變的越來越復(fù)雜也就自然的影響著性能。
鄰接表模型的局限性在純SQL中使用鄰接表模型會很困難，如果想檢索某個分類在層級結(jié)構(gòu)中的路徑就需要事先知道它在層級結(jié)構(gòu)中所處的層級，此外當(dāng)進行刪除某項操作時需要非常小心，因為在刪除的過程中可能會導(dǎo)致出現(xiàn)整個的孤立的子樹（刪除portable electronics 會使它下面所有的子項變孤立）。其中一些局限可以通過使用客戶端編碼或Stored Procedure（存儲過程是一組為了完成特定功能的SQL語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中，用戶通過指定存儲過程的名字并給定參數(shù)來調(diào)用執(zhí)行它）來解決。使用面向過程的語言，我們可以從樹的底部開始，向上迭代返回完整的樹或一條路徑。我們也可以通過提升子項和對剩下的子項重新排序以使之指向新的父項來避免產(chǎn)生孤立的子樹。
The Nested Set Model 嵌套集合模型本篇文章將聚焦于嵌套集合模型，在這種模型中我們可以以一種新的方式來看待我們的層級數(shù)據(jù)，不是以節(jié)點和節(jié)點之間的線，而是以一種嵌套容器的方式，試著以下面的方式來展示我們的電器分類：
注意我們是怎么實現(xiàn)層級結(jié)構(gòu)的，父項包含它們所有的子節(jié)點。我們通過使用左值和右值來在數(shù)據(jù)表上表示這種形式的層級結(jié)構(gòu)，以此表示項目的嵌套關(guān)系
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來表示左值和右值，然而我們怎么確定這兩個值呢？我們從外出節(jié)點最左邊向右邊編號：
這種設(shè)計也可以運用在經(jīng)典樹上:
由于一個節(jié)點的左值永遠在其父節(jié)點的左值和右值之間，所以我們可以通過自連接來檢索一整個樹：
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語句中加上節(jié)點右值的判斷語句，因為右值總是與左值處于相同的父項下。
檢索所有的葉子節(jié)點在嵌套集合模型中檢索葉子節(jié)點也比在鄰接表模型中使用左連接方法要簡單得多。如果你仔細觀察nested_category表，你會發(fā)現(xiàn)所有的葉子節(jié)點的左值與右值是兩個連續(xù)的數(shù)字，所有為了檢索葉子節(jié)點，我們只需要檢索那些rgt = lft +1 的節(jié)點就行了:
SELECT name FROM nested_category WHERE rgt = lft + 1; +--------------+ | name | +--------------+ | TUBE | | LCD | | PLASMA | | FLASH | | CD PLAYERS | | 2 WAY RADIOS | +--------------+檢索一條路徑
使用嵌套集合模型，我們可以檢索一條路徑而不需要之前那樣復(fù)雜的多次自連接：
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 |
+----------------------+
檢索節(jié)點的深度
我們已經(jīng)了解了怎么檢索整個樹，但是如果我們還需要知道樹中的每個節(jié)點的深度，以便更好的確定層級結(jié)構(gòu)中的各個節(jié)點到底處于什么位置怎么辦呢？ 我們可以通過增加COUNT函數(shù)和一條GROUP BY 語句到我們已存在的查詢語句中來實現(xiàn)顯示整個樹：
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字符串函數(shù)來根據(jù)深度縮進分類名稱：
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 |
+-----------------------+
當(dāng)然，你可以利用深度值直接在客戶端應(yīng)用程序上展現(xiàn)層級結(jié)構(gòu)。Web開發(fā)者可以遍歷樹，并根據(jù)深度值的變化增加 和 標(biāo)簽。
當(dāng)我們需要子樹的深度信息時，我們不能在自連接中限制節(jié)點或父表，因為它會破壞我們的結(jié)果。相反，我們添加了一個第三自連接，以及一個子查詢以確定這個深度將作為子樹的新起點：
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 |
+----------------------+-------+
這個函數(shù)可以被運用到任何節(jié)點上，包括根節(jié)點。查詢的深度值總是相對應(yīng)該指定節(jié)點。
查找節(jié)點的直接后代假設(shè)你正在一個零售網(wǎng)站上展示電子產(chǎn)品分類。當(dāng)一個用戶點擊一個分類后，你想要顯示那個分類下面的產(chǎn)品并列出它們的子分類，而不是它們下面的完整分類樹，為了達到目的，我們需要顯示它的直接子節(jié)點。但是不向下更進一步檢索。例如：我們顯示PORTABLE ELECTRONICS 分類，我們想顯示P3 PLAYERS, CD PLAYERS, 和2 WAY RADIOS，但是不想顯示FLASH。
我們可以在之前的語句上添加一條HABING語句來輕松實現(xiàn)：
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 |
+----------------------+-------+
如果想顯示父節(jié)點，將HAVING depth <= 1改為HAVING depth = 1。
嵌套集合中的聚合函數(shù)讓我們添加一個產(chǎn)品表來演示聚合函數(shù)：
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 |
+------------+-------------------+-------------+
現(xiàn)在讓我們來寫一套查詢語句來檢索帶有各分類產(chǎn)品數(shù)量的分類樹：
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函數(shù)和GROUP BY 函數(shù)，以及產(chǎn)品表的引用和在WHERE語句中對node和product表的關(guān)聯(lián)。就像你看到的，每個分類都計數(shù)，子分類的產(chǎn)品數(shù)量在父分類上反應(yīng)出來
添加新的節(jié)點既然我們以及學(xué)會了怎么檢索我們的樹，接下來我們來了解下怎么添加新的節(jié)點到我們的樹中，我們再來看看下面的圖表：
如果我想在TELEVISIONS 和PORTABLE ELECTRONICS 中間添加新的節(jié)點，那么這個新的節(jié)點將會給予lft 和 rgt 分別為10和11（我們是從左向右編號的），它右邊所有的節(jié)點的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 |
+-----------------------+
如果我們想給一個沒有子節(jié)點的節(jié)點添加一個子節(jié)點，我們需要稍微改改我們的語句。讓我們在 2 WAY RADIOS節(jié)點下面添加新的FRS節(jié)點：
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;
在這個例子中我們將我們新的父節(jié)點的左值右邊的值全部擴大。然后將改節(jié)點插入到父節(jié)點左值的右邊：
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 |
+-----------------------+
刪除節(jié)點
最后我們了解下移除節(jié)點。刪除節(jié)點時所采取的操作過程取決于節(jié)點在層級結(jié)構(gòu)中的位置；刪除葉節(jié)點比刪除帶有子節(jié)點的節(jié)點要容易得多，因為我們必須處理孤立的節(jié)點
刪除葉子節(jié)點的過程與添加新的節(jié)點正好相反，我們刪除該節(jié)點并且刪除父節(jié)點中它的寬度：
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;
我們再次執(zhí)行我們上面的縮進樹的查詢語句來確認(rèn)我們刪除了節(jié)點且沒有破壞層級結(jié)構(gòu)：
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 |
+-----------------------+
這個方法對刪除節(jié)點和它的子節(jié)點同樣有效：
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 |
+-----------------------+
另一個場景是我們需要刪除父節(jié)點，但是需要保留它的子節(jié)點。有時你可能會僅僅是將它的名稱改為一個占位符直到新的名稱來替換它，有時這些子節(jié)點需要提升到被刪除的父節(jié)點的層級：
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;
這里我們改節(jié)點右側(cè)的節(jié)點的左右值全部減去2，該節(jié)點的所有子節(jié)點的左右值全部減去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 |
+---------------+
總結(jié)
相對于鄰接表模型，嵌套集合模型在層級數(shù)據(jù)的查詢中具有極大的優(yōu)勢，而在數(shù)據(jù)的插入和刪除操作時則根據(jù)插入或刪除數(shù)據(jù)的位置的不同，可能需要更新很多節(jié)點甚至是整個樹的左右值，從而影響數(shù)據(jù)庫的性能。對于客戶端需要頻繁修改表的程序我們應(yīng)該避免使用嵌套集合模型，而對于客戶端需要頻繁的查詢表的程序我們應(yīng)當(dāng)使用它，像商城的產(chǎn)品分類表，表的數(shù)據(jù)只是在后臺維護，而大量的用戶會產(chǎn)生大量的查詢。
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請注明本文地址：http://systransis.cn/yun/28170.html
摘要：原文鏈接歡迎作客我們的學(xué)習(xí)群本文翻譯自維基百科嵌套集合模型是一種在關(guān)系型數(shù)據(jù)庫中表示嵌套集合的特殊技術(shù)。誘因該技術(shù)的出現(xiàn)解決了標(biāo)準(zhǔn)關(guān)系代數(shù)和關(guān)系演算以及基于它們的操作不能直接在層次結(jié)構(gòu)上表示所有期望操作的問題。這通常被稱為物料清單問題。 原文鏈接：http://www.pilishen.com/posts...; 歡迎作客我們的php&Laravel學(xué)習(xí)群：109256050本文翻譯自...
摘要：眾數(shù)周知，文檔的編寫和整理工作將花費巨大精力甚至不亞于代碼的編寫，因此在時間緊任務(wù)重的情況下，文檔是首先被忽略的工作。是一款非常流行的文檔管理交互工具，適用于在團隊中的管理，以及服務(wù)組件對接。而我們目前需要的是獲取文檔或文件。 本文最先發(fā)布在博客：https://blog.ihypo.net/152551... Flask 是一個以自由度高、靈活性強著稱的 Python Web 框架...
摘要：通過自定義的查詢加載和大多數(shù)情況下，你需要按層級排序祖先集合可以被預(yù)加載視圖模板中面包屑將祖先的全部取出后轉(zhuǎn)換為數(shù)組，在用拼接為字符串輸出。 原文鏈接：http://www.pilishen.com/posts...; 歡迎作客我們的php&Laravel學(xué)習(xí)群：109256050 laravel-nestedset是一個關(guān)系型數(shù)據(jù)庫遍歷樹的larvel4-5的插件包 目錄： Nes...
摘要：本文源地址，轉(zhuǎn)發(fā)請注明該地址或地址，謝謝微信公眾號發(fā)布的文章和一般門戶網(wǎng)站的新聞文本類型有所不同，通常不能用現(xiàn)有的文本分類器直接對這些文章進行分類，不過文本分類的原理是相通的，本文以微信公眾號文章為對象，介紹樸素貝葉斯分類器的實現(xiàn)過程。 本文源地址：http://www.fullstackyang.com/...，轉(zhuǎn)發(fā)請注明該地址或segmentfault地址，謝謝！ 微信公眾號發(fā)布的...
閱讀 1850·2023-04-26 00:59
閱讀 3142·2021-11-15 18:10
閱讀 3088·2021-09-22 16:02
閱讀 772·2021-09-02 15:15
閱讀 3726·2019-08-30 15:56
閱讀 1925·2019-08-30 15:54
閱讀 2869·2019-08-29 16:31
閱讀 2044·2019-08-29 16:10
