前序遍历(nested model)树算法笔记

最新推荐文章于 2023-10-18 15:39:47 发布

置顶 msljava

最新推荐文章于 2023-10-18 15:39:47 发布

阅读量1k

点赞数

分类专栏： database 文章标签：数据结构算法 nested model

本文链接：https://blog.csdn.net/msljava/article/details/38110027

版权

database 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

环境说明：

mysql 5.5.32-log

tools: sqlyog (可选)

准备 sql 语句

/*declare table construct   定义表结构   lft左值  rgt右值  removed移动标志位*/
DROP TABLE IF EXISTS `nested_category`;

CREATE TABLE `nested_category` (
  `title` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `parent` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `rgt` int(11) DEFAULT NULL,
  `weight` int(11) DEFAULT NULL,
  `cn_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `removed` tinyint(4) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `nested_category`  导入初始数据 */

LOCK TABLES `nested_category` WRITE;

insert  into `nested_category`(`title`,`parent`,`lft`,`rgt`,`weight`,`cn_name`,`removed`) values ('Banana','Yello',8,9,NULL,'香蕉',0),('Beef','Meat',13,14,NULL,'牛肉',0),('Cherry','Red',4,5,NULL,'樱桃',0),('Food',NULL,1,18,NULL,'食物',0),('Fruit','Food',2,11,NULL,'水果 ',0),('Meat','Food',12,17,NULL,'肉',0),('Pork','Meat',15,16,NULL,'猪肉',0),('Red','Fruit',3,6,NULL,'红',0),('Yello','Fruit',7,10,NULL,'黄色',0);

UNLOCK TABLES;

备份与恢复

#复制表结构
CREATE TABLE `nested_category_origi` LIKE `nested_category`;

#复制表结构及数据
CREATE TABLE `nested_category_origi` SELECT * FROM `nested_category`;
#----------------恢复 begin ---------------
SET autocommit=0;
START TRANSACTION;

/*
#去掉外外键约束  当自关联时有用
UPDATE `nested_category`
SET `parent` = NULL;
*/
 
DELETE FROM `nested_category`;

INSERT INTO `nested_category`
(     `title`,
        `parent`,
        `lft`,
        `rgt`,
        `weight`,
        `cn_name`,
        `removed`
)
(
     SELECT
       `title`,
       `parent`,
       `lft`,
       `rgt`,
       `weight`,
       `cn_name`,
       `removed`
     FROM `nested_category_origi`
     #一定要有这个排序，要不外键依赖有问题
     ORDER BY lft ASC
)
ON DUPLICATE KEY UPDATE
     parent = VALUES(parent),
     lft = VALUES(lft),
     rgt = VALUES(rgt),
     weight = VALUES(weight),
     cn_name = VALUES(cn_name),
     removed = VALUES(removed)
;

COMMIT;
#ROLLBACK;
SET autocommit=1;
#----------------恢复 end---------------

数据结构及算法原理：

我们先把树按照水平方式摆开。从根节点开始（“Food”），然后他的左边写上1 。然后按照树的顺序（从上到下）给“Fruit”的左边写上2。这样，你沿着树的边界走啊走（这就是“遍历”），然后同时在每个节点的左边和右边写上数字。最后，我们回到了根节点“Food”在右边写上18。下面是标上了数字的树，同时把遍历的顺序用箭头标出来了。

遍历的次序是：

1 从树的根结点出发，先访问结点左值。

2 递归子结点左值。

3 遍历到树叶结点(rgt-lft=2)时,访问本结点的右值

4 查看是否有兄弟结点，若有按 2,3操作

5 递归退回父结点，若有未访问的子树时，按2,3,4 ，否则就访问结点的右值，并一直回退至根结点的右值,整个过程结束。

结点间的关系比如说 a结点与b结点有4种关系

a左b右 -> a.rgt<b.lft

a右b左 -> b.rgt<a.lft

a祖b孙 -> b.lft between a.lft and a.rgt

b祖a孙 -> a.lft between b.lft and b.rgt

简单的说就是：左路结点.rgt<右结点.lft | 子孙结点.lft between 祖先结点.lft and 祖先结点.rgt

我们称这些数字为左值和右值（如，“Food”的左值是1，右值是18）。正如你所见，这些数字按时了每个节点之间的关系。因为“Red”有3和6两个值，所以，它是有拥有1-18值的“Food”节点的后续。同样的，我们可以推断所有左值大于2并且右值小于11的节点，都是有2-11的“Fruit” 节点的后续。这样，树的结构就通过左值和右值储存下来了。这种数遍整棵树算节点的方法叫做“改进前序遍历树”算法。

表数据如下设计：

核心算法SQL语句

下面是比较常用的查询方法

#每个结点与祖先或自己结点的连接关系  这个很关键
SELECT node.title,node.lft,node.rgt, (parent.title) AS parent_title,parent.lft AS parent_lft,parent.rgt AS parent_rgt
FROM nested_category node, nested_category parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
order by node.lft asc ;

#前序查询树结点，按title分组，lft排序，node.title出现次数就是本结点树深度
SELECT node.title,MAX(node.lft) AS lft,MAX(node.rgt) AS rgt, MAX(parent.title) AS parent_title,MAX(parent.lft) AS parent_lft,MAX(parent.rgt) AS parent_rgt,COUNT(1) AS deepth
FROM nested_category node, nested_category parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.title
ORDER BY node.lft ASC;

#查某个子树的结构 指定一个父结点
SELECT node.title,MAX(node.lft) AS lft,MAX(node.rgt) AS rgt, MAX(parent.title) AS parent_title,MAX(parent.lft) AS parent_lft,MAX(parent.rgt) AS parent_rgt
FROM nested_category node, nested_category parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.`title`='Food'
GROUP BY node.title
ORDER BY node.lft;

#查找祖先
SELECT parent.title,parent.cn_name,parent.lft
FROM nested_category node, nested_category parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
       AND node.title = 'Banana'
ORDER BY parent.lft ASC

插入逻辑: 在父结点下新增一个结点

1 计算父结点右值( @currnt_right)

2 修改调整相关结点自增2(一个结点的子树宽度就是 rgt-lft+1=2) when lft> @currnt_right then set lft=lft+2 when rgt>= @currnt_right then set rgt=rgt+2

3 插入新记录行 set lft= @currnt_right set rgt= @currnt_right+1

4 修改父结点右值自增2 set parent.rgt= parent.rgt+2

#  ---------插入结点  begin ------------------------

SET autocommit=0;
START TRANSACTION;

#表加写锁
LOCK TABLES nested_category WRITE;

#要插入的父结点title
SET @current_title = 'Red';
SET @new_titile='Lichee',@new_cn_name='荔枝';
#set @new_titile='Tomato',@new_cn_name='西红柿';

#设置当前结点右值变量
SELECT @currnt_right:=rgt FROM nested_category WHERE title = @current_title;


#更新右值比@currnt_right大的，全部+2
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @currnt_right;

#更新右值比@currnt_right大的，全部+2
UPDATE nested_category SET lft = lft + 2 WHERE lft > @currnt_right;

#插入新记录，左传=@currnt_right(原父结点的右值)   右值=@currnt_right+1
INSERT INTO `nested_category`
(`title`,`parent`,`lft`,`rgt`,`weight`,`cn_name`)
VALUES (@new_titile,@current_title,@currnt_right,(@currnt_right+1),NULL,@new_cn_name);

#原父结点的右值自增2
UPDATE nested_category SET rgt = (@currnt_right+2) WHERE rgt = @currnt_right;

#释放表锁
UNLOCK TABLES;

#commit;
ROLLBACK;
SET autocommit=1;

#  ---------插入结点  end ------------------------

删除操作逻辑:删除是删除指定结点及其下属子树

1 用变量保存待删除结点的左(@myLeft)右值(@myRight)，及计算出子树的宽度 @myWidth=rgt-lft+1 .

2 删除本结点及下属子结点 lft between @myLeft and @myRight

3 调整余下相关结点，自增子树宽度. when lft>@myRight then set lft=lft+@myWidth when rgt>=@myRight then set rgt=rgt+@myWidth

#  ---------删除结点  begin ------------------------

SET autocommit=0;
START TRANSACTION;


#表加写锁
LOCK TABLES nested_category WRITE;

SET @current_title='Lichee';

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
  FROM nested_category
WHERE title = @current_title;

#删除本结点及子树结点
DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;


#释放表锁
UNLOCK TABLES;

#commit;
ROLLBACK;
SET autocommit=1;

#  ---------删除结点  end ------------------------

移动一棵子树，从本质来讲就是删除旧子树，在目标结点下追加新子树（新子树内部结构与旧子树相同）。移动后source与target是父子关系(source.lft between target.lft and target.rgt)

这里的子树，特例是子树只有一个结点，就等同于删除一个结点，在目标结点下面追加一个新结点，跟上面的操作是一样的

上面的新增与删除都是子树的特例（只有一个结点的树默认宽度就是2）

子树操作中宽度(就是子树根结点的宽度)这个概念很重要逻辑: node_width=rgt-lft+1 (树枝结点宽度>2 树叶结点宽度==2)

移动的偏移量(target.rgt-source.lft)，移动有三种情况，左移右，右移左，子移祖。子树内部结构不变，但是全部子树内的结点lft,rgt都要修改加上偏移量。

操作逻辑：

1 计算子树宽度 @sub_tree_width=source.rgt - source.lft + 1

2 修改子树各结点删除标志位为1，假删除 removed=true（默认是false）

3 删除源子树后,剩下树各个结点都要调整(剔除原子树 removed=true)左值右值 when rgt>=source.rgt then -> set rgt=rgt-@sub_tree_width ; when lft>=source.rgt then -> set lft=lft-@sub_tree_widthset lft=lft-@sub_tree_width

4 计算位移偏移量 @shift_diff=target.rgt - source.lft,获取目标结点的右值. （移动后结果是源子树的lft就是目标的rgt ，而子树中各结点lft rgt都要自增偏移量， rgt>=目标rgt or lft>目标rgt:自增子树的宽度。

5 修改受新增子树后受影响的结点的左值右值 when rgt>=target.rgt then -> set rgt=rgt+@sub_tree_width ; when lft>=target.rgt then -> set lft=lft+@sub_tree_widthlft=lft+@sub_tree_width

6 按偏移量更新原子树(removed=true)的左值右值 when removed=TRUE and (lft between source.lft and source.rgt) then -> set lft=lft+@shift_diff , rgt=rgt+@shift_diff

7 更新source.parent=target.id (可选)

#  --------移动子树  begin ------------------------


#表加写锁
LOCK TABLES nested_category WRITE;

SET @source_node_pk='Yello',@target_node_pk='Meat';
SET @sub_tree_width=0,@source_lft=0,@source_rgt=0,@target_new_rgt=0,@shift_diff=0;

SET autocommit=0;
START TRANSACTION;

#1 计算子树宽度 @sub_tree_width=source.rgt - source.lft + 1
SELECT (node.`rgt`-node.`lft`+1),node.`lft`,node.`rgt` INTO @sub_tree_width,@source_lft,@source_rgt
FROM `nested_category` node
WHERE node.`title`=@source_node_pk;

#2 修改子树各结点删除标志位为1，假删除 removed=true（默认是false）
UPDATE `nested_category`
SET removed=TRUE
WHERE lft BETWEEN @source_lft AND @source_rgt;

#3 删除源子树后,剩下树各个结点都要调整(剔除原子树 removed=true)左值右值      when rgt>=source.rgt    then -> set rgt=rgt-@sub_tree_width  ; when lft>=source.rgt    then -> set lft=lft-@sub_tree_width
UPDATE nested_category 
SET rgt = rgt - @sub_tree_width 
WHERE rgt > @source_rgt AND removed=FALSE;

UPDATE nested_category 
SET lft = lft - @sub_tree_width 
WHERE lft > @source_rgt AND removed=FALSE;

#4 计算位移偏移量 @shift_diff=target.rgt - source.lft,获取目标结点的右值. （移动后结果是源子树的lft就是目标的rgt ，而子树中各结点lft rgt都要自增偏移量，  rgt>=目标rgt or lft>目标rgt:自增子树的宽度。
SELECT SUM(CASE title 
       WHEN @source_node_pk THEN -1*lft
       WHEN @target_node_pk THEN rgt
       ELSE 0
       END) INTO @shift_diff
FROM nested_category ;

#目标rgt因为上面删除操作而调整过rgt，重新获取该值
SELECT rgt INTO @target_new_rgt
FROM `nested_category` 
WHERE title=@target_node_pk;


#5 修改受新增子树后受影响的结点的左值右值   when rgt>=target.rgt     then -> set rgt=rgt+@sub_tree_width  ; when lft>=target.rgt    then -> set lft=lft+@sub_tree_width
UPDATE nested_category 
SET rgt=rgt+@sub_tree_width
WHERE rgt >= @target_new_rgt  AND removed=FALSE;

UPDATE nested_category 
SET lft=(lft+@sub_tree_width)
WHERE lft > @target_new_rgt AND removed=FALSE;


#6 按偏移量更新原子树(removed=true)的左值右值      when removed=TRUE and (lft between source.lft and source.rgt)     then -> set lft=lft+@shift_diff , rgt=rgt+@shift_diff
UPDATE nested_category 
SET lft=lft+@shift_diff,
    rgt=rgt+@shift_diff,
    removed=FALSE
WHERE removed=TRUE;


#7 更新source.parent=target.id  (可选)
UPDATE `nested_category`
SET parent=@target_node_pk
WHERE title=@source_node_pk;


#COMMIT;
ROLLBACK;
SET autocommit=1;

#释放表锁
UNLOCK TABLES;
#  --------移动子树  end ------------------------

#  --------移动子树  begin ------------------------


#表加写锁
LOCK TABLES nested_category WRITE;

SET @source_node_pk='Yello',@target_node_pk='Meat';
SET @sub_tree_width=0,@source_lft=0,@source_rgt=0,@target_new_rgt=0,@shift_diff=0;

SET autocommit=0;
START TRANSACTION;

#1 计算子树宽度 @sub_tree_width=source.rgt - source.lft + 1
SELECT (node.`rgt`-node.`lft`+1),node.`lft`,node.`rgt` INTO @sub_tree_width,@source_lft,@source_rgt
FROM `nested_category` node
WHERE node.`title`=@source_node_pk;

#2 修改子树各结点删除标志位为1，假删除 removed=true（默认是false）
UPDATE `nested_category`
SET removed=TRUE
WHERE lft BETWEEN @source_lft AND @source_rgt;

#3 删除源子树后,剩下树各个结点都要调整(剔除原子树 removed=true)左值右值      when rgt>=source.rgt    then -> set rgt=rgt-@sub_tree_width  ; when lft>=source.rgt    then -> set lft=lft-@sub_tree_width
UPDATE nested_category 
SET rgt = rgt - @sub_tree_width 
WHERE rgt > @source_rgt AND removed=FALSE;

UPDATE nested_category 
SET lft = lft - @sub_tree_width 
WHERE lft > @source_rgt AND removed=FALSE;

#4 计算位移偏移量 @shift_diff=target.rgt - source.lft,获取目标结点的右值. （移动后结果是源子树的lft就是目标的rgt ，而子树中各结点lft rgt都要自增偏移量，  rgt>=目标rgt or lft>目标rgt:自增子树的宽度。
SELECT SUM(CASE title 
       WHEN @source_node_pk THEN -1*lft
       WHEN @target_node_pk THEN rgt
       ELSE 0
       END) INTO @shift_diff
FROM nested_category ;

#目标rgt因为上面删除操作而调整过rgt，重新获取该值
SELECT rgt INTO @target_new_rgt
FROM `nested_category` 
WHERE title=@target_node_pk;


#5 修改受新增子树后受影响的结点的左值右值   when rgt>=target.rgt     then -> set rgt=rgt+@sub_tree_width  ; when lft>=target.rgt    then -> set lft=lft+@sub_tree_width
UPDATE nested_category 
SET rgt=rgt+@sub_tree_width
WHERE rgt >= @target_new_rgt  AND removed=FALSE;

UPDATE nested_category 
SET lft=(lft+@sub_tree_width)
WHERE lft > @target_new_rgt AND removed=FALSE;


#6 按偏移量更新原子树(removed=true)的左值右值      when removed=TRUE and (lft between source.lft and source.rgt)     then -> set lft=lft+@shift_diff , rgt=rgt+@shift_diff
UPDATE nested_category 
SET lft=lft+@shift_diff,
    rgt=rgt+@shift_diff,
    removed=FALSE
WHERE removed=TRUE;


#7 更新source.parent=target.id  (可选)
UPDATE `nested_category`
SET parent=@target_node_pk
WHERE title=@source_node_pk;


#COMMIT;
ROLLBACK;
SET autocommit=1;

#释放表锁
UNLOCK TABLES;
#  --------移动子树  end ------------------------

procedure 存储过程实现方式上面的语句中，语句很多，而且有很多传值的地方，如果使用JAVA一条一条执行，开发效率慢，逻辑结构复杂。

考虑使用存储过程，相当于对JAVA来讲只是API的调用不需要考虑实现细节，分离修改，procedure只要外部接口不改变，内部修改不会影响到JAVA调用，

任何逻辑都内聚（包括业务校验）在过程中，由专门的DB开发人员进行。 但是考虑到有些低版本的5.0.X 5.1.X会不支持存储过程，方案的使用者要充分

考虑这方面的实际情况，如需要就升级到5.5.x版本。

------------------ 新增结点 begin------------------

DELIMITER $$

USE `school`$$

DROP PROCEDURE IF EXISTS `create_nested_node`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `create_nested_node`(IN current_title VARCHAR(128),IN new_titile VARCHAR(128), IN new_cn_name VARCHAR(128))
BEGIN
     DECLARE currnt_right INT DEFAULT 0;
     DECLARE search_current_num INT DEFAULT 0;
         
     SELECT COUNT(1) INTO search_current_num FROM `nested_category` WHERE title = current_title;
    
     IF search_current_num=0 THEN
        signal SQLSTATE '45000' SET message_text = 'current_title not match at less one recore id db!';
     END IF;
     SET autocommit=0;
     START TRANSACTION;
    
     #设置当前结点右值变量
     SELECT rgt INTO currnt_right FROM nested_category WHERE title = current_title;
     #更新右值比@currnt_right大的，全部+2
     UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > currnt_right;
     #更新右值比@currnt_right大的，全部+2
     UPDATE nested_category SET lft = lft + 2 WHERE lft > currnt_right;
     #插入新记录，左传=@currnt_right(原父结点的右值)   右值=@currnt_right+1
     INSERT INTO `nested_category`
     (`title`,`parent`,`lft`,`rgt`,`weight`,`cn_name`)
     VALUES (new_titile,current_title,currnt_right,(currnt_right+1),NULL,new_cn_name);
     #原父结点的右值自增2
     UPDATE nested_category SET rgt = (currnt_right+2) WHERE rgt = currnt_right;
    
     COMMIT;
     #ROLLBACK;
     SET autocommit=1;
    
    
    END$$

DELIMITER ;

------------------ 新增结点 end------------------

---------------- 删除结点 begin--------------------------

DELIMITER $$

USE `school`$$

DROP PROCEDURE IF EXISTS `delete_nested_node`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_nested_node`(IN current_title VARCHAR(64))
BEGIN
   
     DECLARE myLeft INT DEFAULT 0;
     DECLARE myRight INT DEFAULT 0;
     DECLARE myWidth INT DEFAULT 0;
     DECLARE search_current_num INT DEFAULT 0;
     SELECT COUNT(1) INTO search_current_num FROM `nested_category` WHERE title = current_title;
    
     IF search_current_num=0 THEN
        signal SQLSTATE '45000' SET message_text = 'can,t delete the record then not exists in db !';
     END IF;
    
     SET autocommit=0;
     START TRANSACTION;
    
    
     SELECT lft, rgt, (rgt-lft+1) INTO myLeft,myRight,myWidth
       FROM nested_category
     WHERE title = current_title;
     #删除本结点及子树结点
     DELETE FROM nested_category WHERE lft BETWEEN myLeft AND myRight;
     UPDATE nested_category SET rgt = rgt - myWidth WHERE rgt > myRight;
     UPDATE nested_category SET lft = lft - myWidth WHERE lft > myRight;
    
     COMMIT;
     #ROLLBACK;
     SET autocommit=1;
   
   
    END$$

DELIMITER ;

---------------- 删除结点 end--------------------------

---------------- 移动结点 begin--------------------------

DELIMITER $$
USE `school`$$
DROP PROCEDURE IF EXISTS `move_sub_tree`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `move_sub_tree`(IN source_node_pk VARCHAR(32),IN target_node_pk VARCHAR(32))
BEGIN
# declare sub_tree_width,source_lft,source_rgt int default 0;
DECLARE sub_tree_width INT DEFAULT 0;
DECLARE source_lft INT DEFAULT 0;
DECLARE source_rgt INT DEFAULT 0;
DECLARE shift_diff INT DEFAULT 0;
DECLARE target_new_rgt INT DEFAULT 0;
DECLARE search_current_num INT DEFAULT 0;
DECLARE search_target_num INT DEFAULT 0;
DECLARE is_inherit_flag BOOLEAN DEFAULT FALSE;
SELECT COUNT(1) INTO search_current_num FROM `nested_category` WHERE title = source_node_pk;
#若源不存在，抛错
IF search_current_num=0 THEN
  signal SQLSTATE '45000' SET message_text = 'source title is not exists renord ID ';
END IF;
SELECT COUNT(1) INTO search_target_num FROM `nested_category` WHERE title = target_node_pk;
#若目标不存在，抛错
IF search_target_num=0 THEN
  signal SQLSTATE '45000' SET message_text = 'target title is not exists renord, ID !';
END IF;
#查看目标结点是否是源结点的子孙
SELECT COUNT(1)=1 INTO is_inherit_flag
FROM (
SELECT parent.title AS title
FROM nested_category node, nested_category parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
      AND node.title = target_node_pk
ORDER BY parent.lft ASC
) AS _x
WHERE title = source_node_pk;
#若源是目标的祖先，抛锚
IF is_inherit_flag THEN
 signal SQLSTATE '45000' SET message_text='source must not be ancestor of target';
END IF;
SET autocommit=0;
START TRANSACTION;
#1 计算子树宽度 sub_tree_width=source.rgt - source.lft + 1
SELECT (node.`rgt`-node.`lft`+1),node.`lft`,node.`rgt` INTO sub_tree_width,source_lft,source_rgt
FROM `nested_category` node
WHERE node.`title`=source_node_pk;
#2 修改子树各结点删除标志位为1，假删除 removed=true（默认是false）
UPDATE `nested_category`
SET removed=TRUE
WHERE lft BETWEEN source_lft AND source_rgt;
#3 删除源子树后,剩下树各个结点都要调整(剔除原子树 removed=true)左值右值      when rgt>=source.rgt    then -> set rgt=rgt-sub_tree_width  ; when lft>=source.rgt    then -> set lft=lft-sub_tree_width
UPDATE nested_category 
SET rgt = rgt - sub_tree_width 
WHERE rgt > source_rgt AND removed=FALSE;
UPDATE nested_category 
SET lft = lft - sub_tree_width 
WHERE lft > source_rgt AND removed=FALSE;
#4 计算位移偏移量 shift_diff=target.rgt - source.lft,获取目标结点的右值. （移动后结果是源子树的lft就是目标的rgt ，而子树中各结点lft rgt都要自增偏移量，  rgt>=目标rgt or lft>目标rgt:自增子树的宽度。
SELECT SUM(CASE title 
      WHEN source_node_pk THEN -1*lft
      WHEN target_node_pk THEN rgt
      ELSE 0
      END) INTO shift_diff
FROM nested_category ;
#目标rgt因为上面删除操作而调整过rgt，重新获取该值
SELECT rgt INTO target_new_rgt
FROM `nested_category` 
WHERE title=target_node_pk;
#5 修改受新增子树后受影响的结点的左值右值   when rgt>=target.rgt     then -> set rgt=rgt+sub_tree_width  ; when lft>=target.rgt    then -> set lft=lft+sub_tree_width
UPDATE nested_category 
SET rgt=rgt+sub_tree_width
WHERE rgt >= target_new_rgt  AND removed=FALSE;
UPDATE nested_category 
SET lft=(lft+sub_tree_width)
WHERE lft > target_new_rgt AND removed=FALSE;
#6 按偏移量更新原子树(removed=true)的左值右值      when removed=TRUE and (lft between source.lft and source.rgt)     then -> set lft=lft+shift_diff , rgt=rgt+shift_diff
UPDATE nested_category 
SET lft=lft+shift_diff,
   rgt=rgt+shift_diff,
   removed=FALSE
WHERE removed=TRUE;
#7 更新source.parent=target.id  (可选)
UPDATE `nested_category`
SET parent=target_node_pk
WHERE title=source_node_pk;
COMMIT;
#ROLLBACK;
SET autocommit=1;
    END$$
DELIMITER ;

---------------- 移动结点 end--------------------------