How does SQL implement MYSQL recursive query and SQL implement MYSQL Recursion

How does SQL implement MYSQL recursive query and SQL implement MYSQL Recursion

As we all know, the current mysql version does not support direct recursive queries, but the idea of recursive to iterative conversion can still implement tree recursive queries within an SQL statement. This is because Mysql allows @ variables in SQL statements. The following is the sample code.

Create a table

Create table 'treenode' ('id' int, -- node id 'nodename' varchar (60), -- node name 'pid' int -- node parent ID );

Insert Test Data

INSERT INTO `treenodes` (`id`, `nodename`, `pid`) VALUES('1','A','0'),('2','B','1'),('3','C','1'),('4','D','2'),('5','E','2'),('6','F','3'),('7','G','6'),('8','H','0'),('9','I','8'),('10','J','8'),('11','K','8'),('12','L','9'),('13','M','9'),('14','N','12'),('15','O','12'),('16','P','15'),('17','Q','15'),('18','R','3'),('19','S','2'),('20','T','6'),('21','U','8');

Query statement

SELECT id as id, pid AS parent ID, levels AS parent-child level, paths AS parent-child path FROM (SELECT id, pid, @ le: = IF (pid = 0, 0, IF (LOCATE (CONCAT ('|', pid, ':'), @ pathlevel)> 0, SUBSTRING_INDEX (@ pathlevel, CONCAT ('|', pid, ':'),-1), '|', 1) + 1, @ le + 1) levels, @ pathlevel: = CONCAT (@ pathlevel, '|', id, ':', @ le, '|') pathlevel, @ pathnodes: = IF (pid = 0, ', 0 ', CONCAT_WS (',', IF (LOCATE (CONCAT ('|', pid, ':'), @ pathall)> 0, SUBSTRING_INDEX (@ pathall, CONCAT ('|', pid, ':'),-1), '|', 1), @ pathnodes), pid) paths, @ pathall: = CONCAT (@ pathall, '|', id, ':', @ pathnodes, '|') pathall FROM treenodes, (SELECT @ le: = 0, @ pathlevel: = '', @ pathall: ='', @ pathnodes: = '') vv order by pid, id) srcORDER BY id

The final result is as follows:

ID parent-child level parent-child path
---------------------------------------------------
1 0 0, 0
2 1 1, 0, 1
3 1 1, 0, 1
4 2 2, 0, 1, 2
5 2 2, 0, 1, 2
6 3 2, 0, 1, 3
7 6 3, 0, 1, 3, 6
8 0 0, 0
9 8 1, 0, 8
10 8 1, 0, 8
11 8 1, 0, 8
12 9 2, 0, 8, 9
13 9 2, 0, 8, 9
14 12 3, 0, 8, 9, 12
15 12 3, 0, 8, 9, 12
16 15 4, 9, 12, 15
17 15 4, 9, 12, 15
18 3 2, 0, 1, 3
19 2 2, 0, 1, 2
20 6 3, 0, 1, 3, 6
21 8 1, 0, 8

The above is an SQL statement to implement the whole process of MYSQL recursive query. I hope it will be helpful for your learning.