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

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 subjectNameFROM category AS node,category AS parentWHERE node.lft BETWEEN parent.lft AND parent.rgtGROUP BY node.nameORDER 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 utf8BEGIN        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