采用左右值编码来存储无限分级树形结构的数据库表设计

梦里梦外; 2022-09-25 14:24 117阅读 0赞

该设计方案的优点是：只用一条查询语句即可得到某个根节点及其所有子孙节点的先序遍历。由于消除了递归，在数据记录量较大时，可以大大提高列表效率。但是，这种编码方案由于层信息位数的限制，限制了每层能所允许的最大子节点数量及最大层数。同时，在添加新节点的时候必须先计算新节点的位置是否超过最大限制。

上面的设计方案必须预先设定类别树的最大层数以及最大子节点数，不是无限分级，在某些场合并不能采用，那么还有更完美的解决方案吗？通过 google的搜索，我又探索到一种全新的无递归查询，无限分级的编码方案——左右值。原文的程序代码是用php写的，但是通过仔细阅读其数据库表设计说明及相关的sql语句，我彻底弄懂了这种巧妙的设计思路，并在这种设计中新增了删除节点，同层平移的需求（原文只提供了列表及插入子节点的sql语句）。

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

首先，我们弄一棵树作为例子：

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

采用左右值编码的保存该树的数据记录如下（设表名为 tree）：

第一次看见上面的数据记录，相信大部分人都不清楚左值（ 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

查询结果如下：

那么某个节点到底有多少子孙节点呢？很简单，子孙总数 ＝（右值 \-左值-1）/2

以节点“食品”举例，其子孙总数＝（ 11-2-1）/ 2 ＝ 4

同时，我们在列表显示整个类别树的时候，为了方便用户直观的看到树的层次，一般会根据节点所处的层数来进行相应的缩进，那么，如何计算节点在树中的层数呢？还是只需通过左右值的查询即可，以节点“食品”举例， sql语句如下：

select count(\*) from tree where lft <= 2 and rgt >= 11

为了方便列表，我们可以为 tree表建立一个视图，添加一个层数列，该类别的层数可以写一个自定义函数来计算。该函数如下：

CREATE   FUNCTION  dbo.CountLayer  
![None.gif][](      
![None.gif][]     @type\_id   **int**   
![None.gif][])  
![None.gif][] RETURNS   **int**   
![None.gif][] AS   
![None.gif][] begin   
![None.gif][]     declare   @result   **int**   
![None.gif][]     set   @result = **0**   
![None.gif][]     declare   @lft   **int**   
![None.gif][]     declare   @rgt   **int**   
![None.gif][]     if   exists  ( select   **1**   from  tree  where  type\_id = @type\_id )  
![None.gif][]     begin   
![None.gif][]         select   @lft = lft, @rgt = rgt  from  tree  where  type\_id = @type\_id   
![None.gif][]         select   @result   =   count ( \* )  from  tree  where  lft  <=   @lft   and  rgt  >=   @rgt   
![None.gif][]     end       
![None.gif][]     return   @result   
![None.gif][] end   
![None.gif][] GO   
![None.gif][]

然后，我们建立如下视图：

![None.gif][] CREATE   VIEW  dbo.TreeView  
![None.gif][] AS   
![None.gif][] SELECT  type\_id, name, lft, rgt, dbo.CountLayer(type\_id)  AS  layer  FROM  dbo.tree  ORDER   BY  lft  
![None.gif][] GO

给出对于给定某个节点，对该节点及其子孙节点进行先序遍历的存储过程：

CREATE   PROCEDURE   \[ dbo \] . \[ GetTreeListByNode \]   
![None.gif][](  
![None.gif][]     @type\_id   **int**   \-- 给定节点标识   
![None.gif][] )  
![None.gif][] AS   
![None.gif][] declare   @lft   **int**   
![None.gif][] declare   @rgt   **int**   
![None.gif][] if   exists  ( select   **1**   from  tree  where  type\_id = @type\_id )  
![None.gif][]     begin   
![None.gif][]         select   @lft = lft, @rgt = rgt  from  tree  where  type\_id = @type\_id   
![None.gif][]         select   \*   from  TreeView  where  lft  between   @lft   and   @rgt   order   by  lft  asc   
![None.gif][]     end   
![None.gif][] go   
![None.gif][]

现在，我们使用上面的存储过程来列表节点“食品”及其所有子孙节点，查询结果如下：

<table style="margin:0px; padding:0px; border-collapse:collapse; border-spacing:0px; border:medium none; word-break:break-word; color:rgb(0,0,0); font-family:微软雅黑; font-size:13.92px; line-height:20.88px"> 
 <tbody style="margin:0px; padding:0px"> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:0cm 5.4pt; border:0.5pt solid windowtext; border-collapse:collapse; width:3cm"> 
    <div style="margin:0px; padding:0px">
      Type_id 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:0.5pt 0.5pt 0.5pt medium; border-style:solid solid solid none; border-top-color:windowtext; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:3cm"> 
    <div style="margin:0px; padding:0px">
      Name 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:0.5pt 0.5pt 0.5pt medium; border-style:solid solid solid none; border-top-color:windowtext; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      Lft 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:0.5pt 0.5pt 0.5pt medium; border-style:solid solid solid none; border-top-color:windowtext; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      Rgt 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:0.5pt 0.5pt 0.5pt medium; border-style:solid solid solid none; border-top-color:windowtext; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      Layer 
    </div> </td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt; border-style:none solid solid; border-right-color:windowtext; border-bottom-color:windowtext; border-left-color:windowtext; border-collapse:collapse; width:3cm"> 
    <div style="margin:0px; padding:0px">
      2 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:3cm"> 
    <div style="margin:0px; padding:0px">
      食品 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      2 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      11 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      2 
    </div> </td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt; border-style:none solid solid; border-right-color:windowtext; border-bottom-color:windowtext; border-left-color:windowtext; border-collapse:collapse; width:3cm"> 
    <div style="margin:0px; padding:0px">
      3 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:3cm"> 
    <div style="margin:0px; padding:0px">
      肉类 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      3 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      6 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      3 
    </div> </td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt; border-style:none solid solid; border-right-color:windowtext; border-bottom-color:windowtext; border-left-color:windowtext; border-collapse:collapse; width:3cm"> 
    <div style="margin:0px; padding:0px">
      4 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:3cm"> 
    <div style="margin:0px; padding:0px">
      猪肉 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      4 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      5 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      4 
    </div> </td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt; border-style:none solid solid; border-right-color:windowtext; border-bottom-color:windowtext; border-left-color:windowtext; border-collapse:collapse; width:3cm"> 
    <div style="margin:0px; padding:0px">
      5 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:3cm"> 
    <div style="margin:0px; padding:0px">
      蔬菜类 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      7 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      10 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      3 
    </div> </td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt; border-style:none solid solid; border-right-color:windowtext; border-bottom-color:windowtext; border-left-color:windowtext; border-collapse:collapse; width:3cm"> 
    <div style="margin:0px; padding:0px">
      6 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:3cm"> 
    <div style="margin:0px; padding:0px">
      白菜 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      8 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      9 
    </div> </td> 
   <td style="margin:0px; padding:0cm 5.4pt; border-width:medium 0.5pt 0.5pt medium; border-style:none solid solid none; border-right-color:windowtext; border-bottom-color:windowtext; border-collapse:collapse; width:2cm"> 
    <div style="margin:0px; padding:0px">
      4 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

采用左右值编码的设计方案，在进行类别树的遍历时，由于只需进行 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 \]   
![None.gif][](  
![None.gif][]     @type\_id   **int** ,  
![None.gif][]     @name   **varchar** ( **50** )  
![None.gif][])  
![None.gif][] AS   
![None.gif][] declare   @rgt   **int**   
![None.gif][] if   exists  ( select   **1**   from  tree  where  type\_id = @type\_id )  
![None.gif][]     begin   
![None.gif][]        SET  XACT\_ABORT  ON   
![None.gif][]        BEGIN   TRANSACTION   
![None.gif][]         select   @rgt = rgt  from  tree  where  type\_id = @type\_id   
![None.gif][]         update  tree  set  rgt = rgt \+ **2**   where  rgt >= @rgt   
![None.gif][]         update  tree  set  lft = lft \+ **2**   where  lft >= @rgt   
![None.gif][]         insert   into  tree (name,lft,rgt)  values  ( @name , @rgt , @rgt \+ **1** )      
![None.gif][]         COMMIT   TRANSACTION   
![None.gif][]        SET  XACT\_ABORT  OFF       
![None.gif][]     end   
![None.gif][] go   
![None.gif][]

然后，我们删除节点“电视机”，再来看看该树会变成什么情况：

1商品20-2

+-----------------------------------+

2食品13                                14电器19-2  
          +-----------------+                  
       3肉类8          9蔬菜类12              17-2电冰箱18-2  
    +----------+

4猪肉5  6牛肉7  10白菜11

相应的存储过程如下：

![None.gif][] CREATE   PROCEDURE   \[ dbo \] . \[ DelNode \]    
![None.gif][]     @type\_id   **int**   
![None.gif][] AS   
![None.gif][] declare   @lft   **int**   
![None.gif][] declare   @rgt   **int**   
![None.gif][] if   exists  ( select   **1**   from  tree  where  type\_id = @type\_id )  
![None.gif][]     begin   
![None.gif][]        SET  XACT\_ABORT  ON   
![None.gif][]        BEGIN   TRANSACTION   
![None.gif][]         select   @lft = lft, @rgt = rgt  from  tree  where  type\_id = @type\_id   
![None.gif][]         delete   from  tree  where  lft >= @lft   and  rgt <= @rgt   
![None.gif][]         update  tree  set  lft = lft \- ( @rgt \- @lft \+ **1** )  where  lft > @lft   
![None.gif][]         update  tree  set  rgt = rgt \- ( @rgt \- @lft \+ **1** )  where  rgt > @rgt   
![None.gif][]         COMMIT   TRANSACTION   
![None.gif][]        SET  XACT\_ABORT  OFF       
![None.gif][] 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，正好和节点左右值需要调整的幅度相等。由此规律，我们可以编写出类似下面的存储过程来实现节点同层前移的功能：

![None.gif][] CREATE   PROCEDURE   \[ dbo \] . \[ MoveNodeUp \]    
![None.gif][]     @type\_id   **int**   
![None.gif][] AS   
![None.gif][] declare   @lft   **int**   
![None.gif][] declare   @rgt   **int**   
![None.gif][] declare   @layer   **int**   
![None.gif][] if   exists  ( select   **1**   from  tree  where  type\_id = @type\_id )  
![None.gif][]     begin   
![None.gif][]        SET  XACT\_ABORT  ON   
![None.gif][]        BEGIN   TRANSACTION   
![None.gif][]         select   @lft = lft, @rgt = rgt, @layer = layer  from  TreeView  where  type\_id = @type\_id   
![None.gif][]         if   exists  ( select   \*   from  TreeView  where  rgt = @lft \- **1**   and  layer = @layer )  
![None.gif][]            begin   
![None.gif][]                declare   @brother\_lft   **int**   
![None.gif][]                declare   @brother\_rgt   **int**   
![None.gif][]                select   @brother\_lft = lft, @brother\_rgt = rgt  from  TreeView  where  rgt = @lft \- **1**   and  layer = @layer   
![None.gif][]                update  tree  set  lft = lft \- ( @brother\_rgt \- @brother\_lft \+ **1** )  where  lft >= @lft   and  rgt <= @rgt   
![None.gif][]                update  tree  set  lft = lft \+ ( @rgt \- @lft \+ **1** )  where  lft >= @brother\_lft   and  rgt <= @brother\_rgt   
![None.gif][]                update  tree  set  rgt = rgt \- ( @brother\_rgt \- @brother\_lft \+ **1** )  where  rgt > @brother\_rgt   and  rgt <= @rgt   
![None.gif][]                update  tree  set  rgt = rgt \+ ( @rgt \- @lft \+ **1** )  where  lft >= @brother\_lft \+ ( @rgt \- @lft \+ **1** )  and  rgt <= @brother\_rgt   
![None.gif][]            end   
![None.gif][]         COMMIT   TRANSACTION   
![None.gif][]        SET  XACT\_ABORT  OFF       
![None.gif][]     end

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

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

最后，我对上面这种左右值编码实现无限分级类别树的方案做一个总结：

优点：在消除递归的前提下实现了无限分级，而且查询条件是基于整形数字比较的，效率很高。可以进行先序列表，添加，修改，删除，同层平移等常规操作，基本满足需求。

缺点：由于这种左右值编码的方式和常见的阿拉伯数字直观排序不同，再加上节点在树中的层次，顺序不是直观显示出来，而必须通过简单的公式计算后得到，需要花费一定的时间对其数学模型进行深入理解。而且，采用该方案编写相关存储过程，新增，删除，同层平移节点需要对整个树进行查询修改，由此导致的代码复杂度，耦合度较高，修改维护的风险较高。

[None.gif]: /images/20220717/2a442590c451462ca0168d6ef57acb04.png