大开眼界，一种新的树形表结构设计方法？

神奇！！一种新型的树形表结构设计

场景例子

假如我们想存储这样的数据，表结构是不是只能想到了 id parent_id 的组合，与之对应的表数据(department)：

部门表结构（department）

但是问题来了？

但是问题来了，这种结构如果要想查出一个节点下所有的子节点，必须使用递归查询，在树层数很多的场景下，就很难保证效率了，今天我看了一个设计《改进后的先序树遍历》(竟然是2002年的老东西)，就像一棒子抡在我的头上，详情如下： 针对每个节点，我们分配好ID，

我们从根节点开始，给董事长左值设为1，下级部门总经理左值设为2，以此类推地沿着边缘开始遍历，给每个节点加上左值，遇到叶子节点处给节点加上右值，再继续向上沿着边缘继续遍历，遍历结束回到根节点右侧,你将得到类似这样的结构。

遍历完后每一个节点都有与之对应的左右值。 这个时候可以去除parent_id字段，添加lft,rgt，来存储左右值。

数据和结构准备完毕，我们来试试操作解决上面的需求~

查出所有子孙部门

SQL Schema

SET @lft := 9;
SET @rgt := 18;
SELECT * FROM department WHERE lft BETWEEN @lft AND @rgt ORDER BY lft ASC;

查询子孙部门总数

公式：总数 = (右值 - 左值 - 1) / 2

SQL Schema

行政总监的子孙部门数 = (18 - 9 - 1) / 2 = 4

董事长的子孙部门数 = (20 - 1 - 1) / 2 = 9

会计的子部门数 =  (14 - 13 - 1) / 2 = 0

可以数数看，确实没错哦～

判断是否叶子节点

通过有了上述计算公式算总数的经验后，现在判断是否叶子节点，有的小伙伴已经知道了怎么做，那就是： 右值 - 1 == 左值那他就是叶子节点，或者左值 + 1 == 右值那他就是叶子节点，反之则不是叶子节点。 例如： 设计部，5 - 1 == 4，因此他是叶子节点。 董事长，20 - 1 != 1，因此他不是叶子节点。 至此已经完美的解决了上述需求问题，接下来再尝试一下业务的基本操作。

新增部门

当新增一个部门时，需要对新增节点位置的后续边缘进行加2操作，因为每一个节点有左右两个数值。这个操作通常需要放到事务中进行处理。 例如：在研发部门下添加一个新部门：

对应sql：

SQL Schema

SET @lft := 7;/*新部门的左值*/
SET @rgt := 8;/*新部门的左值*/
SET @level := 5;/*新部门的层级*/
begin;
/*将插入的后续边缘的节点左右数+2*/
UPDATE department SET lft=lft+2 WHERE lft > @lft;
UPDATE department SET rgt=rgt+2 WHERE rgt >= @lft;
/*插入数据*/
INSERT INTO department(name,lft,rgt,level) VALUES('新部门',@lft,@rgt,level);
/*新增影响行数为0时，必须回滚*/
commit;
/*rollback;*/

删除部门

删除部门与新增部门类似，不同的是需要对删除节点的后续边缘节点减2操作。 例如：删除刚刚添加的新部门：

对应sql：

SQL Schema

SET @lft := 7;/*要删除的节点左值*/
SET @rgt := 8;/*要删除的节点右值*/
begin;
UPDATE department SET lft=lft-2 WHERE lft > @lft;
UPDATE department SET rgt=rgt-2 WHERE rgt > @lft;

/*删除节点*/
DELETE FROM department WHERE lft=@lft AND rgt=@rgt;
/*删除影响行数为0时，必须回滚*/
commit;
/*rollback*/

查询直接子部门

查询某部门的直接子部门（即不包含孙子部门），例如：查询总经理下的直接子部门。正常需要返回产品部和行政总监

对应sql：

SQL Schema

SET @level := 2;/*总经理的level*/
SET @lft := 2;/*总经理的左值*/
SET @rgt := 19;/*总经理的右值*/

SELECT * FROM department WHERE lft > @lft AND rgt < @rgt AND level = @level+1;

查询祖链路径

查询某部门的祖链路径。 例如：查询产品部的祖链路径，正常需要返回董事长,总经理

SQL Schema

SET @lft := 3;/*产品部左值*/
SET @rgt := 8;/*产品部右值*/

SELECT * FROM department WHERE lft < @lft AND rgt > @rgt ORDER BY lft ASC;

最后总结一下： 在我目前看来，这个方法的唯一缺点就是，每一次的新增或删除，操作节点的后续边缘走到的节点都要加/减2操作。

本文由 mdnice 多平台发布