mysql单表递归查询

一、向下递归：

DROP FUNCTION IF EXISTS getAllChildren;
CREATE FUNCTION queryChildrenAreaInfo(parentId 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;

在jpa中使用：

@Query(value = "select c.* from category c where FIND_IN_SET(id,queryChildrenAreaInfo(:parentId))",nativeQuery=true)
    List<Category> getAllChildren(@Param("parentId") Integer parentId);

二、向上递归：