Home >Database >Mysql Tutorial >How does MySql use parent id to recursively query child nodes downwards?

How does MySql use parent id to recursively query child nodes downwards?

王林
王林forward
2023-05-30 16:03:281181browse

No need to write stored procedures, no need to build database functions, just a piece of sql can be implemented

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

How does MySql use parent id to recursively query child nodes downwards?

Test

--创建测试环境
create table t_test(
	id int PRIMARY key,
	parent_id int,
	name varchar(200)
)

insert t_test VALUES(1,null,"中国");

insert t_test VALUES(2,1,"华北");

insert t_test VALUES(3,2,"山西省");
insert t_test VALUES(4,2,"北京");

insert t_test VALUES(5,3,"临汾市");
insert t_test VALUES(6,4,"北京市");


insert t_test VALUES(7,5,"尧都区");
insert t_test VALUES(8,6,"朝阳区");

insert t_test VALUES(9,7,"解放西路");
insert t_test VALUES(10,8,"朝阳北路");


SELECT * FROM t_test;

Test data display

How does MySql use parent id to recursively query child nodes downwards?

Query id=1, query what places are below 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

How does MySql use parent id to recursively query child nodes downwards?

id=3, check what places are under 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

How does MySql use parent id to recursively query child nodes downwards?

id=4, check what places are under Beijing

How does MySql use parent id to recursively query child nodes downwards?

Finally, query down from the North China region with id=2

How does MySql use parent id to recursively query child nodes downwards?

The above is the detailed content of How does MySql use parent id to recursively query child nodes downwards?. For more information, please follow other related articles on the PHP Chinese website!

Statement:
This article is reproduced at:yisu.com. If there is any infringement, please contact admin@php.cn delete