mysql 树结构递归查询

--向下递归查询
SELECT
	T2.*, T1.lvl
FROM
	(
		SELECT
			@r AS _parent_id,
			(
				SELECT
					@r := group_concat(id)
				FROM
					tab_test
				WHERE
					find_in_set(parent_id, _parent_id) > 0
			) AS parent_id,
			@l := @l + 1 AS lvl
		FROM
			(SELECT @r := 1, @l := 0) vars,
			tab_test h
		WHERE
			@r is not NULL
	) T1
JOIN tab_test T2 ON find_in_set(T2.id, T1.parent_id) > 0;
--向上递归查询
SELECT
	T2.*, T1.lvl
FROM
	(
		SELECT
			@r AS _id,
			(
				SELECT
					@r := parent_id
				FROM
					tab_test
				WHERE
					id = _id
			) AS parent_id,
			@l := @l + 1 AS lvl
		FROM
			(SELECT @r := 1, @l := 0) vars,
			tab_test h
	) T1
JOIN tab_test T2 ON T1._id = T2.id;

sql语句未仔细斟酌性能等其它问题，只单以sql语句简练考虑，如有问题请大家多多请教