mysql查询树形结构数据库设计,树形结构的数据库表Schema设计

那么某个节点到底有多少的子孙节点呢？经过该节点的左、右值咱们能够将其子孙节点圈进来，则子孙总数 = (右值 – 左值– 1) / 2，以Fruit为例，其子孙总数为：(11 –2 – 1) / 2 = 4。同时，为了更为直观地展示树形结构，咱们须要知道节点在树中所处的层次，经过左、右值的SQL查询便可实现，以Fruit为例：SELECTCOUNT(*) FROM Tree WHERE Lft <= 2 AND Rgt >=11。为了方便描述，咱们能够为Tree创建一个视图，添加一个层次数列，该列数值能够写一个自定义函数来计算，函数定义以下：

CREATE FUNCTIONdbo.CountLayer

(@node_id int)RETURNS int

AS

begin

declare @result int

set @result = 0

declare @lft int

declare @rgt int

if exists(select Node_id from Tree where Node_id = @node_id)begin

select @lft = Lft, @rgt = Rgt from Tree where node_id = @node_id

select @result = count(*) from Tree where Lft <= @lft and Rgt >= @rgt

end

return @result

end

GO

基于层次计算函数，咱们建立一个视图，添加了新的记录节点层次的数列：

CREATE VIEWdbo.TreeViewAS

SELECT Node_id, Name, Lft, Rgt, dbo.CountLayer(Node_id) AS Layer FROM dbo.Tree ORDER BYLftGO

建立存储过程，用于计算给定节点的全部子孙节点及相应的层次：

CREATE PROCEDURE [dbo].[GetChildrenNodeList](@node_id int)AS

declare @lft int

declare @rgt int

if exists(select Node_id from Tree where node_id = @node_id)begin

select @lft = Lft, @rgt = Rgt from Tree where Node_id = @node_id

select * from TreeView where Lft between @lft and @rgt order by Lft ASC

end

GO

如今，咱们使用上面的存储过程来计算节点Fruit全部子孙节点及对应层次，查询结果以下：

从上面的实现中，咱们能够看出采用左右值编码的设计方案，在进行树的查询遍历时，只须要进行2次数据库查询，消除了递归，再加上查询条件都是数字的比较，查询的效率是极高的，随着树规模的不断扩大，基于左右值编码的设计方案将比传统的递归方案查询效率提升更多。固然，前面咱们只给出了一个简单的获取节点子孙的算法，真正地使用这棵树咱们须要实现插入、删除同层平移节点等功能。

(2)获取某节点的族谱路径

假定咱们要得到某节点的族谱路径，则根据左、右值分析只须要一条SQL语句便可完成，以Fruit为例：SELECT* FROM Tree WHERE Lft < 2 AND Rgt > 11 ORDER BY Lft ASC ，相对完整的存储过程：

CREATE PROCEDURE [dbo].[GetParentNodePath](@node_id int)AS

declare @lft int

declare @rgt int

if exists(select Node_id from Tree where Node_id = @node_id)begin

select @lft = Lft, @rgt = Rgt from Tree where Node_id = @node_id

select * from TreeView where Lft < @lft and Rgt > @rgt order by Lft ASC

end

GO

(3)为某节点添加子孙节点

假定咱们要在节点“Red”下添加一个新的子节点“Apple”，该树将变成以下图所示，其中红色节点为新增节点。

仔细观察图中节点左右值变化，相信你们都应该可以推断出如何写SQL脚本了吧。咱们能够给出相对完整的插入子节点的存储过程：

CREATE PROCEDURE [dbo].[AddSubNode](@node_id int,@node_name varchar(50)

)AS

declare @rgt int

if exists(select Node_id from Tree where Node_id = @node_id)begin

SET XACT_ABORT ON

BEGINTRANSCTIONselect @rgt = Rgt from Tree where Node_id = @node_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(@node_name, @rgt, @rgt + 1)COMMIT TRANSACTION

SET XACT_ABORT OFF

end

GO

(4)删除某节点

若是咱们想要删除某个节点，会同时删除该节点的全部子孙节点，而这些被删除的节点的个数为：(被删除节点的右值 – 被删除节点的左值+ 1) / 2，而剩下的节点左、右值在大于被删除节点左、右值的状况下会进行调整。来看看树会发生什么变化，以Beef为例，删除效果以下图所示。

则咱们能够构造出相应的存储过程：

CREATE PROCEDURE [dbo].[DelNode](@node_id int)AS

declare @lft int

declare @rgt int

if exists(select Node_id from Tree where Node_id = @node_id)begin

SET XACT_ABORT ON

BEGINTRANSCTIONselect @lft = Lft, @rgt = Rgt from Tree where Node_id = @node_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

GO

5、总结

咱们能够对这种经过左右值编码实现无限分组的树形结构Schema设计方案作一个总结：

(1)优势：在消除了递归操做的前提下实现了无限分组，并且查询条件是基于整形数字的比较，效率很高。

(2)缺点：节点的添加、删除及修改代价较大，将会涉及到表中多方面数据的改动。

固然，本文只给出了几种比较常见的CRUD算法的实现，咱们一样能够本身添加诸如同层节点平移、节点下移、节点上移等操做。有兴趣的朋友能够本身动手编码实现一下，这里不在列举了。值得注意的是，实现这些算法可能会比较麻烦，会涉及到不少条update语句的顺序执行，若是顺序调度考虑不周详，出现Bug的话将会对整个树形结构表产生惊人的破坏。所以，在对树形结构进行大规模修改的时候，能够采用临时表作中介，以下降代码的复杂度，同时，强烈推荐在作修改以前对表进行完整备份，以备不时之需。在以查询为主的绝大多数基于数据库的应用系统中，该方案相比传统的由父子继承关系构建的数据库Schema更为适用。