用预排序遍历树保存上下级关系

原文链接：https://blog.scorpii.net/2019/11/11/mptt/

这个方法还是刚哥教我的，当时觉得这才叫写代码啊。

准备

例子

这里我们假设有一个分销员场景，分销员的层级关系如上图。

我们的表结构如下：

CREATE TABLE mptt
(
    `id` int(11) not null auto_increment,
    `name` varchar(25) comment '分销员名称',
    `left` int(11) comment '左值',
    `right` int(11) comment '右值',
    `level` int(11) comment '层级',
    `amount` int(11) comment '业绩',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我特意加了业绩字段，用来模拟之后业绩求和的场景。

我们按照上图的字母顺序一个一个将数据插入。

插入

其实插入节点就两种情况，一个是插入根节点，一个是插入子节点。我们分别将这两种情况写成存储过程，有兴趣的将它翻译成其他语言就能拿来用了：

# 插入根节点
DELIMITER //
create procedure add_node
( in name varchar(255), in amount int(11) )
begin
set @left=(select ifnull(max(`right`), 0) from mptt) + 1;
insert into mptt (`name`, `left`, `right`, `level`, `amount`) values(name, @left, @left+1, 1, amount);
end //

# 插入子节点
DELIMITER //
create procedure add_sub_node
( in name varchar(255), in amount int(11), in parent_id int(11) )
begin
select @parent_left := `left`, @parent_level := `level` from mptt where `id`=parent_id;
update mptt set `left`=`left`+2 where `left`>@parent_left;
update mptt set `right`=`right`+2 where `right`>@parent_left;
insert into mptt (`name`, `left`, `right`, `level`, `amount`) values(name, @parent_left+1, @parent_left+2, @parent_level+1, amount);
end //

我们按照上图插入节点

call add_node('A', 6);
call add_sub_node('B', 3, (select id from mptt where name='A'));
call add_sub_node('C', 5, (select id from mptt where name='B'));
call add_sub_node('D', 10, (select id from mptt where name='A'));
call add_sub_node('E', 11, (select id from mptt where name='D'));
call add_sub_node('F', 50, (select id from mptt where name='B'));

此时表中的数据是这样的：

表

查询

当我们需要统计A分销员所有下级的业绩之和时，我们只需要输入一条sql即可查询出来，这比用parent_id的方式方便多了。

select @left := `left`, @right := `right` from mptt where name='A';
select sum(amount) from mptt where `left` > @left and `right` < @right

结果

当我们只统计A分销员直属下级的销售额之和时，level字段起了左右。

select @left := `left`, @right := `right`, @level := `level` from mptt where name='A';
select sum(amount) from mptt where `left` > @left and `right` < @right and `level` = @level+1

结果

修改

这里的修改是指将E分销员截出并加入B、C分销员之间，或者将B分销员以及它下面的整个子书截出并且放到E分销员的下面等等。稍微想一下就能知道实现起来不是不可能，只是很复杂。这个之后慢慢研究。

总结

实际上这个方法可以支持无限层级，就算按照规定分销员最多只能三级，那也可以用这个方法来做无限层级的菜单或者省市区表什么的。反正总是有地方可以用到的。