[TREE]采用左右值编码来存储无限分级树形结构的数据库表设计
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
采用左右值编码来存储无限分级树形结构的数据库表设计
之前我介绍过一种按位数编码保存树形结构数据的表设计方法，详情见：浅谈数据库设计技巧(上)
该设计方案的优点是：只用一条查询语句即可得到某个根节点及其所有子孙节点的先序遍历。由于消除了递归，在数据记录量较大时，可以大大提高列表效率。但是，这种编码方案由于层信息位数的限制，限制了每层能所允许的最大子节点数量及最大层数。同时，在添加新节点的时候必须先计算新节点的位置是否超过最大限制。
上面的设计方案必须预先设定类别树的最大层数以及最大子节点数，不是无限分级，在某些场合并不能采用，那么还有更完美的解决方案吗？通过google的搜索，我又探索到一种全新的无递归查询，无限分级的编码方案——左右值。原文的程序代码是用php写的，但是通过仔细阅读其数据库表设计说明及相关的sql语句，我彻底弄懂了这种巧妙的设计思路，并在这种设计中新增了删除节点，同层平移的需求（原文只提供了列表及插入子节点的sql语句）。
下面我力图用比较简短的文字，少量图表，及相关核心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 (select1from 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 ORDE R BY lft
GO
(
)
AS declare declare if
go
假定我们要在节点“肉类”下添加一个子节点“牛肉”，该树将变成：
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牛肉78+2白菜9+2
看完上图相应节点左右值的变化后，相信大家都知道该如何写相应的sql脚本吧？下面我给出相对完整的插入子节点的存储过程：
CREATE PROCEDURE[dbo].[AddSubNodeByNode]
(
@type_id int,
@name varchar(50)
)
AS
declare@rgt int
if exists (select1from 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+2where rgt>=@rgt
update tree set lft=lft+2where lft>=@rgt
insert into tree (name,lft,rgt) values (@name,@rgt,@rgt+1)
COMMIT TRANSACTION
SET XACT_ABORT OFF
end
然后，我们删除节点“电视机”，再来看看该树会变成什么情况：
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 (select1from 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