MySQL recursive 递归

MyFreeIT

已于 2024-04-24 15:58:20 修改

阅读量370

点赞数 4

分类专栏：数据库文章标签： mysql recursive tree 递归 CTE CTE 递归

于 2024-04-24 10:59:35 首次发布

创作源自于MyFreeIT

本文链接：https://blog.csdn.net/MyFreeIT/article/details/138152728

版权

数据库专栏收录该内容

53 篇文章 0 订阅

订阅专栏

本文介绍了在MySQL中使用递归查询(CTE)和变量赋值两种方法来查询多级菜单结构，包括通过子节点向上查询的CTE示例以及针对单个节点的变量赋值解决方案，以满足不同场景的需求。

摘要由CSDN通过智能技术生成

MySQL 从最内的select开始执行，但是同一个select clause可以在查询的结果上继续查询。

SELECT menu_id,parent_id,

(SELECT m1.parent_id FROM sys_menu AS m1 WHERE m1.menu_id=m.parent_id) 

FROM sys_menu AS m WHERE m.menu_id = 89

方案1.通过recursive递归

使用MySQL的CTE 方法，通过子节点向上查询

SELECT DISTINCT *  FROM (	
WITH recursive recur_menu_nodes_child AS (
		SELECT
			m.* 
		FROM
			sys_menu AS m,
			sys_user AS u,
			sys_role AS r,
			sys_role_menu AS rm 
		WHERE
			u.user_role = r.role_id 
			AND r.role_id = rm.role_id 
			AND m.menu_id = rm.menu_id 
			AND u.user_id = 1
			
			UNION ALL
			
			SELECT n.* FROM sys_menu AS n 
			JOIN recur_menu_nodes_child pn ON pn.parent_id = n.menu_id
)

SELECT * FROM recur_menu_nodes_child
) AS tab

通过UNION DISTINCT简化后

WITH recursive recur_menu_nodes_child AS (
		SELECT
			m.* 
		FROM
			sys_menu AS m,
			sys_user AS u,
			sys_role AS r,
			sys_role_menu AS rm 
		WHERE
			u.user_role = r.role_id 
			AND r.role_id = rm.role_id 
			AND m.menu_id = rm.menu_id 
			AND u.user_id = 247 
			
			UNION DISTINCT
			
			SELECT n.* FROM sys_menu AS n 
			JOIN recur_menu_nodes_child pn ON pn.parent_id = n.menu_id
)SELECT * FROM recur_menu_nodes_child

方案2. 通过变量赋值

只适用于一个节点，而不是多个节点。

SELECT
	d3.* 
FROM
	(
	
	SELECT
		@r AS id,
		( SELECT @r := parent_id FROM sys_menu WHERE menu_id = @r ) AS parent_id 
	FROM
		( SELECT @r := 89 ) child,sys_menu hd
		
) d2
	
	INNER JOIN sys_menu d3 ON d2.id = d3.menu_id 
ORDER BY
	d3.menu_id

或者

更简单点

SELECT m.* FROM sys_menu AS m,(
SELECT 
        @r AS _id,
        (SELECT @r := parent_id FROM sys_menu WHERE menu_id = _id) AS parent_id
        FROM
        (SELECT @r := 89) vars, sys_menu AS h
        WHERE @r <> 0 
) AS tab WHERE m.menu_id = tab._id