如何在MySQL中使用“嵌套集模型“管理分层数据
大多数用户都曾在SQL数据库中处理过层次数据，毫无疑问，层次数据的管理不是关系数据库的目的。关系数据库的表不是分层的(像XML一样)，而是一个简单的列表。分层数据具有父子关系，这种关系在关系数据库表中没有自然地表示出来。在本文中，我们将研究MySQL中处理层次数据的两种模型.
·
介绍
大多数用户都曾在SQL数据库中处理过层次数据，毫无疑问，层次数据的管理不是关系数据库的目的。关系数据库的表不是分层的(像XML一样)，而是一个简单的列表。分层数据具有父子关系，这种关系在关系数据库表中没有自然地表示出来。
对于我们的目的来说，分层数据是一个数据集合，其中每个项都有一个父项和零个或多个子项(根项除外，它没有父项)。层次数据可以在各种数据库应用程序中找到，包括论坛和邮件列表线程、业务组织图、内容管理类别和产品类别。出于我们的目的，我们将使用一个虚构的电子商店中的以下产品类别层次结构:
这些类别形成层次结构的方式与上面提到的其他例子非常相似。在本文中，我们将研究MySQL中处理层次数据的两种模型，从传统的邻接表模型开始。
1. 邻接表模型(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)
在邻接表模型中，表中的每一项都包含一个指向其父项的指针。最顶层的元素，在本例中是electronics，其父元素的值为空。邻接表模型的优点是相当简单，很容易看出FLASH是MP3 PLAYERS播放器的子，MP3 PLAYERS是PORTABLE ELECTRONICS的子，PORTABLE ELECTRONICS是ELECTRONICS的子。虽然在客户端代码中可以很容易地处理邻接列表模型，但在纯SQL中使用该模型可能会有更大的问题。
检索全树
处理层次结构数据时，第一个常见任务是显示整个树，通常以某种形式的缩进显示。 在纯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)
找到所有的叶节点
通过使用左连接查询，我们可以找到树中的所有叶节点(没有子节点):
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 |
+--------------+
检索单条路径(从叶子节点开始往上)
自联接(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' AND t4.name = 'FLASH';
+-------------+----------------------+-------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
+-------------+----------------------+-------------+-------+
1 row in set (0.01 sec)
这种方法的主要局限性是层次结构中的每个级别都需要一个自连接，并且随着连接的复杂性增加，性能自然会随着每个级别的增加而降低。
邻接表模型的限制
在纯SQL中使用邻接表模型可能非常困难。在能够看到一个类别的完整路径之前，我们必须知道它所在的层次。 此外，在删除节点时必须特别注意，因为在此过程中可能会使整个子树成为孤儿(删除portable electronics类别，它的所有子树都会成为孤儿)。 其中一些限制可以通过使用客户端代码或存储过程来解决。 使用过程语言，我们可以从树的底部开始，向上迭代以返回完整的树或单个路径。我们还可以使用过程编程来删除节点，而不会使整个子树孤立，方法是提升一个子元素，并重新排序剩下的子元素以指向新的父元素。
2. 嵌套集模型
我想在本文中重点介绍的是另一种方法，通常称为“嵌套集模型”。 在嵌套集模型中，我们可以以新的方式查看层次结构，而不是将其视为节点和行，而是将其视为嵌套容器。 尝试通过以下方式描绘我们的electronics类别：
请注意，随着父类别将其子级包裹起来，我们的层次结构仍然得以维护。我们通过使用左右值来表示节点的嵌套，在表中表示这种形式的层次结构：
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是因为left和right是MySQL中的保留字，有关信息，请参见http://dev.mysql.com/doc/mysql/en/reserved-words.html。 保留字的完整列表。
那么我们如何确定左右值呢?我们从最左边开始编号，一直编号到右边:
此设计也可以应用于典型的树：
在处理树时，我们从左到右，每次一层，向下到每个节点的子节点，然后分配右边的数字，再向右移动。这种方法称为改进的预排序树遍历算法。
检索全树
我们可以通过使用将父节点与节点链接在一起的自联接来检索完整树，其依据是节点的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 |
+----------------------+
与我们前面关于邻接表模型的例子不同，这个查询将与树的深度无关。我们不关心BETWEEN子句中节点的rgt值，因为rgt值总是与lft值属于同一个父节点。
查找所有叶节点
在嵌套集合模型中查找所有叶节点甚至比在邻接表模型中使用的左连接方法更简单。如果查看nested_category表，您可能会注意到叶节点的lft和rgt值是连续的数字。为了找到叶节点，我们寻找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类别时，我们希望显示MP3 PLAYERS, CD PLAYERS, 和 2 WAY RADIOS ，但不显示FLASH。
这可以通过在前面的查询中添加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 |
+----------------------+-------+
如果你不希望显示父节点，将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子句中节点与产品表之间的联接。 如您所见，每个类别都有一个计数，子类别的计数反映在父类别中。
添加新节点
现在我们已经了解了如何查询树，我们应该看看如何通过添加新节点来更新树。让我们再看看我们的嵌套集合图:
1: 父节点下已经有子节点情况下添加兄弟节点
如果我们想在“TELEVISIONS”和“PORTABLE ELECTRONICS”节点之间添加一个新节点，则新节点的“lft”和“rgt”值分别为10和11，其右边的所有节点的“lft” 和 “rgt”值将增加2。 然后，我们将使用适当的“lft”和“rgt”值添加新节点。 尽管可以使用MySQL 5中的存储过程来完成此操作，但目前我将假定大多数读者都使用4.1，因为它是最新的稳定版本，并且我将使用“LOCK TABLES”语句隔离查询：
# 查询指定节点名称下的节点个数,如果值大于1说明有子节点
SELECT count(*) as nodeCount
FROM nested_category AS node, nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'TELEVISIONS'
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;
然后，我们可以使用缩进树查询检查嵌套情况:
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: 父节点下没有子节点的情况下添加子节点
相反，如果我们想将一个节点添加为没有现有子节点的节点的子节点，则需要稍作修改。 让我们在"2 WAY RADIOS"节点下面添加一个新的"FRS"节点：
# 查询指定节点名称下的节点个数,如果值大于1说明有子节点
SELECT count(*) as nodeCount
FROM nested_category AS node, nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'TELEVISIONS'
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(因为没有子节点，它的宽度将为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 |
+---------------+
删除节点的其他场景还包括将一个子节点提升到父节点的位置，以及将子节点移动到父节点的兄弟节点下，但是出于篇幅考虑，本文不涉及这些场景。
最后的思考
虽然我希望本文中的信息对您有用，但是SQL中的嵌套集的概念已经存在了十多年，在书籍和互联网上还有很多其他信息。 在我看来，有关管理层次结构信息的最全面信息源是一本书，名为Joe Celko’s Trees and Hierarchies in SQL for Smarties, 由高级SQL领域一位受人尊敬的作者Joe Celko撰写。 Joe Celko通常以嵌套集模型而著称，并且是迄今为止该主题上最多产的作者。 我发现Celko的书在我自己的学习中是宝贵的资源，因此强烈推荐它。 本书涵盖了本文中未涉及的高级主题，并提供了除了邻接表和嵌套集模型之外的其他管理分层数据的方法。
在下面的“参考资料/资源”部分中，我列出了一些可用于您的管理分层数据研究的Web资源，包括一对与PHP相关的资源，其中包括用于处理MySQL中的嵌套集的预构建PHP库。 那些当前使用邻接表模型并想使用嵌套集模型的人将在下面列出的Storing Hierarchical Data in a Database资源中找到用于在两者之间进行转换的示例代码。
参考资料/资源
- Joe Celko’s Trees and Hierarchies in SQL for Smarties – ISBN 1-55860-920-2
- Storing Hierarchical Data in a Database
- A Look at SQL Trees
- SQL Lessons
- Nontraditional Databases
- Trees in SQL
- Trees in SQL (2)
- Converting an adjacency list model to a nested set model
- Nested Sets in PHP Demonstration (German)
- A Nested Set Library in PHP
实战
子树的深度(Mybatis-Plus方式)
根据节点name来查找子树的深度信息,查询语句是这个:
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
Vo类(TreeDepthVo)
package wjw.test.mybatisplus.master.vo;
public class TreeDepthVo {
private String name;
private Integer depth;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getDepth() {
return depth;
}
public void setDepth(Integer depth) {
this.depth = depth;
}
public TreeDepthVo() {
super();
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("TreeDepthVo [name=");
builder.append(name);
builder.append(", depth=");
builder.append(depth);
builder.append("]");
return builder.toString();
}
}
Mapper类(TreeMapper)
package wjw.test.mybatisplus.master.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import wjw.test.mybatisplus.master.vo.TreeDepthVo;
@Mapper
public interface TreeMapper extends BaseMapper {
@Select("SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth\r\n"
+ "FROM nested_category AS node,\r\n"
+ " nested_category AS parent,\r\n"
+ " nested_category AS sub_parent,\r\n"
+ " (\r\n"
+ " SELECT node.name, (COUNT(parent.name) - 1) AS depth\r\n"
+ " FROM nested_category AS node,\r\n"
+ " nested_category AS parent\r\n"
+ " WHERE node.lft BETWEEN parent.lft AND parent.rgt\r\n"
+ " AND node.name = #{name}\r\n"
+ " GROUP BY node.name\r\n"
+ " ORDER BY node.lft\r\n"
+ " )AS sub_tree\r\n"
+ "WHERE node.lft BETWEEN parent.lft AND parent.rgt\r\n"
+ " AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt\r\n"
+ " AND sub_parent.name = sub_tree.name\r\n"
+ "GROUP BY node.name\r\n"
+ "ORDER BY node.lft")
List<TreeDepthVo> getTreeDepth(@Param("name")String name);
}
测试类(MybatisPlusTestTree)
package wjw.test.mybatisplus;
import java.util.List;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.test.context.ActiveProfiles;
import wjw.test.mybatisplus.master.mapper.TreeMapper;
import wjw.test.mybatisplus.master.vo.TreeDepthVo;
@SpringBootTest(
webEnvironment = WebEnvironment.NONE)
@ActiveProfiles("dev") //标明激活哪个profile
public class MybatisPlusTestTree {
@Autowired
private TreeMapper b1001Mapper;
public MybatisPlusTestTree() {
//
}
@BeforeEach
public void setup() {
//
}
@AfterEach
public void stop() {
}
@Test
public void get1001() {
List<TreeDepthVo> list = b1001Mapper.getTreeDepth("ELECTRONICS");
System.out.println(list);
}
}
测试结果:
w.t.m.master.mapper.TreeMapper.getTreeDepth - [debug,137] - ==> Preparing: 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 = ? 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
w.t.m.master.mapper.TreeMapper.getTreeDepth - [debug,137] - ==> Parameters: ELECTRONICS(String)
w.t.m.master.mapper.TreeMapper.getTreeDepth - [trace,143] - <== Columns: name, depth
w.t.m.master.mapper.TreeMapper.getTreeDepth - [trace,143] - <== Row: ELECTRONICS, 0
w.t.m.master.mapper.TreeMapper.getTreeDepth - [trace,143] - <== Row: TELEVISIONS, 1
w.t.m.master.mapper.TreeMapper.getTreeDepth - [trace,143] - <== Row: TUBE, 2
w.t.m.master.mapper.TreeMapper.getTreeDepth - [trace,143] - <== Row: LCD, 2
w.t.m.master.mapper.TreeMapper.getTreeDepth - [trace,143] - <== Row: PLASMA, 2
w.t.m.master.mapper.TreeMapper.getTreeDepth - [trace,143] - <== Row: PORTABLE ELECTRONICS, 1
w.t.m.master.mapper.TreeMapper.getTreeDepth - [trace,143] - <== Row: MP3 PLAYERS, 2
w.t.m.master.mapper.TreeMapper.getTreeDepth - [trace,143] - <== Row: FLASH, 3
w.t.m.master.mapper.TreeMapper.getTreeDepth - [trace,143] - <== Row: CD PLAYERS, 2
w.t.m.master.mapper.TreeMapper.getTreeDepth - [trace,143] - <== Row: 2 WAY RADIOS, 2
w.t.m.master.mapper.TreeMapper.getTreeDepth - [debug,137] - <== Total: 10
[TreeDepthVo [name=ELECTRONICS, depth=0],
TreeDepthVo [name=TELEVISIONS, depth=1],
TreeDepthVo [name=TUBE, depth=2],
TreeDepthVo [name=LCD, depth=2],
TreeDepthVo [name=PLASMA, depth=2],
TreeDepthVo [name=PORTABLE ELECTRONICS, depth=1],
TreeDepthVo [name=MP3 PLAYERS, depth=2],
TreeDepthVo [name=FLASH, depth=3],
TreeDepthVo [name=CD PLAYERS, depth=2],
TreeDepthVo [name=2 WAY RADIOS, depth=2]]
删除节点(Mybatis-Plus方式)
Vo类(NestedCategory)
package wjw.test.mybatisplus.master.vo;
import java.io.Serializable;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
public class NestedCategory implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(
type = IdType.AUTO)
/**
* category_id
*/
private Integer categoryId;
/**
* name
*/
private String name;
/**
* lft
*/
private Integer lft;
/**
* rgt
*/
private Integer rgt;
public NestedCategory() {
}
public Integer getCategoryId() {
return categoryId;
}
public void setCategoryId(Integer categoryId) {
this.categoryId = categoryId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getLft() {
return lft;
}
public void setLft(Integer lft) {
this.lft = lft;
}
public Integer getRgt() {
return rgt;
}
public void setRgt(Integer rgt) {
this.rgt = rgt;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("NestedCategory [categoryId=");
builder.append(categoryId);
builder.append(", name=");
builder.append(name);
builder.append(", lft=");
builder.append(lft);
builder.append(", rgt=");
builder.append(rgt);
builder.append("]");
return builder.toString();
}
}
Mapper类(TreeMapper)
package wjw.test.mybatisplus.master.mapper;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import wjw.test.mybatisplus.master.vo.NestedCategory;
@Mapper
@DS("master")
public interface TreeMapper extends BaseMapper<NestedCategory> {
/**
* 删除节点
* 使用嵌套集所涉及的最后一个基本任务是删除节点。删除节点时所采取的操作过程取决于节点在层次结构中的位置;删除叶节点比删除有子节点的节点更容易，因为我们必须处理孤立的节点。
* @param name 节点名称(假定节点名称都是唯一的)
*/
@SuppressWarnings("rawtypes")
@Transactional(propagation=Propagation.REQUIRES_NEW)
default void deleteNode(@Param("name") String name) {
/*
* SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';
*/
List<Map> lm = this.getLefRightWidth(name);
if (lm == null || lm.size() == 0) {
return;
}
int lft = Integer.parseInt(lm.get(0).get("lft").toString());
int rgt = Integer.parseInt(lm.get(0).get("rgt").toString());
int width = Integer.parseInt(lm.get(0).get("width").toString());
{ // DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;
QueryWrapper<NestedCategory> wrapper = new QueryWrapper<>();
wrapper.between("lft", lft, rgt);
this.delete(wrapper);
}
{ // UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
NestedCategory tree = new NestedCategory();
tree.setRgt(rgt - width);
QueryWrapper<NestedCategory> wrapper = new QueryWrapper<>();
wrapper.gt("rgt", rgt);
this.update(tree, wrapper);
}
{ // UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;
NestedCategory tree = new NestedCategory();
tree.setLft(lft - width);
QueryWrapper<NestedCategory> wrapper = new QueryWrapper<>();
wrapper.gt("lft", rgt);
this.update(tree, wrapper);
}
}
@Select("SELECT lft, rgt, (rgt - lft + 1) as width "
+ "FROM nested_category "
+ "WHERE name = #{name}")
List<Map> getLefRightWidth(@Param("name")String name);
}
测试类(MybatisPlusTestTree)
package wjw.test.mybatisplus;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.test.context.ActiveProfiles;
import wjw.test.mybatisplus.master.mapper.TreeMapper;
@SpringBootTest(
webEnvironment = WebEnvironment.NONE)
@ActiveProfiles("dev") //标明激活哪个profile
public class MybatisPlusTestTree {
@Autowired
private TreeMapper treeMapper;
public MybatisPlusTestTree() {
//
}
@BeforeEach
public void setup() {
//
}
@AfterEach
public void stop() {
}
@Test
public void testDeleteNode() {
treeMapper.deleteNode("MP3 PLAYERS");
}
}
测试结果:
w.t.m.master.mapper.TreeMapper.getLefRightWidth - [debug,137] - ==> Preparing: SELECT lft, rgt, (rgt - lft + 1) as width FROM nested_category WHERE name = ?
w.t.m.master.mapper.TreeMapper.getLefRightWidth - [debug,137] - ==> Parameters: MP3 PLAYERS(String)
w.t.m.master.mapper.TreeMapper.getLefRightWidth - [trace,143] - <== Columns: lft, rgt, width
w.t.m.master.mapper.TreeMapper.getLefRightWidth - [trace,143] - <== Row: 11, 14, 4
w.t.m.master.mapper.TreeMapper.getLefRightWidth - [debug,137] - <== Total: 1
w.test.mybatisplus.master.mapper.TreeMapper.delete - [debug,137] - ==> Preparing: DELETE FROM nested_category WHERE (lft BETWEEN ? AND ?)
w.test.mybatisplus.master.mapper.TreeMapper.delete - [debug,137] - ==> Parameters: 11(Integer), 14(Integer)
w.test.mybatisplus.master.mapper.TreeMapper.delete - [debug,137] - <== Updates: 2
w.test.mybatisplus.master.mapper.TreeMapper.update - [debug,137] - ==> Preparing: UPDATE nested_category SET rgt=? WHERE (rgt > ?)
w.test.mybatisplus.master.mapper.TreeMapper.update - [debug,137] - ==> Parameters: 10(Integer), 14(Integer)
w.test.mybatisplus.master.mapper.TreeMapper.update - [debug,137] - <== Updates: 4
w.test.mybatisplus.master.mapper.TreeMapper.update - [debug,137] - ==> Preparing: UPDATE nested_category SET lft=? WHERE (lft > ?)
w.test.mybatisplus.master.mapper.TreeMapper.update - [debug,137] - ==> Parameters: 7(Integer), 14(Integer)
w.test.mybatisplus.master.mapper.TreeMapper.update - [debug,137] - <== Updates: 2
GitCode 天启AI是一款由 GitCode 团队打造的智能助手，基于先进的LLM（大语言模型）与多智能体 Agent 技术构建，致力于为用户提供高效、智能、多模态的创作与开发支持。它不仅支持自然语言对话，还具备处理文件、生成 PPT、撰写分析报告、开发 Web 应用等多项能力，真正做到“一句话，让 Al帮你完成复杂任务”。
更多推荐
1
0- 0
已为社区贡献1条内容
所有评论(0)