MySQL取得父节点数据

之前用Oracle实现递归调用很方便，但是MySQL不支持，所以自己写function实现递归调用
SET GLOBAL log_bin_trust_function_creators = 1; 

DELIMITER ;;
CREATE FUNCTION `f_getParentList`(rootId varchar(100))   
RETURNS varchar(1000)   
BEGIN   
DECLARE fid varchar(100) default '';   
DECLARE str varchar(1000) default rootId;   
  
WHILE rootId is not null  do   
    SET fid =(SELECT parentid FROM Organization WHERE id = rootId);   
    IF fid is not null THEN   
        SET str = concat(str, '->', fid);   
        SET rootId = fid;   
    ELSE   
        SET rootId = fid;   
    END IF;   
END WHILE;   
return str;  
   
END ;;
DELIMITER ;
    
用法
 select f_getParentList('1003'); 
 select * from Organization where FIND_IN_SET(id,f_getParentList('1003'))