Example of a MySQL function that recursively acquires the values of the ancestor Element

Test. SQL:

-- Create Test Data
Drop table if exists T1;
Create table T1 (
Id bigint not null auto_increment,
Pid bigint,
Code varchar (255 ),
Primary key (id)
);
Insert into t1 (id, pid, code) values (1, null, '1 ');
Insert into t1 (id, pid, code) values (2, null, '2 ');
Insert into t1 (id, pid, code) values (3, 1, '1. 1 ');
Insert into t1 (id, pid, code) values (4, 1, '1. 2 ');
Insert into t1 (id, pid, code) values (5, 2, '2. 1 ');
Insert into t1 (id, pid, code) values (6, 3, '1. 1.1 ');
Select * from t1 order by code;
 
-- Define a recursive processing function: Get the id and code of the ancestor and connect them in sequence with the symbol '/'. Connect the id and code ';'.
DELIMITER $
Drop function if exists getAncestors $
Create function getAncestors (id bigint) returns varchar (1000)
BEGIN
DECLARE done int default 0;
DECLARE r VARCHAR (1000 );
DECLARE ri VARCHAR (1000 );
DECLARE rc VARCHAR (1000 );
DECLARE levint;
DECLARE cid bigint;
DECLARE pid bigint;
DECLARE pcode VARCHAR (255 );
Declare continue handler for not found set done = 1;
 
SET cid = id;
SET lev_= 0;
SET ri = '';
SET rc = '';
REPEAT
SELECT p. id, p. code into pid, pcode FROM T1 c inner join T1 p on p. id = c. pid where c. id = cid;
If not done THEN
SET cid = pid;
If length (ri)> 0 then
SET ri = concat (cast (pid as char), '/', ri );
SET rc = concat (cast (pid as char), '/', rc );
Else
SET ri = cast (pid as char );
SET rc = pcode;
End if;
End if;
UNTIL done end repeat;
If length (ri)> 0 then
SET r = concat (ri, ';', rc );
Else
SET r = null;
End if;
RETURN r;
END $
DELIMITER;
-- Return Value: null;
Select getAncestors (1 );
-- Return Value: '1; 1 ';
Select getAncestors (3 );
-- Return Value: '1970; 1/3. 1 ';
Select getAncestors (6 );