原文地址：http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

作者：Mike Hillyer

翻译：陈建平  chinaontology@gmail.com

摘要：无限分级的树状结构往往很难处理，作者推荐“嵌套集合模型”方法，可以用简单的SQL完成树状数据的操作，避免了常用的邻接表模型的多次连接查询带来的巨大性能开销。

介绍

     大部分的开发者都会遇到要在SQL数据库中处理层状数据的问题，也都知道关系数据库其实并不擅长此道。关系数据库中的表并不是层次状的（XML是层次结构），而是扁平的列表。层状数据中的父子关系无法在关系表中自然地表达。

    层状数据是一个集合，集合当中的元素都有唯一的父节点和零个或多个的子节点（根节点除外，它无父节点）。层状数据广泛应用于数据库应用系统当中，包括了论坛、邮件列表、商业组织结构、内容管理分类和产品分类等。为了说明问题，我们使用一个虚拟的电子商店的产品分类层次作为例子。

这些产品分类形成一个层状结构，与上面提到的其他应用系统当中的结构类似。在这篇文章当中，我们将在 MySQL 中使用两种处理方式，先使用传统的邻接表模型。

邻接表模型

典型的做法下，范例中的分类数据将被存储于如下结构的表中（为了方便读者，我已经包含了完整的CREATE 和 INSERT 命令）

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)

在这种邻接表模式下，表中每一个节点都包含一个指向父节点的指针。这个例子中的最顶层的元素，其父节点为NULL。邻接表模式的优点是很简单，很容易看清父子关系，例如 Flash 是 MP3 PLAYERS（MP3播放器）的子节点，MP3 PLAYERS是PORTABLE ELECTRONICS（便携式电子设备）的子节点，PORTABLE ELECTRONICS 又是 ELECTRONICS （电子设备）的子节点。 邻接表在客户端的代码上比较容易处理，但是后台的服务器要使用纯粹的SQL来工作是有问题的。

检索完整的树

处理层状数据时，首先遇到的常见任务是显示整个树，通常要求某种形式的缩进格式。使用SQL时一般的做法是采用自连接：

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)

查询所有的叶子节点

我们可以使用左连接（LEFT JOIN）查询来获得所有的叶子节点（叶子节点：无子节点的节点）

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)

这中方法的主要局限是，你必须在每个层次上进行自连接，性能自然会随着复杂连接的增加而下降。

邻接表模型的局限

使用纯SQL来处理邻接表模型很难做到最好。如果要找到完整的路径，我们必须先知道其所在的层次。其次，还必须特别注意删除操作带来的整棵子树被孤儿化（例如删除 portable 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 |
+-------------+----------------------+-----+-----+

因为 left 和 right 在MySQL当中是保留字，我们使用 lft 和 rgt 来分别表示。（有关MySQL的保留字的全部信息，请参考 http://dev.mysql.com/doc/mysql/en/reserved-words.html ）

那我们如何决定左、右值呢？ 我们从最左边开始向右，为各个集合的边界标上数字编号，如下图：

这个编号设计套用在树形图上如下：

当我们对树形结构编号时，从左向右，一次一层，每个节点左边编号后，紧接着向其下层子节点编号，然后再为节点的右边编号。这个方法被称为改进的“前序遍历树算法”。

取得整个树

基于子节点的 lft 值始终位于其父节点的 lft 和 rgt 值之间的原理，使用自连接的SQL即可取得整棵树。

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 值一样。

查询所有叶节点

在当前的模型下，查询叶节点比邻接表模型中的 LEFT JOIN 方法更简单。查看 nested_category 表，注意到叶节点的特征是其左右值是连续的，所以只需查询 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 |
+----------------------+-------+

这个方法中可以使用任何节点的name，包括根节点。深度值总能根据 name 获取到。

查找直接的下级节点

假设我们想在一个零售商的网站上显示电子产品的分类。当一个用户点击一个类别时，你想给他显示该类别下的产品，并且显示其直接的子类别，而不是该类下所有的子树，不需要搜索所有的子孙。例如，当点击 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 子句中有指向产品表的引用和树节点与产品表的连接。正如你所见，这个统计包含每个类别数量统计，并且每个类别也都包含了其子类的统计值。

添加新节点

现在知道了如何查询这个树，我们将继续看看如何通过添加节点更新它。请再看看我们的嵌套集合模型：

如果我们想在 TELEVISIONS 和 PORTABLE ELECTRONICS 节点之间添加新节点，新节点的左右值将分别是 10 和 11。添加此节点，则其右边的所有节点的左右值都必须加2。这个过程可以通过 MySQL 5 的存储过程完成。我假设大部分读者现在还在使用4.1版，这是最后一个稳定的MySQL版本（译者：此文章较早，现在5.0 以后的版本早已稳定）。所以我还是使用一个独立的查询，以 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;

We can then check our nesting with our indented tree query:

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

在这个例子中，我们对新的父节点的左值以右的所有 lft、rgt 值都进行了增加，然后将我们的新节点放置在此父节点左值的右边，现在我们的新节点已经正确嵌套了：

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;

我们再检查一次以确认节点是否被提升层次：

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的嵌套集合的概念已有超过10年的历史了，还有一些更进一步的信息可以在一些专著和网络上找到。 就我的理解，处理层状数据模型最有价值的专著是 Joe Celko's Trees and Hierarchies in SQL for Smarties ， 作者是高级SQL领域里很受尊敬的Joe Celko，  他是一位多产和备受赞誉的技术作者。Celko 的专著对我的研究和学习来说是无价之宝，我极力推荐。他的书中也包含了其他很多高级主题，本文并未涉及，包括邻接表、嵌套集合以外的其他处理层状数据的方法。

在参考文献和资源部分，我列出了一些Web资源，也许对于读者研究层状数据的处理会有帮助，包括一套PHP的处理MySQL嵌套模型的库。在 Storing Hierarchical Data in a Database 等文章当中也可以找到一些在两种方法之间进行转换的代码。

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="showphotos.aspx.cs" Inherits="Default3" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>信息07-2 首页</title>
<style type="text/css">
 html {
  overflow: hidden;
 }

 body {
  margin: 0px;
  padding: 0px;
  background: #000000;
  position: absolute;
  width: 100%;
  height: 100%;
 }

 #diapoContainer {
  position: absolute;
  margin:0 auto;
  background: #222222;
  left: 10%;
  top: 10%;
  width: 80%;
  height: 80%;
  
  overflow: hidden;
 }

 .imgDC {
  position: absolute;
  cursor: pointer;
  border: #000 solid 2px;
  filter: alpha(opacity=90);
  opacity: 0.9;
  visibility: hidden;
 }

 .spaDC {
  position: absolute;
  filter: alpha(opacity=20);
  opacity: 0.2;
  background: #000;
  visibility: hidden;
 }

 .imgsrc {
  position: absolute;
  width: 120px;
  height: 100px;
  visibility: hidden;
  margin: 3%;
 }

 #bkgcaption {
  position: absolute;
  bottom: 0px;
  left: 0px;
  width: 100%;
  height: 6%;
  background:#1a1a1a;
 }
 #caption {
  position: absolute;
  font-family: arial, helvetica, verdana, sans-serif;
  white-space: nowrap;
  color: #fff;
  bottom: 0px;
  width: 100%;
  left: -10000px;
  text-align: center;
 }

</style>

<script type="text/javascript">
var xm;
var ym;

</html>

今天在项目上遇到了这个问题，其实只是window.returnValue的简单应用，不是asp.net的专属内容。作为积累，记录一个简单的实现模型。

图1  用到的文件

   从图1中我们可以看到，只用到了两个页面，其中Default.aspx作为父页面，Default2.aspx作为子页面被弹出。Default.aspx页面上有两个TextBox一个Button，代码如下：

 1 <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
 2 
 3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 4 
 5 <html xmlns="http://www.w3.org/1999/xhtml">
 6 <head runat="server">
 7     <title></title>
 8     
 9 </head>
10 <body>
11     <form id="form1" runat="server">
12     <div>
13     <asp:TextBox runat="server" ID="a1">
14     </asp:TextBox>
15     <asp:TextBox ID="TextBox1" runat="server" ontextchanged="TextBox1_TextChanged"></asp:TextBox>
16     <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
17     
18     
19     </div>
20     
21     </form>
22 </body>
23 </html>
24 

在Button1的Click事件中，我们注册弹窗脚本，代码如下

 1     protected void Button1_Click(object sender, EventArgs e)
 2     {
 3         StringBuilder s = new StringBuilder();
 4         s.Append("<script language=javascript>");
 5         s.Append("var a=window.showModalDialog('Default2.aspx');");
 6         s.Append("if(a!=null)");
 7         s.Append("document.all('TextBox1').value=a;");
 8         s.Append("</script>");
 9         Type cstype = this.GetType();
10         ClientScriptManager cs = Page.ClientScript;
11         string sname = "lt";
12         if (!cs.IsStartupScriptRegistered(cstype, sname))
13             cs.RegisterStartupScript(cstype, sname, s.ToString());
14     }

其中  s.Append("var a=window.showModalDialog('Default2.aspx');");一句用来弹窗Default2.aspx页面并接收它的返回值。

接收了返回值之后我们把它赋值给TextBox1.

 Default2.aspx页面有一个TextBox和一个Button，代码如下：

 (这里需要注意的是在head里的<base target="_self" />标记十分重要。

 1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
 2 
 3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 4 
 5 <html xmlns="http://www.w3.org/1999/xhtml">
 6 <head >
 7     <title></title>
 8       <base target="_self" />
 9 </head>
10 
11 <body>
12     <form id="form1" runat="server">
13     <div>
14     <asp:textbox runat="server" ID="t1"></asp:textbox>
15     <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
16     
17     </div>
18     </form>
19 </body>
20 </html>
21 

我们在Default2.aspx页面的Button_Click事件中使用脚本返回一个值给父页面。代码如下：

 1    protected void Button1_Click(object sender, EventArgs e)
 2     {
 3         StringBuilder s = new StringBuilder();
 4         s.Append("<script language=javascript>" + "\n");
 5         s.Append("window.returnValue='" + this.GetSelectValue() + "';" + "\n");
 6         s.Append("window.close();" + "\n");
 7         s.Append("</script>");
 8         Type cstype = this.GetType();
 9         ClientScriptManager cs = Page.ClientScript;
10         string csname = "ltype";
11         if (!cs.IsStartupScriptRegistered(cstype, csname))
12             cs.RegisterStartupScript(cstype, csname, s.ToString());
13 
14     }

脚本注册成功之后，我们可以做如下的实验：

1）打开Default1.aspx页面在id为a1的TextBox中输入数字55，然后点击Button

2）在弹窗中输入数字66再点子窗体的按钮关闭子窗体。

3）查看结果

从结果中，我们可以看出我们保留了先输入到父窗体中的值，又接收了从子窗体传递过来的值。

FCKeditor是一个专门使用在网页上属于开放源代码的所见即所得文字编辑器。它志于轻量化，不需要太复杂的安装步骤即可使用。它可和PHP、JavaScript、ASP、ASP.NET、ColdFusion、Java、以及ABAP等不同的编程语言相结合。

有时我们需要在一个页面上使用多个Fck的实例，首先需要按照id获取fck的实例。例如：

 使用FCKeditorAPI.GetInstance获取Fck实例。

 使用这个方法就可以处理多个Fck的情况。

要想很好地优化ERP系统，可以从客户端、服务器、网络等入手，对于我们M1系统的优化来说，SQL 语句的优化就起到很重要的作用了。为此，我们展开，学习了SQL SERVER 2008的事件探查器（SQL SERVER PROFILEr），方便我们对系统优化前后速度与性能的对比。

    如何进入事件探查器：开始---程序---SQL Server 2008---性能工具---SQL SERVER PROFILEr，进入，点击新建事件跟踪，输入sa用户与密码。
如果你输入的用户与密码没有权限的话，会提示：“您必须是 sysadmin 固定服务器角色的成员或具有 ALTER TRACE 权限，才能对 SQL Server 运行跟踪。”

事件探查器重要列名解释：

CPU：事件所使用的 CPU 时间总计（以毫秒为单位）。
Duration ： 持续时间，事件所花费的时间总计,（以毫秒为单位）。
Reads ： 服务器代表事件执行的逻辑磁盘读取数，（以字节为单位） 。
Writes ：服务器代表事件执行的物理磁盘写入数,(以字节为单位) 。
loginName：SQL 登陆用户；
SPID：会话编号；
starttime：开始执行时间；
endtime：执行结束时间；
TEXTDATA：执行的语句。

   如何得到当前会话编号：

1、在SQL SERVER 2008，打开一个查询分析器，就可以在标题最后括号中有一个数值，那个就是当前会话编号，如：57、55等;
2、通过执行代码：ctrl+1，出来的结果集中，第一列spid,即为当前会话编号；
3、通过执行此代码也可以得到：select @@spid.

   得到当前会话编号在事件探查器的那里可以用：

在打开的事件探查器中，先停止探查器，在下方的网格中右键选择属性，点击“事件选择”再点击“列筛选”，选择spid，在这里就可以填写了。
在这里，如果你限制了会话编号，那么，当运行事件探查器，就只会跟踪你所指定的会话编号中所执行的操作。

1、一个问题引发的思考
大家在群里讨论了一个问题，奉文帅之命写篇作文，且看：
String user_web = "user_web"
String sql = "update user set user_web="+user_web+" where userid=2343";
　　大家看看这条sql有没有问题，会将user_web字段 更新成什么？

　　问题的结论是：执行后的记录结果跟执行前一样,(执行时的sql语句为

update user set user_web=user_web where userid=2343，
　　user_web字段值被update为自己原有的值），这与作者的本意想违背却很难被发现有问题。原来的语句漏掉了一对单引号，正确的写法应该是：

String sql = "update user set user_web='"+user_web+"' where userid=2343";
　　用这种写法将变量值传递到sql语句中，意图是达到了，但不是好的方式，理由如下：

　　1.可读性差。单引号双引号混杂（试想有多个变量的情况，再想下如果稍一不慎在前面的单引号双引号直间多个空格又会怎样？）

　　2.会造成潜在的性能问题和sql注入漏洞（对测试代码而言，这两点可能要求不高，但养成良好的编码习惯还是很重要的）

　　下面以非专业人员的角度大致分析下 ‘”+变量+”‘（未采用绑定变量方式）这种方式组织sql为什么会造成潜在的性能问题和sql注入漏洞问题

　　2、性能问题

　　Sql代码不采用绑定变量的方式可能会造成性能问题，表现在以下两个方面：

　　1.导致相同的测试计划被重复执行

　　sql语句的执行过程分几个步骤：语法检查、分析、执行、返回结果。当一条sql通过语法检查后，会在共享池里寻找是否有跟其相同的语句，如果有则用已有的执行计划执行sql语句，如果没有找到，则生成执行计划，然后才执行sql语句。可见，后者比前者多了额外的步骤，消耗了额外的CPU，并导致sql总体执行时间延长，而这里的关键就是“共享池中是否有相同的sql语句”。

String username="test_xx";String sql = "SELECT id,nick FROM user WHERE username='"+username+"'";
　　以这样的方式传递到数据库中的sql为

SELECT id,nick FROM user WHERE username='test_xx'
　　假定这个语句是第一次执行，会生成执行计划。当变量发生变化时（username=”test_yy”），数据库又接收到这样的语句

SELECT id,nick FROM user WHERE username='test_yy'
　　Oracle不认为以上两条语句是相同的，因此又会生成执行计划，而这两者的执行计划是一样的（做了重复的工作）

　　2.导致共享池中的sql语句过多，加速SQL老化，造成共享池内部结构频繁维护。
如果一个某段程序未采用绑定变量的方式而又被大量调用，会导致共享池中不同的sql语句增多，而重用性极低，导致共享池内命中率下降。随着sql数量过多，一些语句逐渐老化，最终被清理出共享池。 而维护共享池内部结构要消耗大量的CPU和内存资源。

　　3、Sql注入漏洞

　　不采用绑定变量的方式可能会造成sql注入漏洞，本文仅仅通过示例说明为什么会造成sql注入漏洞，不对攻击方式、攻击类型等展开。以一个用户验证为例。

String sql = "SELECT id,nick FROM user WHERE username='"+username+"' AND password='"+password+"'";
　　以上代码接收从客户端传来的username和password变量，在数据库中查询验证。假设攻击者从客户端传的username为任意值（如test）password变量为
1′ or ‘1′=’1
此时替换变量后的sql变为

SELECT id,nick FROM user WHERE username='test' AND password='1' or '1'='1'
　　这样得到的结果就是user表中的所有数据了。

　　4、使用绑定变量

　　以上两种问题的解决方式就是使用绑定变量，就是在sql语句里不直接写变量，而是用占位符，在执行时再把占位符替换为具体的变量值。代码片段如下

String sql = "SELECT id,nick FROM user WHERE username=? AND password=?";preparedStatement.setString(1,username);preparedStatement.setString(2,password);
　　一些常用Jdbc工具对此进行了良好的封装，使代码更加简洁。比如Spring的SimpleJdbcTemplate

String sql = "SELECT id,nick FROM user WHERE username=? AND password=?";jdbcTemplate.queryForList(sql,username,password);
　　上面 ？的做占位符的形式被称为顺序占位符，在传参数值时必须注意顺序对应，还有一种是名称占位符。同样以SimpleJdbcTemplate为例说明.

String sql = "SELECT id,nick FROM user WHERE username=:name AND password=:pass";map.put("pass",password);map.put("name",username);jdbcTemplate.queryForList(sql,map);
　　上面的例子中的:name和:pass就是名称占位符,在执行时sql时再绑定变量。
iBatis中有两种占位符，#name# 和$name$两种方式，需要注意的是前者会在执行sql时绑定变量，而后者直接是替换为变量值，所以后者仍然存在sql注入漏洞问题。

　　5、未尽话题

　　接口测试要不要检查sql注入漏洞问题？这个问题值得商榷，个人认为通过常规的用例设计检查sql注入漏洞恐怕不太可行（工作量太大效果不一定好），如果要做的话可借助（或自己开发）一些工具，通过扫描静态代码再人工排查的方式进行。另外如果这项工作进行的太细致，恐怕会跟安全测试的工作重叠太多，当然如果在测试过程中发现开发的代码存在sql注入漏洞（这往往跟开发者编码习惯有关）问题，一定不要放过，进行排查还是很有必要的。