# 分层数据 Hierarchical Data 探索 (3.嵌套集合模型) 无限极分类

arunfung

## 分层数据Hierarchical Data探索（例如：无限级分类、多级菜单、省份城市）

### 嵌套集合模型(Nested Set Model)

``````# 为了模拟，我们创建一个表category包含三个字段：id，title，lft，rgt如下：
CREATE TABLE category (
id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
title varchar(255) NOT NULL,
lft int(10) NOT NULL,
rgt int(10) NOT NULL
);

# 插入模拟数据
INSERT INTO category(title,lft,rgt) VALUES('Electronics',1,28);

INSERT INTO category(title,lft,rgt) VALUES('Laptops & PC',2,7);

INSERT INTO category(title,lft,rgt) VALUES('Laptops',3,4);
INSERT INTO category(title,lft,rgt) VALUES('PC',5,6);

INSERT INTO category(title,lft,rgt) VALUES('Cameras & photo',8,11);
INSERT INTO category(title,lft,rgt) VALUES('Camera',9,10);

INSERT INTO category(title,lft,rgt) VALUES('Phones & Accessories',12,27);
INSERT INTO category(title,lft,rgt) VALUES('Smartphones',13,20);

INSERT INTO category(title,lft,rgt) VALUES('Android',14,15);
INSERT INTO category(title,lft,rgt) VALUES('iOS',16,17);
INSERT INTO category(title,lft,rgt) VALUES('Other Smartphones',18,19);

INSERT INTO category(title,lft,rgt) VALUES('Batteries',21,22);
INSERT INTO category(title,lft,rgt) VALUES('Screen Protectors',25,26);

select * from category;
+----+----------------------+-----+-----+
| id | title                | lft | rgt |
+----+----------------------+-----+-----+
|  1 | Electronics          |   1 |  28 |
|  2 | Laptops & PC         |   2 |   7 |
|  3 | Laptops              |   3 |   4 |
|  4 | PC                   |   5 |   6 |
|  5 | Cameras & photo      |   8 |  11 |
|  6 | Camera               |   9 |  10 |
|  7 | Phones & Accessories |  12 |  27 |
|  8 | Smartphones          |  13 |  20 |
|  9 | Android              |  14 |  15 |
| 10 | iOS                  |  16 |  17 |
| 11 | Other Smartphones    |  18 |  19 |
| 12 | Batteries            |  21 |  22 |
| 13 | Headsets             |  23 |  24 |
| 14 | Screen Protectors    |  25 |  26 |
+----+----------------------+-----+-----+
14 rows in set (0.00 sec)``````
• 检索分层路径

``````SELECT node.id,node.title,node.lft,node.rgt
FROM category AS node,
category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.title = 'Electronics'
ORDER BY node.lft;
+----+----------------------+-----+-----+
| id | title                | lft | rgt |
+----+----------------------+-----+-----+
|  1 | Electronics          |   1 |  28 |
|  2 | Laptops & PC         |   2 |   7 |
|  3 | Laptops              |   3 |   4 |
|  4 | PC                   |   5 |   6 |
|  5 | Cameras & photo      |   8 |  11 |
|  6 | Camera               |   9 |  10 |
|  7 | Phones & Accessories |  12 |  27 |
|  8 | Smartphones          |  13 |  20 |
|  9 | Android              |  14 |  15 |
| 10 | iOS                  |  16 |  17 |
| 11 | Other Smartphones    |  18 |  19 |
| 12 | Batteries            |  21 |  22 |
| 13 | Headsets             |  23 |  24 |
| 14 | Screen Protectors    |  25 |  26 |
+----+----------------------+-----+-----+
14 rows in set (0.05 sec)``````

• 检索所有叶子节点

``````SELECT id,title,lft,rgt
FROM category
WHERE rgt = lft + 1;
+----+-------------------+-----+-----+
| id | title             | lft | rgt |
+----+-------------------+-----+-----+
|  3 | Laptops           |   3 |   4 |
|  4 | PC                |   5 |   6 |
|  6 | Camera            |   9 |  10 |
|  9 | Android           |  14 |  15 |
| 10 | iOS               |  16 |  17 |
| 11 | Other Smartphones |  18 |  19 |
| 12 | Batteries         |  21 |  22 |
| 13 | Headsets          |  23 |  24 |
| 14 | Screen Protectors |  25 |  26 |
+----+-------------------+-----+-----+
9 rows in set (0.00 sec)``````

### 查询

• 检索单一路径

``````SELECT parent.id,parent.title,parent.lft,parent.rgt
FROM category AS node,
category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.title = 'PC'
ORDER BY parent.lft;

+----+--------------+-----+-----+
| id | title        | lft | rgt |
+----+--------------+-----+-----+
|  1 | Electronics  |   1 |  28 |
|  2 | Laptops & PC |   2 |   7 |
|  4 | PC           |   5 |   6 |
+----+--------------+-----+-----+
3 rows in set (0.00 sec)``````
• 检索节点的深度

``````SELECT node.title,(COUNT(parent.title) - 1) AS lev
FROM category AS node,
category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.title
ORDER BY node.lft;

+----------------------+-----+
| title                | lev |
+----------------------+-----+
| Electronics          |   0 |
| Laptops & PC         |   1 |
| Laptops              |   2 |
| PC                   |   2 |
| Cameras & photo      |   1 |
| Camera               |   2 |
| Phones & Accessories |   1 |
| Smartphones          |   2 |
| Android              |   3 |
| iOS                  |   3 |
| Other Smartphones    |   3 |
| Batteries            |   2 |
| Screen Protectors    |   2 |
+----------------------+-----+
14 rows in set (0.01 sec)``````

``````报错：
ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'test.node.lft' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

This supposed you need to make that GROUP BY with non aggregated columns.``````

``````SELECT CONCAT( REPEAT(' ', COUNT(parent.title) - 1), node.title) AS name,(COUNT(parent.title) - 1) AS lev
FROM category AS node,
category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.title
ORDER BY node.lft;
+-----------------------+-----+
| name                  | lev |
+-----------------------+-----+
| Electronics           |   0 |
|  Laptops & PC         |   1 |
|   Laptops             |   2 |
|   PC                  |   2 |
|  Cameras & photo      |   1 |
|   Camera              |   2 |
|  Phones & Accessories |   1 |
|   Smartphones         |   2 |
|    Android            |   3 |
|    iOS                |   3 |
|    Other Smartphones  |   3 |
|   Batteries           |   2 |
|   Screen Protectors   |   2 |
+-----------------------+-----+
14 rows in set (0.01 sec)``````
• 检索子树的深度
``````SELECT node.title, (COUNT(parent.title) - (sub_tree.lev + 1)) AS lev
FROM category AS node,
category AS parent,
category AS sub_parent,
(
SELECT node.title, (COUNT(parent.title) - 1) AS lev
FROM category AS node,
category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.title = 'Phones & Accessories'
GROUP BY node.title
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.title = sub_tree.title
GROUP BY node.title
ORDER BY node.lft;``````

• 检索节点的直接子节点

``````SELECT node.title, (COUNT(parent.title) - (sub_tree.lev + 1)) AS lev
FROM category AS node,
category AS parent,
category AS sub_parent,
(
SELECT node.title, (COUNT(parent.title) - 1) AS lev
FROM category AS node,
category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.title = 'Phones & Accessories'
GROUP BY node.title
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.title = sub_tree.title
GROUP BY node.title
HAVING lev <= 1
ORDER BY node.lft;``````

### 新增节点

• 添加同一层次的节点

``````LOCK TABLE category WRITE;

SELECT @myRight := rgt FROM category WHERE title = 'Laptops & PC';

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

INSERT INTO category(title, lft, rgt) VALUES('Game Consoles', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

SELECT CONCAT( REPEAT(' ', COUNT(parent.title) - 1), node.title) AS name,(COUNT(parent.title) - 1) AS lev
FROM category AS node,
category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.title
ORDER BY node.lft;

+-----------------------+-----+
| name                  | lev |
+-----------------------+-----+
| Electronics           |   0 |
|  Laptops & PC         |   1 |
|   Laptops             |   2 |
|   PC                  |   2 |
|  Game Consoles        |   1 |
|  Cameras & photo      |   1 |
|   Camera              |   2 |
|  Phones & Accessories |   1 |
|   Smartphones         |   2 |
|    Android            |   3 |
|    iOS                |   3 |
|    Other Smartphones  |   3 |
|   Batteries           |   2 |
|   Screen Protectors   |   2 |
+-----------------------+-----+
15 rows in set (0.00 sec)``````
• 添加叶子节点

``````LOCK TABLE category WRITE;

SELECT @myLeft := lft FROM category WHERE title = 'Camera';

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

INSERT INTO category(title, lft, rgt) VALUES('SLR', @myLeft + 1, @myLeft + 2);

UNLOCK TABLES;``````

### 删除节点

• 删除叶子节点

``````LOCK TABLE category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 FROM category WHERE title = 'Game Consoles';

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

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

UNLOCK TABLES;``````
• 删除子节点以及整颗子树
``````LOCK TABLE category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 FROM category WHERE title = 'Cameras & photo';

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

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

UNLOCK TABLES;``````
• 删除该节点，而不删除该节点的子节点
``````LOCK TABLE category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 FROM category WHERE title = 'Cameras & photo';

DELETE FROM category WHERE lft = @myLeft;

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

UNLOCK TABLES;``````

22 声望
0 粉丝