How does MySql use the parent id to recursively query down child nodes

This article mainly introduces how MySql uses the parent id to recursively query child nodes, which has a certain reference value, and interested friends can refer to it. I hope you can learn a lot after reading this article.

Without writing stored procedures or building database functions, it can be realized by a section of sql.

SELECT ID.LEVEL, DATA.* FROM (SELECT @ ids AS _ ids, (SELECT @ ids: = GROUP_CONCAT (region_id) FROM region WHERE FIND_IN_SET (parent_id, @ ids)) AS cids, @ l: = @ l + 1 AS LEVEL FROM region, (SELECT @ ids: = 3 @ l: = 0) b WHERE @ ids IS NOT NULL) ID, region DATA WHERE FIND_IN_SET (DATA.region_id, ID._ids) ORDER BY LEVEL

test

-- create test environment create table t_test (id int PRIMARY key, parent_id int, name varchar) insert t_test VALUES (1precol null, "China"); insert t_test VALUES (2pint 1, "North China"); insert t_test VALUES (3Power2, "Shanxi Province"); insert t_test VALUES (4Perry 2, "Beijing"); insert t_test VALUES (5je 3, "Linfen City"); insert t_test VALUES (6jre 4, "Beijing") Insert t_test VALUES (7Jing 5, "Yaodu District"); insert t_test VALUES (8Jing 6, "Chaoyang District"); insert t_test VALUES (9jue 7, "Jiefang West Road"); insert t_test VALUES (10Jing 8, "Chaoyang North Road"); SELECT * FROM t_test

Test data presentation

Query id=1 to find out what are the following places in China

SELECT ID.LEVEL, DATA.* FROM (SELECT @ ids AS _ ids, (SELECT @ ids: = GROUP_CONCAT (id) FROM t_test WHERE FIND_IN_SET (parent_id, @ ids)) AS cids, @ l: = @ l + 1 AS LEVEL FROM t_test (SELECT @ ids: = 1, @ l: = 0) b WHERE @ ids IS NOT NULL) ID, t_test DATA WHERE FIND_IN_SET (DATA.id, ID._ids) ORDER BY LEVEL

Id=3, inquire about the places below Shanxi.

SELECT ID.LEVEL, DATA.* FROM (SELECT @ ids AS _ ids, (SELECT @ ids: = GROUP_CONCAT (id) FROM t_test WHERE FIND_IN_SET (parent_id, @ ids)) AS cids, @ l: = @ l + 1 AS LEVEL FROM t_test (SELECT @ ids: = 3, @ l: = 0) b WHERE @ ids IS NOT NULL) ID, t_test DATA WHERE FIND_IN_SET (DATA.id, ID._ids) ORDER BY LEVEL

Id=4, inquire about the places below Beijing.

Finally, we will further query from id=2 in North China.

Thank you for reading this article carefully. I hope the article "how to use the parent id to recursively query child nodes" shared by the editor is helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!