How to implement unlimited levels of parent-child relationship query in a single sentence in MySQL

In SQL Server, it is easy to use CTE expressions to query infinite levels of parent-child relationships; in versions that do not support CTE expressions, it can also be easily achieved with the help of function recursion.
In MySQL, the example of this requirement is slightly complicated. There is no query that supports recursion in MySQL, there is no table-valued function, and the function does not support recursion, so it is usually implemented with a loop, which seems awkward. Today I saw a recursive query implemented with a single statement. I have a unique idea and would like to share it.

Recommended related mysql video tutorials: "mysql tutorial"

Table structure and data

CREATE TABLE table1(id int, name varchar(10), parent_id int); 
INSERT table1 VALUES 
(1, ‘Home’,        0), 
(2, ‘About’,       1), 
(3, ‘Contact’,     1), 
(4, ‘Legal’,         2), 
(5, ‘Privacy’,      4), 
(6, ‘Products’,   1), 
(7, ‘Support’,     2);

Query all parents of id = 5

SELECT ID.level, DATA.* FROM( 

    SELECT 

        @id as _id, 

        (   SELECT @id := parent_id 

            FROM table1 

            WHERE id = @id 

        ) as _pid, 

        @l := @l+1 as level 

    FROM table1, 

        (SELECT @id := 5, @l := 0 ) b 

    WHERE @id > 0 
) ID, table1 DATA 
WHERE ID._id = DATA.id 
ORDER BY level;

According to this parent query method, it is easy to write a query for all children. The following query id=2 for all children

SELECT ID.level, DATA.* FROM( 

    SELECT 

        @ids as _ids, 

        (   SELECT @ids := GROUP_CONCAT(id) 

            FROM table1 

            WHERE FIND_IN_SET(parent_id, @ids) 

        ) as cids, 

        @l := @l+1 as level 

    FROM table1, 

        (SELECT @ids :=’1’, @l := 0 ) b 

    WHERE @ids IS NOT NULL 
) id, table1 DATA 
WHERE FIND_IN_SET(DATA.id, ID._ids) 
ORDER BY level, id

This article explains how to make a single sentence in MySQL Realize unlimited levels of parent-child relationship query, please pay attention to the PHP Chinese website for more related content.

Related recommendations:

How to access SQL Server FileStream with progress

What to do when you forget your SQL Server administrator password How to deal with it

A brief analysis of the use of concat and group_concat in MySQL

The above is the detailed content of How to implement unlimited levels of parent-child relationship query in a single sentence in MySQL. For more information, please follow other related articles on the PHP Chinese website!