Mysql递归查询

1.建表

DROP TABLE IF EXISTS `t_areainfo`;
CREATE TABLE `t_areainfo` (

`id` int(11) NOT '0' AUTO_INCREMENT,

`level` int(11) DEFAULT '0',

`name` varchar(255) DEFAULT '0',

`parentId` int(11) DEFAULT '0',

`status` int(11) DEFAULT '0',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;

2.初始数据

INSERTINTO`t_areainfo`VALUES('1','0','中国','0','0');

INSERTINTO`t_areainfo`VALUES('2','0','华北区','1','0');

INSERTINTO`t_areainfo`VALUES('3','0','华南区','1','0');

INSERTINTO`t_areainfo`VALUES('4','0','北京','2','0');

INSERTINTO`t_areainfo`VALUES('5','0','海淀区','4','0');

INSERTINTO`t_areainfo`VALUES('6','0','丰台区','4','0');

INSERTINTO`t_areainfo`VALUES('7','0','朝阳区','4','0');

3.向下递归：利用find_in_set()函数和group_concat()函数实现递归查询

DROP FUNCTION IF EXISTS queryChildrenAreaInfo;

CREATE FUNCTION queryChildrenAreaInfo(areaId INT)

RETURNS VARCHAR(4000)

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 t_areainfo WHERE FIND_IN_SET(parentId,sTempChd)>0;

END WHILE;

RETURN sTemp;

END;

4、调用方式：
查询id为"4"下面的所有节点

SELECT*FROMt_areainfoWHEREFIND_IN_SET(id,queryChildrenAreaInfo(4));

5.向上递归：

DROP FUNCTION IF EXISTS queryChildrenAreaInfo1;

CREATE FUNCTION queryChildrenAreaInfo1(areaId INT)

RETURNS VARCHAR(4000)

BEGIN

DECLARE sTemp VARCHAR(4000);

DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';

SET sTempChd = CAST(areaId AS CHAR);

SET sTemp = CONCAT(sTemp,',',sTempChd);

SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;

WHILE sTempChd <> 0 DO

SET sTemp = CONCAT(sTemp,',',sTempChd);

SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;

END WHILE;

RETURN sTemp;

END;

6、调用方式：
查询id为"7"的节点的所有上级节点：

SELECT*fromt_areainfowhereFIND_IN_SET(id,queryChildrenAreaInfo1(7));