mysql递归查询， 包含mysql 递归查询父节点 和子节点。

执行

CREATE TABLE `treenodes` (

`id` int(11) NOT NULL,

`nodename` varchar(20) DEFAULT NULL,

`pid` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `treenodes` VALUES ('1', 'A', '0');

INSERT INTO `treenodes` VALUES ('2', 'B', '1');

INSERT INTO `treenodes` VALUES ('3', 'C', '1');

INSERT INTO `treenodes` VALUES ('4', 'D', '2');

INSERT INTO `treenodes` VALUES ('5', 'E', '2');

INSERT INTO `treenodes` VALUES ('6', 'F', '3');

INSERT INTO `treenodes` VALUES ('7', 'G', '6');

INSERT INTO `treenodes` VALUES ('8', 'H', '0');

INSERT INTO `treenodes` VALUES ('9', 'I', '8');

INSERT INTO `treenodes` VALUES ('10', 'J', '8');

INSERT INTO `treenodes` VALUES ('11', 'K', '8');

INSERT INTO `treenodes` VALUES ('12', 'L', '9');

INSERT INTO `treenodes` VALUES ('13', 'M', '9');

INSERT INTO `treenodes` VALUES ('14', 'N', '12');

INSERT INTO `treenodes` VALUES ('15', 'O', '12');

INSERT INTO `treenodes` VALUES ('16', 'P', '15');

INSERT INTO `treenodes` VALUES ('17', 'Q', '15');

接下来创见查询父节点的函数

delimiter // CREATE FUNCTION `getParentList`(rootId INT)      RETURNS char(255)      BEGIN       declare fid int default 1;      declare str char(255) default rootId;      while rootId>0 do      set fid=(SELECT pid FROM hostmonitor.treenodes  WHERE rootId=id);      IF fid > 0 THEN       SET str=concat(str,',',fid);        SET rootId=fid;       ELSE SET rootId=fid;       END IF;       END WHILE; return str;     END  //调用select getParentList(7);

同样可以用以下函数查询子节点

delimiter // CREATE FUNCTION `getChildList`(rootId INT)      RETURNS char(255)      BEGIN        DECLARE str char(255) ;        DECLARE cid char(255) ;             SET str = '';        SET cid =cast(rootId as CHAR);             WHILE cid is not null DO          SET str= concat(str,',',cid);          SELECT group_concat(id) INTO cid FROM treeNodes where FIND_IN_SET(pid,cid)>0;        END WHILE;        RETURN str;      END //    select getChildLst(1);