mysql单表递归查询

一、向下递归：

DROP FUNCTION IF EXISTS getAllChildren;CREATE FUNCTION queryChildrenAreaInfo(parentId INT)RETURNS VARCHAR(4000)BEGINDECLARE sTemp VARCHAR(4000);DECLARE sTempChd VARCHAR(4000);SET sTemp='$';SET sTempChd = CAST(areaId AS CHAR);WHILE sTempChd IS NOT NULL DOSET 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);

二、向上递归：