MYSQL 5.8 如何递归查询路径 举例如下

Question

一个表ABC ，两列UID 和PID ， 现在需要得到UID 与PID 的路径关系如下：

这个是用SQLSERVER的 with as 递归查询语句得出的结果。 请问 用MYSQL 语句如何得出这个结果。请给出MYSQL 的查询语句。

Answer 1

CREATE FUNCTION func(M INT,N INT) RETURNS varchar(50)
BEGIN
  DECLARE str varchar(50) DEFAULT '';
  DECLARE p int DEFAULT 0;
    DECLARE u int DEFAULT 0;
  DECLARE count int DEFAULT 0;
    set p = N;
  set u = M;
  set count = 0;
    if p = 0 Then
     set str = concat(u,'');
  end if;
  while p != 0 do

    select uid into u from a where pid = p and uid = u;
  select pid into p from a where pid = p and uid = u;
      if p = 0 THEN
      set p = 0;
    else 
      if count = 0 THEN
                    set str = concat(p,"->",u,str);
      else
          set str = concat(p,"->",str);
      end if;
        end if;
    set u = p;
    set count= count+1;
        select pid into p from a where uid = u;
   end while;
 
  RETURN (
      # Write your MySQL query statement below.
        str
      );
END;