一.概念
- 左右值無限級分類，也稱為預(yù)排序樹無限級分類
- 是一種有序的樹狀結(jié)構(gòu)
- 于這些樹狀結(jié)構(gòu)中的每一個節(jié)點都有一個
左值 和 右值
二.規(guī)則
- 每一個
后代節(jié)點 的 左值 > 父節(jié)點 的 左值
- 每一個
后代節(jié)點 的 右值 < 父節(jié)點的 右值
- 每一個節(jié)點的
右值 < 左值
三.新增節(jié)點
四. 刪除節(jié)點
- 獲取刪除節(jié)點的左右值
$lft, $rgt
- 刪除該節(jié)點以及所有后代節(jié)點
DELETE FROM `catagory` WHERE `lft`>=$lft AND `rgt`<=$Rgt"
$Value=$rgt-$lft+1;
UPDATE `catagory` SET `lft`=`lft`- $Value WHERE `lft`>$lft
UPDATE `catagory` SET `rgt`=`rgt`- $Value WHERE `rgt`>$rgt"
五.數(shù)據(jù)準備
CREATE TABLE `nested_category` (
`category_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`name` varchar(18) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '名稱',
`lft` int(4) NOT NULL,
`rgt` int(4) NOT NULL,
KEY `category_id` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `nested_category` VALUES
(1,'商品',1,26),
(2,'化妝品',2,7),
(3,'食品',8,9),
(4,'酒',10,15),
(5,'服裝',16,17),
(6,'家電',18,23),
(7,'鞋帽',24,25),
(8,'面霜',3,4),
(9,'面膜',5,6),
(10,'白酒',11,12),
(11,'紅酒',13,14),
(12,'冰箱',19,20),
(13,'空調(diào)',21,22);
數(shù)據(jù)查看
mysql> select * from nested_category;
+-------------+-----------+-----+-----+
| category_id | name | lft | rgt |
+-------------+-----------+-----+-----+
| 1 | 商品 | 1 | 26 |
| 2 | 化妝品 | 2 | 7 |
| 3 | 食品 | 8 | 9 |
| 4 | 酒 | 10 | 15 |
| 5 | 服裝 | 16 | 17 |
| 6 | 家電 | 18 | 23 |
| 7 | 鞋帽 | 24 | 25 |
| 8 | 面霜 | 3 | 4 |
| 9 | 面膜 | 5 | 6 |
| 10 | 白酒 | 11 | 12 |
| 11 | 紅酒 | 13 | 14 |
| 12 | 冰箱 | 19 | 20 |
| 13 | 空調(diào) | 21 | 22 |
+-------------+-----------+-----+-----+
六. 獲取所有后代節(jié)點
select * from nested_category where lft > 18 and rgt < 23;
+-------------+--------+-----+-----+
| category_id | name | lft | rgt |
+-------------+--------+-----+-----+
| 12 | 冰箱 | 19 | 20 |
| 13 | 空調(diào) | 21 | 22 |
+-------------+--------+-----+-----+
2 rows in set (0.00 sec)
七.計算后代數(shù)量
- 后代的數(shù)量 = (右值 - 左值 - 1) / 2。
減少1的原因是排除該節(jié)點本身
八. 判斷葉子節(jié)點
mysql> select * from nested_category where rgt - lft = 1;
+-------------+--------+-----+-----+
| category_id | name | lft | rgt |
+-------------+--------+-----+-----+
| 3 | 食品 | 8 | 9 |
| 5 | 服裝 | 16 | 17 |
| 7 | 鞋帽 | 24 | 25 |
| 8 | 面霜 | 3 | 4 |
| 9 | 面膜 | 5 | 6 |
| 10 | 白酒 | 11 | 12 |
| 11 | 紅酒 | 13 | 14 |
| 12 | 冰箱 | 19 | 20 |
| 13 | 空調(diào) | 21 | 22 |
+-------------+--------+-----+-----+
九. 檢索單一路徑
select
parent.name,
parent.category_id,
parent.lft,
parent.rgt
from
nested_category as node, nested_category as parent
where
node.lft between parent.lft and parent.rgt and node.name = '空調(diào)'
order by parent.lft;
+--------+-------------+-----+-----+
| name | category_id | lft | rgt |
+--------+-------------+-----+-----+
| 商品 | 1 | 1 | 26 |
| 家電 | 6 | 18 | 23 |
| 空調(diào) | 13 | 21 | 22 |
+--------+-------------+-----+-----+
3 rows in set (0.00 sec)
十. 檢索分類深度
select
node.name as name, (count(parent.name) - 1) as deep
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 | deep |
+-----------+------+
| 商品 | 0 |
| 化妝品 | 1 |
| 面霜 | 2 |
| 面膜 | 2 |
| 食品 | 1 |
| 酒 | 1 |
| 白酒 | 2 |
| 紅酒 | 2 |
| 服裝 | 1 |
| 家電 | 1 |
| 冰箱 | 2 |
| 空調(diào) | 2 |
| 鞋帽 | 1 |
+-----------+------+
13 rows in set (0.03 sec)
十一. 檢索某個節(jié)點的子節(jié)點(不包含后代節(jié)點)
select * from (
select
node.name as name,
(count(parent.name) - 1) as deep
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
) as a where a.deep <= 1;
+-----------+------+
| name | deep |
+-----------+------+
| 商品 | 0 |
| 化妝品 | 1 |
| 食品 | 1 |
| 酒 | 1 |
| 服裝 | 1 |
| 家電 | 1 |
| 鞋帽 | 1 |
+-----------+------+
7 rows in set (0.00 sec)
十二.總結(jié)
- 我們看到上邊的
獲取深度 和 檢索某個節(jié)點的子節(jié)點實現(xiàn)上用到了子查詢,sql語句很復雜.
- 所以我的解決辦法是在數(shù)據(jù)結(jié)構(gòu)上增加
深度 和 父id 兩個字段
- 因為分類是前臺頁面操作人員操作的, 也就是說操作的時候就知道深度, 每次新增時候?qū)?
深度 和 父id 帶上就可以方便的解決復雜sql語句的問題;
參考文章: http://blog.csdn.net/i_bruce/article/details/41558063
