MYSQL 取树型数据

获取一个节点的所有子孙节点群

通过一个根节点获取其下所有子孙节点列表

---

创建操作对象——数据表和数据

CREATE TABLE Region
(
Id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
NAME NVARCHAR(20),
ParentId INT,
PRIMARY KEY (`Id`)
)

INSERT INTO Region(NAME,ParentId) VALUES('广东',NULL);
INSERT INTO Region(NAME,ParentId) VALUES('深圳',1);
INSERT INTO Region(NAME,ParentId) VALUES('惠州',1);
INSERT INTO Region(NAME,ParentId) VALUES('罗湖区',2);
INSERT INTO Region(NAME,ParentId) VALUES('福田区',2);
INSERT INTO Region(NAME,ParentId) VALUES('龙岗区',2);
INSERT INTO Region(NAME,ParentId) VALUES('惠阳区',3);
INSERT INTO Region(NAME,ParentId) VALUES('龙门县',3);
INSERT INTO Region(NAME,ParentId) VALUES('华强北',5);
INSERT INTO Region(NAME,ParentId) VALUES('体育馆',5);

SELECT * FROM Region;

---

1. 函数法——广度遍历

循环遍历，借助MYSQL内的几个函数对字符串处理：group_concat(); find_in_set(); concat()

// 创建函数

DELIMITER $$
USE `ebt_shop`$$
DROP FUNCTION IF EXISTS `getChildList`$$

CREATE DEFINER=`csst`@`%` FUNCTION `getChildList`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN
      DECLARE sChildList VARCHAR(1000);
      DECLARE sChildTemp VARCHAR(1000);
      SET sChildTemp =CAST(rootId AS CHAR);

      WHILE sChildTemp IS NOT NULL DO
        IF (sChildList IS NOT NULL) THEN
          SET sChildList = CONCAT(sChildList,',',sChildTemp);
       ELSE
          SET sChildList = CONCAT(sChildTemp);
       END IF;
        SELECT GROUP_CONCAT(id) INTO sChildTemp FROM region WHERE FIND_IN_SET(parentId,sChildTemp)>0;
      END WHILE;

      RETURN sChildList;
  END$$

DELIMITER ;

【说明】
优点: 简单，方便，没有递归调用层次深度的限制 (max_sp_recursion_depth,最大255) ；
缺点：长度受限，虽然可以扩大 RETURNS varchar(1000)，但总是有最大限制的。

---

2.存过，递归——深度遍历

借助临时表和游标遍历，递归的存储过程。

存过A:

DELIMITER $$
USE `ebt_shop`$$
DROP PROCEDURE IF EXISTS `showChildLst`$$
CREATE DEFINER=`csst`@`%` PROCEDURE `showChildLst`(IN rootId INT)
BEGIN
  /* create temporary table */
  CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst  
  (      sno INT PRIMARY KEY AUTO_INCREMENT,
 id INT,
 depth INT
   );
  /* delete data for temporary table */
  DELETE FROM tmpLst;

  /* main process: call recursive procedure */
  CALL createChildLst(rootId,0);

  /* show result */
  SELECT tmpLst.*,region.* FROM tmpLst,region WHERE tmpLst.id=region.id ORDER BY tmpLst.sno;

  /* drop temporary table */
  DROP TEMPORARY TABLE IF EXISTS tmpLst;
END$$
DELIMITER ;

存过B:

DELIMITER $$
USE `ebt_shop`$$
DROP PROCEDURE IF EXISTS `createChildLst`$$
CREATE DEFINER=`csst`@`%` PROCEDURE `createChildLst`(IN rootId INT,IN nDepth INT)
BEGIN
  DECLARE done INT DEFAULT 0;   /* 控制while循环变量 */
  DECLARE tempId INT;           /* 游标中循环时赋值变量 */

  /* 定义游标cur1 */
  DECLARE cur1 CURSOR FOR SELECT id FROM region WHERE ParentId=rootId;  
  /* 当前节点没有子节点时，设置while循环变量为退出值 */  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 

  /* 插入当前节点记录 */
  INSERT INTO tmpLst VALUES (NULL,rootId,nDepth);  

  /* 迭代游标；在while循环中做递归 */
  OPEN cur1;
    FETCH cur1 INTO tempId;

    WHILE done=0 DO
      CALL createChildLst(tempId, nDepth+1);
      FETCH cur1 INTO tempId;
    END WHILE;

  CLOSE cur1;

END$$
DELIMITER ;

【说明】：MySQL中你可以利用系统参数 max_sp_recursion_depth 来控制递归调用的层数上限。如下例设为12.

    SET max_sp_recursion_depth=12;

优点 : 可以更灵活处理，及层数的显示。并且可以按照树的遍历顺序得到结果。
缺点 : 递归有255的限制。

---

3.存过，借助额外的表——深度遍历

由于MySQL中不允许在同一语句中对临时表多次引用， 因此用普通表来存储。 当然你的程序中负责在用完后清除这个表。

DELIMITER $$

USE `ebt_shop`$$
DROP PROCEDURE IF EXISTS `showTreeNodesByAssitantTbl`$$

CREATE DEFINER=`csst`@`%` PROCEDURE `showTreeNodesByAssitantTbl`(IN rootId INT)
BEGIN
 /* 创建表 */
  DROP TABLE IF EXISTS tmpLst;
  CREATE TABLE tmpLst (
    id INT,
    nLevel INT,
    sCort VARCHAR(8000)
  );

  DECLARE LEVEL INT ;
  SET LEVEL=0 ;
  INSERT INTO tmpLst SELECT id,LEVEL,ID FROM region WHERE ParentId=rootId;   /* insert into ... select 语句 */
  WHILE ROW_COUNT()>0 DO
       SET LEVEL=LEVEL+1 ;
       INSERT INTO tmpLst 
          SELECT A.id, LEVEL, CONCAT(B.sCort, A.id) FROM region A,tmpLst B 
               WHERE  A.ParentId=B.id AND B.nLevel=LEVEL-1  ;
  END WHILE;

END$$

DELIMITER ;

/* 调用存过会创建表，然后查询展示 */
CALL showTreeNodesByAssitantTbl(1);

SELECT CONCAT(SPACE(B.nLevel*2),'+--',A.name) FROM region A,tmpLst B WHERE A.ID=B.ID ORDER BY B.sCort;

【说明】
优点 : 层数的显示。并且可以按照树的遍历顺序得到结果。没有递归限制。
缺点 : MySQL中对临时表的限制，只能使用普通表，需做事后清理。

---