萬盛學電腦網

浏覽器 windows 10 wps office 殺毒軟件 數據庫 excel教程 文件管理 word教程 網頁制作 裝機必備軟件 linux教程

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> MySQL嵌套集合模型實現方法及省份城市示例

MySQL嵌套集合模型實現方法及省份城市示例

本教程主要講的是MySQL嵌套集合模型，介紹如何用MySQL來存儲嵌套集合數據。後面帶了一個查詢省份城市的實例。

介紹

什麼是分層數據？



類似於樹形結構，除了根節點和葉子節點外，所有節點都有用一個父節點和多個子節點。

那麼，在MySQL中如何處理分層數據呢？

原文中介紹了兩種分層結構模型：鄰接表模型和嵌套集合模型。
鄰接表模型(The Adjacency 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)


在鄰接表中，所有的數據均擁有一個Parent字段，用來存儲它的父節點。當前節點為根節點的話，它的父節點則為NULL。
那麼在遍歷的時候，可以使用遞歸來實現查詢整棵樹，從根節點開始，不斷尋找子節點（父節點->子節點->父節點->子節點）。


檢索分層路徑

一般需要獲取一個分層結構的路徑問題，那麼

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)


檢索葉子節點

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)


鄰接表的缺點

在檢索路徑的過程中，除了本層外，每一層都會對應一個LEFT JOIN，那麼如果層數不定怎麼辦？或者層數過多？
在刪除中間層的節點時，需要同時刪除該節點下的所有節點，否則會出現孤立節點。


嵌套集合模型Nested Set Model

原文中主要的目的是介紹嵌套集合模型，如下


通過集合的包含關系，嵌套結合模型可以表示分層結構，每一個分層可以用一個Set來表示（一個圈），父節點所在的圈包含所有子節點所在的圈。

為了用MySQL來表示集合關系，需要定義連個字段left和right（表示一個集合的范圍）。

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


由於left和right是MySQL的保留字，因此，字段名稱用lft和rgt代替。每一個集合都是從lft開始到rgt結束，也就是集合的兩個邊界。

在樹中也同樣適用，



當為樹狀結構編號時，我們從左到右，一次一層，賦值按照從左到右的順序遍歷其子節點，這種方法稱為先序遍歷算法。


檢索分層路徑

由於子節點的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         |
+----------------------+


這個方法並不需要考慮層數，而且不需要考慮節點的rgt。


檢索所有葉子節點

由於每一個葉子節點的rgt=lft+1，那麼只需要這一個條件即可。

SELECT name
FROM nested_category
WHERE rgt = lft + 1;
+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+


檢索節點路徑

不再需要多個join連接操作。

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


甚至可以得到分層的縮進結果，

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


檢索子樹的深度

考慮到檢索中需要自連接的node或parent，因此需要增加一個額外的連接來作為子查詢來限制子樹。

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


檢索節點的直接子節點

假設一個場景，當用戶點擊網站上電子產品的一個分類時，將呈現該分類下的產品，同時需要列出所有子分類，並不是全部分類。
為了限制顯示分類的層數，需要使用HAVING字句，

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


增加新節點

上面已經介紹了如何檢索結果，那麼如何才能增加新的節點呢？



如果希望在TELEVISIONS和PROTABLE ELECTRONICS節點之間增加一個新的節點，那麼新節點的lft和rgt的值應該是10和11，那麼所有大於10的節點（新節點右側的節點）的lft和rgt都應該加2，如上圖所示。


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


如果希望在葉子節點下增加節點，需要修改下查詢語句，

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;```


###刪除節點

刪除葉子節點比較容易，只需要刪除自己，而刪除一個中間層節點就需要刪除其所有子節點。在這個模型中，所有子節點的節點正好在lft和rgt之間。

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;


在某些情況下，只需要刪除某個節點，但是並不希望刪除該節點下的子節點數據。
通過把右側所有節點的左右值-2，當前節點的子節點左右值-1

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;
```



Mysql嵌套集合模型【省份城市示例】


父分類包圍了其子分類。在數據表中，我們通過使用表示節點的嵌套關系的左值(left value)和右值(right value)來表現嵌套集合模型


中數據的分層特性。我們使用了lft和rgt來代替left和right，是因為在MySQL中left和right是保留字。


http://dev.mysql.com/doc/mysql/en/reserved-words.html，有一份詳細的MySQL保留字清單。

那麼，我們怎樣決定左值和右值呢？我們從外層節點的最左側開始，從左到右編號：

CREATE TABLE `region` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(30) default NULL,
`parent_id` int(11) default NULL,
`lft` int(10) unsigned default NULL,
`rgt` int(10) unsigned default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (1,'中國',0,1,20);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (2,'北京',1,2,5);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (3,'北京市',2,3,4);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (4,'上海',1,6,9);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (5,'上海市',4,7,8);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (6,'浙江',1,10,19);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (7,'金華市',6,15,16);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (8,'溫州市',6,17,18);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (9,'杭州市',6,11,12);
insert into `region`(`id`,`name`,`parent_id`,`lft`,`rgt`) values (10,'寧波市',6,13,14);


查詢全部節點分成展示：


SELECT CONCAT(REPEAT(' ', COUNT(parent.id)-1), node.name) AS name, node.id,node.lft,node.rgt, COUNT(parent.id)
FROM region AS node, region AS parent
where node.lft BETWEEN parent.lft AND parent.rgt
group by node.id
ORDER BY node.lft;


Mysql嵌套集合模型【省份城市示例】

查詢節點路徑：
select parent.name, parent.id from region as node, region as parent
where node.lft BETWEEN parent.lft and parent.rgt and node.name='金華市';

參考

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

mysql教程排行

程序編程推薦

熱門文章

 [媒體工具]Mac Safar一鍵設置網頁靜音
 [平面設計理論]關於設計趨勢的思考
 [iPhone]iPhone6和小米4哪個好
 [windows 7教程]Win7桌面小工具不能正常顯示或無法…
 [電腦基本常識]如何正確使用鼠標
 [3DSMAX教程]3dmax 中的VR插件的幀緩沖區有…

相關文章

圖片文章

CSS Wave濾鏡用法示例

 　　CSS Wave濾鏡可能很多朋友並不喜歡用，其實用的好了，可以有不少奇異效果。CSS Wave…

五個萬能口訣幫你搞定簡約風格網頁設計！

 　　大標題!大背景!要留白!用純色!越薄越好!今天騰訊游戲的莫同學總結了簡約風格設計的五個關鍵要素，…

Web頁面中的“門”—Web端登錄頁的設計

登錄頁是用戶進入網站的入口，就像是我們進家、進公司的“門”一樣。　　試想我們處在一個四周…

css3實現閃亮進度條效果

 style.css：.holder{ position:relative; margin-botto…

萬盛學電腦網 | 設為首頁 | 加入收藏

copyright © 萬盛學電腦網 all rights reserved