运行

set global log_bin_trust_function_creators=1;

创建自定义函数,

CREATE DEFINER=`root`@`%` FUNCTION `getParentList`(rootId varchar(100)) RETURNS varchar(1000) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
BEGIN
	DECLARE fid varchar(100) default '';   
	DECLARE str varchar(1000) default rootId;   
	
	WHILE rootId is not null  do   
     SET fid =(SELECT PID FROM '表名' 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

查询

SELECT id,username,alias FROM '表名' WHERE FIND_IN_SET( id,getParentList ( 'rootId' ))