mysql查询树形数据

需求:

       使用mysql查询树形数据

 

创建函数

DROP FUNCTION IF EXISTS `getChild`;
CREATE FUNCTION `getChild` (rootId INT) RETURNS VARCHAR (1000)
BEGIN
	DECLARE
		ptemp VARCHAR (1000);
	DECLARE
		ctemp VARCHAR (1000);

	SET ptemp = '#';
	SET ctemp = CAST(rootId AS CHAR);

	WHILE ctemp IS NOT NULL DO
		SET ptemp = CONCAT(ptemp, ',', ctemp);

		SELECT
			GROUP_CONCAT(id) INTO ctemp
		FROM
			s_role
		WHERE
			FIND_IN_SET(fatherId, ctemp) > 0;
	END WHILE;

RETURN ptemp;
END

使用：

 SELECT * FROM s_role WHERE FIND_IN_SET(id, getChild(106))