Mysql递归，树形菜单

找到所有的子级

CREATE DEFINER=`root`@`%` FUNCTION `queryChildrenType`(typeId varchar(50)) RETURNS text CHARSET latin1
BEGIN
 DECLARE stemp TEXT;
 DECLARE stemp_child TEXT;
 SET stemp = '';
 SET stemp_child = CAST(typeId AS CHAR);
  
 WHILE stemp_child IS NOT NULL DO
  SET stemp = CONCAT(stemp, ',', stemp_child);
  SELECT GROUP_CONCAT(id) INTO stemp_child FROM t_ams_type WHERE FIND_IN_SET(parent_id, stemp_child) > 0;
 END WHILE ;
 RETURN SUBSTRING(stemp, 2);
    END

找到所有的父级

CREATE DEFINER=`root`@`%` FUNCTION `queryParentType`(root_id varchar(50)) RETURNS text CHARSET latin1
BEGIN
 DECLARE stemp TEXT;
 DECLARE stemp_child TEXT;
 SET stemp = '';
 SET stemp_child = CAST(root_id AS CHAR);
  
 WHILE stemp_child IS NOT NULL DO
  SET stemp = CONCAT(stemp, ',', stemp_child);
  SELECT GROUP_CONCAT(tat.parent_id) INTO stemp_child FROM t_ams_type tat WHERE FIND_IN_SET(tat.id, stemp_child) > 0;
 END WHILE ;
 RETURN SUBSTRING(stemp, 2);
    END