MySQL recursion problem

MySQL itself does not support recursive syntax, but some simple recursion can be achieved through self-connection

--Recursive method: Different methods for temporary tables and ordinary tables--This question uses a recursive drop table if exists test to query the parent node twice using a temporary table;
create table test(
id varchar(100),
name varchar(20),
parentid varchar(100)
);
insert test select
'13ed38f1-3c24-dd81-492f-673686dff0f3', 'University Teacher', '37e2ea0a-1c31-3412-455a-5e60b8395f7d' union all select 
'1ce203ac-ee34-b902-6c10-c806f0f52876','Primary school teacher', '37e2ea0a-1c31-3412-455a-5e60b8395f7d' union all select 
'37e2ea0a-1c31-3412-455a-5e60b8395f7d', 'Teacher' , null union all select 
'c877b7ea-4ed3-f472-9527-53e1618cb1dc', 'High Mathematics Teacher', '13ed38f1-3c24-dd81-492f-673686dff0f3' union all select 
'ce50a471-2955-00fa-2fb7-198f6b45b1bd', 'middle school teacher', '37e2ea0a-1c31-3412-455a-5e60b8395f7d';
 
delimiter $$
 
create procedure usp_ser(in idd varchar(100))
begin
declare lev int;
set lev=1;
drop table if exists tmp1;
drop table if exists tmp2;
CREATE TEMPORARY TABLE tmp1(id varchar(100),name varchar(20),parentid varchar(100),levv int);
CREATE TEMPORARY TABLE tmp2(pid varchar(100));
insert tmp2 select parentid from test where id=idd;
insert tmp1 select t.* , lev from test t join tmp2 a on t.id=a.pid;
    while exists(select 1 from tmp2 )
do
truncate tmp2;
set lev=lev+1;
insert tmp2 select t.id from test t join tmp1 a on t.id=a.parentid and a.levv=lev-1;
insert tmp1 select t.*,lev from test t join tmp2 a on t.id=a.pid;
end while ;
select id,name,parentid from tmp1;
end;
$$
 
delimiter ;
 
 call usp_ser('c877b7ea-4ed3-f472-9527-53e1618cb1dc');
+--------------------------------------+----------+--------------------------------------+
| id | name | parentid |
+--------------------------------------+----------+--------------------------------------+
| 13ed38f1-3c24-dd81-492f-673686dff0f3 | University teacher | 37e2ea0a-1c31-3412-455a-5e60b8395f7d |
| 37e2ea0a-1c31-3412-455a-5e60b8395f7d | Teacher | NULL |
+--------------------------------------+----------+--------------------------------------+
 
 call usp_ser('13ed38f1-3c24-dd81-492f-673686dff0f3');
+--------------------------------------+------+----------+
| id | name | parentid |
+--------------------------------------+------+----------+
| 37e2ea0a-1c31-3412-455a-5e60b8395f7d | Teacher | NULL |
+--------------------------------------+------+----------+
 
 call usp_ser('37e2ea0a-1c31-3412-455a-5e60b8395f7d');
 
Empty set (0.02 sec)

The above method uses two temporary tables because MySQL does not allow multiple references to temporary tables in the same statement. The following is a recursive query that uses a normal table to query child nodes once.

Core code

drop table if exists test;
create table test(
id INT,
parentid INT
);
insert test select
1, 0 UNION ALL SELECT 
2, 1 UNION ALL SELECT 
3, 1 UNION ALL SELECT 
4, 0 UNION ALL SELECT 
5, 2 UNION ALL SELECT 
6, 5 UNION ALL SELECT 
7, 3 ;
Go
 
delimiter $$
 
create procedure usp_ser(in idd varchar(100))
begin
declare lev int;
set lev=1;
drop table if exists tmp1;
CREATE TABLE tmp1(id INT,parentid INT,levv INT,ppath VARCHAR(1000));

INSERT tmp1 SELECT *,lev,id FROM test WHERE parentid=idd;

 while row_count()>0
do

set lev=lev+1;
insert tmp1 select t.*,lev,concat(a.ppath,t.id) from test t join tmp1 a on t.parentid=a.id AND levv=LEV-1;
 
end while ;
SELECT * FROM tmp1;
 
end;
$$
 
delimiter ;
 
 call usp_ser(0);
 
/*
+------+----------+------+-------+
| id | parentid | levv | ppath |
+------+----------+------+-------+
| 1 | 0 | 1 | 1 |
| 4 | 0 | 1 | 4 |
| 2 | 1 | 2 | 12 |
| 3 | 1 | 2 | 13 |
| 5 | 2 | 3 | 125 |
| 7 | 3 | 3 | 137 |
| 6 | 5 | 4 | 1256 |
+------+----------+------+-------+*/

You may also be interested in:

MySQL recursive query tree table child nodes, parent nodes specific implementation
How to implement recursive query in MYSQL
PHP+MySQL infinite classification example without recursion (non-recursive)
Use recursion to delete all child nodes of a tree structure (implemented by Java and MySQL)
Using function recursion to implement a dynamic tree menu based on PHP and MySQL
An example of how to use Java+MySQL recursion to concatenate tree-shaped JSON lists
PHP recursive writing to MySQL to achieve unlimited level classification data operation example
Implementation method of Mysql tree recursive query

<<: Drawing fireworks effect of 2021 based on JS with source code download

>>: How to create your own image using Dockerfile