mysql 树形查询

mysql没有oracle中的start with ….connet prior by 的语法，所以需要自己写函数
（select * from classinfo start with classid=6 connet prior by classid=parentid）

mysql中的写法：
1.创建函数：

CREATE FUNCTION getChildList(rootId INT)
RETURNS varchar(1000)
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 user_role where FIND_IN_SET(parentid,sChildTemp)>0;
END WHILE;
RETURN sChildList;
END;
2.查询语句：
select * From user_role where FIND_IN_SET(id, getChildList(2))