MySQL条sql语句查询子节点的所有父节点信息

在 SQL Server 中，使用 CTE 表达式很容易做到无限层次父子关系查询；在不支持CTE表达式的版本中，借助函数递归也可以轻松实现。
在 MySQL 中，这个需求的实例稍显复杂， MySQL 中没有支持递归的查询，没有表值函数，函数不支持递归，所以通常都是用循环实现，显得比较别扭。今天看到一个用单条语句实现的递归查询，想法独特，分享一下。

表结构和数据

create table sys_menu(
	id int primary key auto_increment,
	name varchar(200),
	parent_id int
);

INSERT INTO sys_menu (`id`, `name`, `parent_id`) VALUES (1, '一级菜单', NULL);
INSERT INTO sys_menu (`id`, `name`, `parent_id`) VALUES (2, '二级菜单', 1);
INSERT INTO sys_menu (`id`, `name`, `parent_id`) VALUES (3, '三级菜单', 2);
INSERT INTO sys_menu (`id`, `name`, `parent_id`) VALUES (4, '四级菜单', 3);
INSERT INTO sys_menu (`id`, `name`, `parent_id`) VALUES (5, '用户管理1', NULL);
INSERT INTO sys_menu (`id`, `name`, `parent_id`) VALUES (6, '用户管理2', 5);
INSERT INTO sys_menu (`id`, `name`, `parent_id`) VALUES (7, '用户管理3', 6);

查询 id = 5 的所有父级次菜单

select m.* from (select @id as _id,(select @id:=parent_id from sys_menu where id = _id)  from (select @id:=(select parent_id from sys_menu where id = 4)) vm,sys_menu m 
where @id is not null) vm inner join sys_menu  m where id = vm._id

解析：
第一部分：创建变量然后与sys_menu表连接

select * from (
	select @id:=(select parent_id from sys_menu where id = 4)
) vm,sys_menu m

第二部分： 将查询出来的parent_id福字给变量@id

只改变select * 为 @id as _id,(select @id:=parent_id from sys_menu where id = _id) where是为了过滤其他的null值
这一步已经获取到所有的父类id了

select @id as _id,(select @id:=parent_id from sys_menu where id = _id)  
from (
	select @id:=(select parent_id from sys_menu where id = 4)
) vm,sys_menu m 
where @id is not null 

第三部分：与sys_menu表联合查询，获取父类菜单的所有信息

select sys_menu.* from (
	select @id as _id,(select @id:=parent_id from sys_menu where id = _id)  
from (
	select @id:=(select parent_id from sys_menu where id = 4)
) vm,sys_menu m 
where @id is not null 
) vm,sys_menu  where vm._id = sys_menu.id

根据这个父级查询方法，很容易可以写出根据父节点查询所有子节点的sql

SELECT
	*
FROM
	(
		SELECT
			@ids AS _ids,
			(
				SELECT
					@ids := GROUP_CONCAT(id)
				FROM
					t_user
				WHERE
					FIND_IN_SET(parent_id, @ids)
			) AS cids,
			@l := @l + 1 AS lvl
		FROM
			t_user,
			(SELECT @ids := 1, @l := 0) b -- 这里的1是用户id
		WHERE
			@ids IS NOT NULL
	) id,
	t_user DATA
WHERE
	FIND_IN_SET(DATA .id, id._ids)
ORDER BY
	lvl,
	id