长期饭票

大家好,请喊我序员!
QQ:15838986
posts - 124, comments - 22, trackbacks - 0, articles - 0
  首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

[转]实现无限级分类的思路

Posted on 2012-06-26 17:33  Baode  阅读(257)  评论(1编辑  收藏
第一种方案:

  使用递归算法,也是使用频率最多的,大部分开源程序也是这么处理,不过一般都只用到四级分类。这种算法的数据库结构设计最为简单。category表中一个字段id,一个字段fid(父id)。这样可以根据WHERE id = fid来判断上一级内容,运用递归至最顶层。

  分析:通过这种数据库设计出的无限级,可以说读取的时候相当费劲,所以大部分的程序最多3-4级分类,这就足以满足需求,从而一次性读出所有的数据,再对得到数组或者对象进行递归。本身负荷还是没太大问题。但是如果分类到更多级,那是不可取的办法。

  这样看来这种分类有个好处,就是增删改的时候轻松了…然而就二级分类而言,采用这种算法就应该算最优先了。

  第二种方案:

  设置fid字段类型为varchar,将父类id都集中在这个字段里,用符号隔开,比如:1,3,6

  这样可以比较容易得到各上级分类的ID,而且在查询分类下的信息的时候,

  可以使用:



SELECT * FROM categoryWHERE pid LIKE1,3%

  分析:相比于递归算法,在读取数据方面优势非常大,但是若查找该分类的所有 父分类 或者 子分类 查询的效率也不是很高,至少也要二次query,从某种意义看上,个人觉得不太符合数据库范式的设计。倘若递增到无限级,还需考虑字段是否达到要求,而且在修改分类和转移分类的时候操作将非常麻烦。

  暂时,在自己项目中用的就是类似第二种方案的解决办法。就该方案在我的项目中存在这样的问题, 如果当所有数据记录达到上万甚至10W以上后,一次性将所以分类,有序分级的现实出来,效率很低。极有可能是项目处理数据代码效率低带来的。现在正在改良。

  第三种方案:

  无限级分类----改进前序遍历树

  那么理想中的树型结构应具备哪些特点呢?数据存储冗余小、直观性强;方便返回整个树型结构数据;可以很轻松的返回某一子树(方便分层加载);快整获以某节点的祖谱路径;插入、删除、移动节点效率高等等。带着这些需求我查找了很多资料,发现了一种理想的树型结构数据存储及操作算法,改进的前序遍历树模型(The Nested Set Model)。


        采用左右值编码来存储无限分级树形结构的数据库表设计,下面是详细介绍。

  下面我力图用比较简短的文字,少量图表,及相关核心sql语句来描述这种设计方案:

  首先,我们弄一棵树作为例子:

商品
|---食品
|    |---肉类
|    |    |--猪肉
|    |---蔬菜类
|          |--白菜
|---电器
     |--电视机
     |--电冰箱


采用左右值编码的保存该树的数据记录如下(设表名为tree):
Type_id Name Lft Rgt
1 商品 1 18
2 食品 2 11
3 肉类 3 6
4 猪肉 4 5
5 蔬菜类 7 10
6 白菜 8 9
7 电器 12 17
8 电视机 13 14
9 电冰箱 15 16


第一次看见上面的数据记录,相信大部分人都不清楚左值(Lft)和右值(Rgt)是根据什么规则计算出来的,而且,这种表设计似乎没有保存父节点的信息。下面把左右值和树结合起来,请看:
          1商品18
     +---------------------------------------+
                2食品11                                    12电器17
          +-----------------+                     +---------------------+
    3肉类6          7蔬菜类10          13电视机14       15电冰箱16
    4猪肉5           8白菜9
请用手指指着上图中的数字,从1数到18,学习过数据结构的朋友肯定会发现什么吧?对,你手指移动的顺序就是对这棵树的进行先序遍历的顺序。接下来,让我讲述一下如何利用节点的左右值,得到该节点的父节点,子孙节点数量,及自己在树中的层数。

假定我们要对节点“食品”及其子孙节点进行先序遍历的列表,只需使用如下一条sql语句:
select * from tree where Lft between 2 and 11 order by Lft asc
查询结果如下:
Type_id Name Lft Rgt
2 食品 2 11
3 肉类 3 6
4 猪肉 4 5
5 蔬菜类 7 10
6 白菜 8 9


那么某个节点到底有多少子孙节点呢?很简单,子孙总数 =(右值-左值-1)/2
以节点“食品”举例,其子孙总数=(11-2-1)/ 2 = 4

同时,我们在列表显示整个类别树的时候,为了方便用户直观的看到树的层次,一般会根据节点所处的层数来进行相应的缩进,那么,如何计算节点在树中的层数呢?还是只需通过左右值的查询即可,以节点“食品”举例,sql语句如下:
select count(*) from tree where lft <= 2 and rgt >= 11
为了方便列表,我们可以为tree表建立一个视图,添加一个层数列,该类别的层数可以写一个自定义函数来计算。该函数如下: CREATE FUNCTION dbo.CountLayer
(   
    @type_id int
)
RETURNS int
AS
begin
    declare @result int
    set @result=0
    declare @lft int
    declare @rgt int
    if exists (select 1 from tree where type_id=@type_id)
    begin
        select @lft=lft,@rgt=rgt from tree where type_id=@type_id
        select @result = count(*) from tree where lft <= @lft and rgt >= @rgt
    end   
    return @result
end
GO

然后,我们建立如下视图:
CREATE VIEW dbo.TreeView
AS
SELECT type_id, name, lft, rgt, dbo.CountLayer(type_id) AS layer FROM dbo.tree ORDER BY lft
GO

给出对于给定某个节点,对该节点及其子孙节点进行先序遍历的存储过程:
CREATE PROCEDURE [dbo].[GetTreeListByNode]
(
    @type_id int --给定节点标识
)
AS
declare @lft int
declare @rgt int
if exists (select 1 from tree where type_id=@type_id)
    begin
        select @lft=lft,@rgt=rgt from tree where type_id=@type_id
        select * from TreeView where lft between @lft and @rgt order by lft asc
    end
go


现在,我们使用上面的存储过程来列表节点“食品”及其所有子孙节点,查询结果如下:
Type_id Name Lft Rgt Layer
2 食品 2 11 2
3 肉类 3 6 3
4 猪肉 4 5 4
5 蔬菜类 7 10 3
6 白菜 8 9 4



采用左右值编码的设计方案,在进行类别树的遍历时,由于只需进行2次查询,消除了递归,再加上查询条件都为数字比较,效率极高,类别树的记录条目越多,执行效率越高。看到这里,相信不少人对这种设计方案有所心动了,下面让我们接着看看如何在这种表结构中实现插入、删除、同层平移节点(变更同层节点排序)的功能。

假定我们要在节点“肉类”下添加一个子节点“牛肉”,该树将变成:
                                     1商品18+2
                      +--------------------------------------------+
                2食品11+2                                  12+2电器17+2
          +-----------------+                                    +-------------------------+
    3肉类6+2   7+2蔬菜类10+2         13+2电视机14+2    15+2电冰箱16+2
    +-------------+
4猪肉5  6牛肉7  8+2白菜9+2

看完上图相应节点左右值的变化后,相信大家都知道该如何写相应的sql脚本吧?下面我给出相对完整的插入子节点的存储过程:
CREATE PROCEDURE [dbo].[AddSubNodeByNode]
(
    @type_id int,
    @name varchar(50)
)
AS
declare @rgt int
if exists (select 1 from tree where type_id=@type_id)
    begin
       SET XACT_ABORT ON
       BEGIN TRANSACTION
        select @rgt=rgt from tree where type_id=@type_id
        update tree set rgt=rgt+2 where rgt>=@rgt
        update tree set lft=lft+2 where lft>=@rgt
        insert into tree (name,lft,rgt) values (@name,@rgt,@rgt+1)   
        COMMIT TRANSACTION
       SET XACT_ABORT OFF   
    end
go


然后,我们删除节点“电视机”,再来看看该树会变成什么情况:
                                            1商品20-2
                       +-----------------------------------+
                2食品13                                14电器19-2
          +-----------------+               
       3肉类8          9蔬菜类12              17-2电冰箱18-2
    +----------+
4猪肉5  6牛肉7  10白菜11

  相应的存储过程如下:
CREATE PROCEDURE [dbo].[DelNode]
    @type_id int
AS
declare @lft int
declare @rgt int
if exists (select 1 from tree where type_id=@type_id)
    begin
       SET XACT_ABORT ON
       BEGIN TRANSACTION
        select @lft=lft,@rgt=rgt from tree where type_id=@type_id
        delete from tree where lft>=@lft and rgt<=@rgt
        update tree set lft=lft-(@rgt-@lft+1) where lft>@lft
        update tree set rgt=rgt-(@rgt-@lft+1) where rgt>@rgt
        COMMIT TRANSACTION
       SET XACT_ABORT OFF   
End

  注意:因为删除某个节点会同时删除该节点的所有子孙节点,而这些被删除的节点的个数为:(被删节点的右值-被删节点的左值+1)/2,而任何一个节点同时具有唯一的左值和唯一的右值,故删除作废节点后,其他相应节点的左、右值需要调整的幅度应为:减少(被删节点的右值-被删节点的左值+1)。

  最后,让我们看看平移节点“电器”,将其和其所有子孙节点移动到节点“食品”之前后,该树会变成什么情况:

             1商品18
+-----------------------------------+
                14-12电器17-12                      2+4食品13+4
                                                               +----------------------+              
             15-12电冰箱16-12      3+4肉类8+4      9+4蔬菜类12+4       
                                                +-------------------+
                                      4+4猪肉5+4     6+4牛肉7+4 10+4白菜11+4

大家仔细观察一下交换后同层2个节点和其所有子孙节点左右值的变化,可以发现一个明显的规律,那就是,节点“电器”及其所有子孙节点的左右值均减少12,而节点“食品”及其所有子孙节点的左右值均增加4。而节点“电器”+其子孙节点的数量为2,节点“食品”+其子孙节点的数量为6,这其中有什么联系吗?还记得我在删除节点的存储过程后面的注释吗?任何一个节点同时具有唯一的左值和唯一的右值。让我们把节点数量*2,正好和节点左右值需要调整的幅度相等。由此规律,我们可以编写出类似下面的存储过程来实现节点同层前移的功能:
CREATE PROCEDURE [dbo].[MoveNodeUp]
    @type_id int
AS
declare @lft int
declare @rgt int
declare @layer int
if exists (select 1 from tree where type_id=@type_id)
    begin
       SET XACT_ABORT ON
       BEGIN TRANSACTION
        select @lft=lft,@rgt=rgt,@layer=layer from TreeView where type_id=@type_id
        if exists (select * from TreeView where rgt=@lft-1 and layer=@layer)
           begin
               declare @brother_lft int
               declare @brother_rgt int
               select @brother_lft=lft,@brother_rgt=rgt from TreeView where rgt=@lft-1 and layer=@layer
               update tree set lft=lft-(@brother_rgt-@brother_lft+1) where lft>=@lft and rgt<=@rgt
               update tree set lft=lft+(@rgt-@lft+1) where lft>=@brother_lft and rgt<=@brother_rgt
               update tree set rgt=rgt-(@brother_rgt-@brother_lft+1) where rgt>@brother_rgt and rgt<=@rgt
               update tree set rgt=rgt+(@rgt-@lft+1) where lft>=@brother_lft+(@rgt-@lft+1) and rgt<=@brother_rgt
           end
        COMMIT TRANSACTION
       SET XACT_ABORT OFF   
    end

  注意:节点的同层平移可以采用临时表来做中介,降低代码的复杂度。不用临时表来处理也行,但是update语句顺序一定要考虑周详。否则,一旦出现bug,对整个类别表的破坏是惊人的,强烈推荐在做上述工作前对类别表进行完整备份。

  同层下移的存储过程和同层上移类似,有兴趣的朋友可以自己动手编写体味一下其中的细节,我就不在这里列出来了。

  最后,我对上面这种左右值编码实现无限分级类别树的方案做一个总结:
  优点:在消除递归的前提下实现了无限分级,而且查询条件是基于整形数字比较的,效率很高。可以进行先序列表,添加,修改,删除,同层平移等常规操作,基本满足需求。
  缺点:由于这种左右值编码的方式和常见的阿拉伯数字直观排序不同,再加上节点在树中的层次,顺序不是直观显示出来,而必须通过简单的公式计算后得到,需要花费一定的时间对其数学模型进行深入理解。而且,采用该方案编写相关存储过程,新增,删除,同层平移节点需要对整个树进行查询修改,由此导致的代码复杂度,耦合度较高,修改维护的风险较高。