mysql tree_MySQL树形遍历(二)

转载自:

http://blog.csdn.net/dreamer0924/article/details/7580278

英文原文:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

预排序遍历树算法：modified preorder tree traversal algorithm

这个算法有如下几个数据结构

1 lft 代表左 left

2 rgt 代表右 right

3 lvl 代表所在的层次 level

下面这个图是一个典型的结构

我们先看一些使用方法

1     查看整个树(A)有多少节点(包含自己)

直接看根节点就行了 (right-left+1)/2 = (20-1+1)/2 = 10

这个数有10个节点

2     查看从节点A到E的路径

select * from tree where lft between 1 and 6 and rgt between 7 and 20 order by lft

得到的结果是A,B,D,E 这4个节点的数据,且按照访问路径的顺序

如果2个节点之间不是上下级的关系，则查询没有结果

反向也是一样的，可以拿到底部一个节点，到上级节点的路径

select * from tree where lft between 1 and 6 and rgt between 7 and 20 order by lft desc

唯一的区别就是排序是反向的就行了。

3     得到某个节点下面的所有节点，且按照树状结构返回

我们用B做例子

select * from tree where lft>2 and right<11 order by lft

拿到的结果是 C,D,E,F,而且顺序也是正确的。

4     拿到所有下2级的子节点

我们A做例子，这次加上了lvl的参数,因为A的level是1，所以我们查询level不大于3的。

select * from tree where lft>2 and right<11 and lvl<=3 order by lft

下面看我们新增加一个节点的方法。

我们在根节点的下面，G节点的右侧增加一个X节点

我们要做的工作就是

1 G节点的右参数为13

2 变更所有的受影响的节点,给新节点腾出空位子

所有左节点比G节点大的，都增加2

update tree set lft=lft+2 where lft>12

所有右节点比G节点大的，都增加2

update tree set rgt=rgt+2 where rgt>13

3 新节点放在空位子上,lft=14,rgt=15

这样就完成了一个新节点的增加操作。

另一篇详细解释：

译文:Yimin

引言

大多数用户都曾在数据库中处理过分层数据(hierarchical data)，认为分层数据的管理不是关系数据库的目的。之所以这么认为，是因为关系数据库中的表没有层次关系，只是简单的平面化的列表；而分层数据具有父－子关系，显然关系数据库中的表不能自然地表现出其分层的特性。

我们认为，分层数据是每项只有一个父项和零个或多个子项(根项除外，根项没有父项)的数据集合。分层数据存在于许多基于数据库的应用程序中，包括论坛和邮件列表中的分类、商业组织图表、内容管理系统的分类、产品分类。我们打算使用下面一个虚构的电子商店的产品分类：

这些分类层次与上面提到的一些例子中的分类层次是相类似的。在本文中我们将从传统的邻接表(adjacency list)模型出发，阐述2种在MySQL中处理分层数据的模型。

邻接表模型

上述例子的分类数据将被存储在下面的数据表中(我给出了全部的数据表创建、数据插入的代码，你可以跟着做)：

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的子项，哪个是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 |

+-------------+----------------------+--------------&#