mysql中使用递归查询所有父级和子级信息

背景：一张树状结构的表，有id，parent_id，每条数据通过关联形成树状结构

需求一：根据id查询所有的父级id

BEGIN
DECLARE fid VARCHAR(100) DEFAULT '';
DECLARE str VARCHAR(100) DEFAULT rootId;

WHILE rootId is not null DO
	set fid = (SELECT parent_id from dict_schoolArea where id = rootId);
	if fid is not null and fid !=0 and fid != 1 THEN
		SET str = CONCAT(str,',',fid);
		set rootId = fid;
	else 
		set rootId = fid;
	end if;
end while;
return str;
end

因为业务中不需要显示根节点和1的节点，故加上了   fid !=0 and fid != 1  ；

mapper.xml使用：

  <!-- 根据id获取所有父级节点id -->
  <select id="getParentIdListById" parameterType="Long" resultType="String">
  	    select getIdofProCityDist(#{id,jdbcType=BIGINT}) from dual
  </select>	

需求二：根据id查询所有的子节点id

  BEGIN
	#根据父节点获取课程体系表中所有的子节点id
  DECLARE resName VARCHAR(100) DEFAULT '';
	DECLARE str VARCHAR(200);
	DECLARE cid VARCHAR(100);
	SET str = '';
	SET cid = rootId;

	WHILE cid is not null DO
		SET str = CONCAT(str,',',cid);
		SELECT GROUP_CONCAT(id) INTO cid from curriculum_system WHERE FIND_IN_SET(parent_id,cid)>0;
	END WHILE;
RETURN str;	
END