MYSQL栏目树左右节点实现方式

最新推荐文章于 2022-10-01 11:30:55 发布

chenzenan

最新推荐文章于 2022-10-01 11:30:55 发布

阅读量2.9k

点赞数

分类专栏： DataBase

本文链接：https://blog.csdn.net/chenzenan/article/details/12445553

版权

DataBase 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `parent` int(11) DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `rgt` int(11) DEFAULT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
insert  into `category`(`category_id`,`name`,`parent`,`lft`,`rgt`) values (1,'根结点',NULL,1,20),(2,'一级1',1,2,9),(3,'二级1---一级1',2,3,4),(4,'二级2---一级1',2,5,6),(5,'二级3---一级1',2,7,8),(6,'一级2',1,10,19),(7,'二级1---一级2',6,11,14),(8,'三级1---二级1---一级2',7,12,13),(9,'二级2---一级2',6,15,16),(10,'二级3---一级2',6,17,18);

1. 新增节点
　a．查找当前插入节点的父节点的lft值
b．将树形中所有lft和rgt节点大于父节点左值的节点都+2
　c．将父节点左值+1，左值+2分别作为当前节点的lft和rgt

SELECT @myLeft:= lft FROM category WHERE category_id = 'parentId';
UPDATE category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE category SET lft = lft + 2 WHERE lft > @myLeft;
INSERT INTO category(name, lft, rgt) VALUES('FRS-------22', @myLeft + 1, @myLeft + 2);

2. 修改节点(在修改lft和rgt之前，当前节点的父节点id已经改变)
a. 查出当前节点的左右节点（nodelft、nodergt），并nodergt-nodelft+1 = span，获取父节点的左节点parentlft
b. 将所有大于parentlft的lft(左节点)、rgt(右节点)的值+span
c. 查找当前节点的左右节点（nodelft、nodergt），并parentlft-nodelft+1 = offset
d. 将所有lft(左节点) between nodelft and nodergt的值+offset
e. 将所有大于nodergt的lft(左节点)、rgt(右节点)的值-span

  SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 FROM category WHERE  category_id = 'category_id';
  SELECT @parentLeft := lft, @parentRight := rgt FROM category WHERE  category_id = 'parentId';
  UPDATE category SET rgt = rgt + @myWidth WHERE rgt > @parentLeft;
  UPDATE category SET lft = lft + @myWidth WHERE lft > @parentLeft;
  SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 FROM category WHERE  category_id = 'category_id';
  UPDATE category set lft=lft+@parentLeft-@myLeft+1, rgt=rgt+@parentLeft-@myLeft+1 WHERE lft between @myLeft and @myRight;
  UPDATE category set  rgt=rgt-@myWidth WHERE rgt>@myRight;
  UPDATE category set  lft=lft-@myWidth WHERE lft>@myRight;

如果parentId为空及意味着将子节点升为根节点，在有些情况下可能表中没有根节点，一般情况下上面操作已经比较完善，这时可以这样操作

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 FROM category WHERE  category_id = '7';
select @maxRgt:=max(rgt)-@myLeft+1 from category;
UPDATE category set  lft=lft+@maxRgt, rgt=rgt+@maxRgt WHERE lft between @myLeft and @myRight;
UPDATE category set  rgt=rgt-@myWidth WHERE rgt>@myRight;
UPDATE category set  lft=lft-@myWidth WHERE lft>@myRight;

3. 删除节点(只能删除叶子结点)

　a．查找要删除节点的lft值
　b．将所有lft和rgt大于删除节点lft值的都-2
非叶子结点(通用删除)

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 FROM category WHERE  category_id = 'category_id';
DELETE FROM category WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE category SET lft = lft - @myWidth WHERE lft > @myRight;

4.测试验证

SELECT node.*,(COUNT(parent.name) - 1) AS depth,CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS subjectName
FROM category AS node,
category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

5.利用函数方式实现递归查询

DELIMITER $$

DROP FUNCTION IF EXISTS `getChildList`$$

CREATE FUNCTION `getChildList`(parentId VARCHAR(50))
     RETURNS VARCHAR(1000)  
     BEGIN 
       DECLARE childList VARCHAR(1000);  
       DECLARE cTemp VARCHAR(1000);
       SET childList = '';  
       SET cTemp =cast(parentId as CHAR);
       WHILE cTemp is not null DO  
         SET childList = concat(cTemp,',',childList);
         SELECT group_concat(category_id) INTO cTemp FROM category   
         WHERE FIND_IN_SET(parent,cTemp)>0; 
         /*SELECT group_concat(parent) INTO cTemp FROM category   
         WHERE FIND_IN_SET(category_id,cTemp)>0;*/
       END WHILE;
       SET childList=LEFT(childList,Length(childList)-1);
       RETURN childList;  
    END$$
DELIMITER ;

如果返回的字符串长度不够，直接返回text类型，修改以上代码如下

DELIMITER $$

DROP FUNCTION IF EXISTS `getChildList`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `getChildList`(parentId VARCHAR(50)) RETURNS  text CHARSET utf8
BEGIN 
       DECLARE childList text;  
       DECLARE cTemp text;
       SET childList = '';  
       SET cTemp =cast(parentId as CHAR);
       WHILE cTemp is not null DO  
         SET childList = concat(cTemp,",",childList);
	 SET GLOBAL group_concat_max_len=102400;
	 SET SESSION group_concat_max_len=102400;
         SELECT group_concat(category_id) INTO cTemp FROM category   
         WHERE FIND_IN_SET(parent,cTemp)>0; 
       END WHILE;      
        SET childList=LEFT(childList,Length(childList)-1);
        RETURN childList;  
END$$

6.函数查询测试

         SELECT getChildList('2')
         SELECT * FROM category WHERE FIND_IN_SET(category_id, getChildList('2'));
         select length(getChildList('2'))-length(replace(getChildList('2'),',',''))

7.存储过程方式查询

DELIMITER $$
DROP PROCEDURE IF EXISTS `findChildList`$$
CREATE PROCEDURE `findChildList`(parentId bigint(20),layer bigint(20))
    BEGIN
         declare id bigint(20) default '-1' ;
	 declare category_name varchar(50) character set utf8;
         declare category_cursor CURSOR FOR select category_id,name from category where parent=parentId;
         declare CONTINUE HANDLER FOR SQLSTATE '02000' SET id = null;
      if layer>0 then
         OPEN category_cursor ;
         FETCH category_cursor INTO id,category_name ;
           WHILE (id is not null ) 
              DO
               insert into tmp_table values(id,category_name);
               call findChildList(id,layer-1);
              FETCH category_cursor INTO id,category_name ;
           END WHILE;
       end if;
    END;$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `getChildList`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getChildList`(parentId int,layer bigint(20))
BEGIN
    create temporary  table if not exists tmp_table(id bigint(20),name varchar(50)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    SET @@max_sp_recursion_depth = 99 ;
     call findChildList(parentId,layer);
    select * from tmp_table ;
    drop temporary  table if  exists   tmp_table ;
    END$$
DELIMITER ;

8.存储过程测试

call getChildList(1,10);

9.总结发现在数据量比较大的时候函数的执行效率还是比较低，查询速度比不上左右节点的方式，存储过程貌似比函数要好，但是还是比不上左右节点方式

10.虽然存储过程的方式效率低，可以利用它来初始化左右节点的值；原理参照新增节点，分为两种方式，一种修改原表结构，还有一种新增加一张表结构

DELIMITER $$
DROP PROCEDURE IF EXISTS `findChildList`$$
CREATE PROCEDURE `findChildList`(parentId bigint(20),layer bigint(20))
    BEGIN
         declare id bigint(20) default '-1' ;
	 declare category_name varchar(50) character set utf8;
         declare category_cursor CURSOR FOR select category_id,name from category where parent=parentId;
         declare CONTINUE HANDLER FOR SQLSTATE '02000' SET id = null;
      if layer>0 then
         OPEN category_cursor ;
         FETCH category_cursor INTO id,category_name ;
           WHILE (id is not null ) 
              DO
			  if (parentId is null) or (ifnull(parentId,'')='') then
				/*新增一张表数据*/
				select @maxLft:=ifnull(max(rgt),0) from tmp_table;
				insert into tmp_table values(id,category_name, @maxLft+1, @maxLft+2);
				
				/*select @maxLft:=ifnull(max(rgt),0) from category;
				UPDATE category SET lft = @maxLft + 1,rgt=@maxLft + 2 WHERE id=id;*/
				else
				/*新增一张表数据*/
				SELECT @myLeft:= lft FROM tmp_table WHERE id = parentId;
				UPDATE tmp_table SET rgt = rgt + 2 WHERE rgt > @myLeft;
				UPDATE tmp_table SET lft = lft + 2 WHERE lft > @myLeft;
				insert into tmp_table values(id,category_name, @myLeft + 1, @myLeft + 2);

				/*SELECT @myLeft:= lft FROM t_employee WHERE id = parentId;
				UPDATE category SET rgt = rgt + 2 WHERE rgt > @myLeft;
				UPDATE category SET lft = lft + 2 WHERE lft > @myLeft;
				UPDATE category SET lft = @myLeft + 1,rgt=@myLeft + 2 WHERE id=id;*/
				end if;
               insert into tmp_table values(id,category_name);
               call findChildList(id,layer-1);
              FETCH category_cursor INTO id,category_name ;
           END WHILE;
       end if;
    END;$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `getChildList`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getChildList`(parentId int,layer bigint(20))
BEGIN
    create temporary  table if not exists tmp_table(id bigint(20),name varchar(50),lft int ,rgt int) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    SET @@max_sp_recursion_depth = 99 ;
     call findChildList(parentId,layer);
    select * from tmp_table ;
    drop temporary  table if  exists   tmp_table ;
    END$$
DELIMITER ;

参考：http://java161.iteye.com/blog/608748

http://www.iteye.com/topic/602979
http://my.oschina.net/bootstrap/blog/166805

http://51wifygoo1go.blog.51cto.com/6455479/1122729

http://www.wdphp.com/Mysql/show_234.html

chenzenan

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MYSQL栏目树左右节点实现方式

CREATE TABLE category(category_id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(20) NOT NULL,parent INT DEFAULT NULL);INSERT INTO categoryVALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(
复制链接

扫一扫