如何在MySQL中使用"嵌套集模型"管理分层数据

2021-01-29 565

如何在MySQL中使用"嵌套集模型"管理分层数据

原文地址:http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

介绍

大多数用户都曾在SQL数据库中处理过层次数据，毫无疑问，层次数据的管理不是关系数据库的目的。关系数据库的表不是分层的(像XML一样)，而是一个简单的列表。分层数据具有父子关系，这种关系在关系数据库表中没有自然地表示出来。

对于我们的目的来说，分层数据是一个数据集合，其中每个项都有一个父项和零个或多个子项(根项除外，它没有父项)。层次数据可以在各种数据库应用程序中找到，包括论坛和邮件列表线程、业务组织图、内容管理类别和产品类别。出于我们的目的，我们将使用一个虚构的电子商店中的以下产品类别层次结构:

这些类别形成层次结构的方式与上面提到的其他例子非常相似。在本文中，我们将研究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)

在邻接表模型中，表中的每一项都包含一个指向其父项的指针。最顶层的元素，在本例中是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类别，它的所有子树都会成为孤儿)。其中一些限制可以通过使用客户端代码或存储过程来解决。使用过程语言，我们可以从树的底部开始，向上迭代以返回完整的树或单个路径。我们还可以使用过程编程来删除节点，而不会使整个子树孤立，方法是提升一个子元素，并重新排序剩下的子元素以指向新的父元素。

嵌套集模型

我想在本文中重点介绍的是另一种方法，通常称为“嵌套集模型”。在嵌套集模型中，我们可以以新的方式查看层次结构，而不是将其视为节点和行，而是将其视为嵌套容器。尝试通过以下方式描绘我们的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”语句隔离查询：

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"节点：

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资源中找到用于在两者之间进行转换的示例代码。

参考资料/资源

微信关注我们

原文链接：https://my.oschina.net/wstone/blog/4937731

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

快速了解云原生架构

作者 | 潘义文（空易）来源|阿里巴巴云原生公众号起源 1. 云原生（Cloud Native）的由来云原生的概念最早开始于 2010 年，在当时 Paul Fremantle 的一篇博客中被提及，他一直想用一个词表达一种架构，这种架构能描述应用程序和中间件在云环境中的良好运行状态。因此他抽象出了 Cloud Native 必须包含的属性，只有满足了这些属性才能保证良好的运行状态。当时提出云原生是为了能构建一种符合云计算特性的标准来指导云计算应用的编写。后来到 2013 年 Matt Stine 在推特上迅速推广云原生概念，并在 2015 年《迁移到云原生架构》一书中定义了符合云原生架构的特征：12 因素、微服务、自服务、基于 API 协作、扛脆弱性。而由于这本书的推广畅销，这也成了很多人对云原生的早期印象，同时云原生也被 12 要素变成了一个抽象的概念。Matt Stine 认为在单体架构向 Cloud Native 迁移的过程中，需要文化、组织、技术共同变革。**解读：**云原生架构本质上也是一种软件架构，最大的特点是在云环境下运行，也算是微服务的一种延伸。 2. CNCF...

2021-01-29

738

简介 docker容器的出现，彻底的改变了应用程序的运行方式，而nodejs同样的也颠覆了后端应用程序的开发模式。两者结合起来，就会产生意想不到的作用。本文将会以一个常用的nodejs程序为例，分析怎么使用docker来构建nodejs image. 准备nodejs应用程序一个标准的nodejs程序，需要一个package.json文件来描述应用程序的元数据和依赖关系，然后通过npm install来安装应用的依赖关系，最后通过node app.js来运行程序。本文将会创建一个简单的koa应用程序，来说明docker的使用。首先创建package.json文件： { "name": "koa-docker", "description": "怎么将nodejs koa程序打包成docker应用", "version": "0.0.1", "dependencies": { "ejs": "^2.5.6", "fs-promise": "^2.0.3", "koa": "^2.2.0", "koa-basic-auth": "^2.0.0", "koa-body": "^4.0...

2021-01-29

439

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。