mysql递归查询子节点

先创建一张测试表

DROP TABLE IF EXISTS `one`;
CREATE TABLE `one` (
  `id` int(11) NOT NULL,
  `pid` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入测试数据

INSERT INTO `one` VALUES ('1', null, '11111');
INSERT INTO `one` VALUES ('2', null, '222');
INSERT INTO `one` VALUES ('3', '1', '333');
INSERT INTO `one` VALUES ('4', '1', '444');
INSERT INTO `one` VALUES ('5', '4', '55555');
INSERT INTO `one` VALUES ('6', '4', '666');
INSERT INTO `one` VALUES ('7', '6', '777');
INSERT INTO `one` VALUES ('8', '7', '8888888');
INSERT INTO `one` VALUES ('9', '8', '8888888');
INSERT INTO `one` VALUES ('10', '9', '8888888');
INSERT INTO `one` VALUES ('11', '10', '8888888');
INSERT INTO `one` VALUES ('12', '11', '8888888');

创建函数

<pre name="code" class="sql">CREATE DEFINER=`root`@`%` FUNCTION `testOne`(areaId INT) RETURNS varchar(4000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp = '$';
SET sTempChd = cast(areaId as char);

WHILE sTempChd is not NULL DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM one where FIND_IN_SET(pid,sTempChd)>0;
END WHILE;
return sTemp;
END

查询时就是用 select * from one where  FIND_IN_SET(id, testOne(1))

可以将id为1的所有下级全部查询出来