MySQL recursive 递归

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