mysql查询树结构

在 oracle 数据库中，通过 start with connect by prior 递归可以直接查出树结构，但是在 mysql 当中如何解决树查询问题呢？

思路

我们可以通过自定义函数，遍历找出某一节点的所有子节点 （或者某一节点的所有父节点）的字符串集合。然后通过 FIND_IN_SET 函数，这就查出了我们想要的树

(1）建表 以及 测试数据准备

CREATE TABLE `tree` (
  `id` int(11) NOT NULL,
  `pid` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT INTO `axs`.`tree` (`id`, `pid`, `name`) VALUES ('1', NULL, '一级');
INSERT INTO `axs`.`tree` (`id`, `pid`, `name`) VALUES ('2', '1', '二级1');
INSERT INTO `axs`.`tree` (`id`, `pid`, `name`) VALUES ('3', '2', '三级1');
INSERT INTO `axs`.`tree` (`id`, `pid`, `name`) VALUES ('4', '3', '四级1');
INSERT INTO `axs`.`tree` (`id`, `pid`, `name`) VALUES ('5', '4', '五级');
INSERT INTO `axs`.`tree` (`id`, `pid`, `name`) VALUES ('6', '1', '三级2');
INSERT INTO `axs`.`tree` (`id`, `pid`, `name`) VALUES ('7', '1', '二级2');
INSERT INTO `axs`.`tree` (`id`, `pid`, `name`) VALUES ('8', '6', '四级2'); 

（2）查询 某节点下所有子节点

先建立函数

CREATE FUNCTION `GET_CHILD_NODE`(rootId varchar(100))   
RETURNS varchar(2000)  
BEGIN   
DECLARE str varchar(2000);  
DECLARE cid varchar(100);   
SET str = '$';   
SET cid = rootId;   
WHILE cid is not null DO   
    SET str = concat(str, ',', cid);   
    SELECT group_concat(id) INTO cid FROM tree where FIND_IN_SET(pid, cid);   
END WHILE;   
RETURN str;   
END 

第一次进入函数 cid 为根节点，开始循环后，cid 为每次查询结果集 （也就是子节点），下一次会查询出所有子节点的子节点 … 以此类推，当没有子节点时退出循环，也就得到了所有的节点。

mysql> SELECT * from tree where FIND_IN_SET(id, GET_CHILD_NODE(2));
+----+-----+-------+
| id | pid | name  |
+----+-----+-------+
|  2 |   1 | 二级1 |
|  3 |   2 | 三级1 |
|  4 |   3 | 四级1 |
|  5 |   4 | 五级  |
+----+-----+-------+
4 rows in set 

(3）查询 某节点的所有父节点

一样先建立函数

CREATE FUNCTION `GET_PARENT_NODE`(rootId varchar(100))   
RETURNS varchar(1000)   
BEGIN   
DECLARE fid varchar(100) default '';   
DECLARE str varchar(1000) default rootId;   
  
WHILE rootId is not null do   
    SET fid =(SELECT pid FROM tree WHERE id = rootId);   
    IF fid is not null THEN   
        SET str = concat(str, ',', fid);   
        SET rootId = fid;   
    ELSE   
        SET rootId = fid;   
    END IF;   
END WHILE;   
return str;  
END 

和上一个函数类似，不断的遍历去找 父id，然后拼接到字符串中，为空退出循环。

mysql> select * from tree where FIND_IN_SET(id, GET_PARENT_NODE(5));
+----+------+-------+
| id | pid  | name  |
+----+------+-------+
|  1 | NULL | 一级  |
|  2 |    1 | 二级1 |
|  3 |    2 | 三级1 |
|  4 |    3 | 四级1 |
|  5 |    4 | 五级  |
+----+------+-------+
5 rows in set