[mysql]通过id获取下级所有节点(包含自身)或者获取所有上级节点(包含自身)

获取所有上级节点(包含自身)

SELECT
	group_concat(T1._id) as id  
FROM
	(
SELECT
	@r AS _id,
	( SELECT @r := parent_id FROM table_name WHERE id = _id ) AS parent_id,
	@l := @l + 1 AS lvl 
FROM
	( SELECT @r := 1243380158908596224 id, @l := 0 ) vars,
	table_name h 
WHERE
	@r <> 0 
	) T1
	JOIN table_name T2 ON T1._id = T2.id 
ORDER BY
	T1.lvl DESC

 

获取所有下级节点(包含自身)

SELECT
	group_concat( T1._id ) AS id 
FROM
	(
SELECT
	@r AS _id,
	( SELECT @r := group_concat( t.id ) FROM table_name t WHERE FIND_IN_SET ( t.parent_id, _id ) ) AS parent_id,
	@l := @l + 1 AS lvl 
FROM
	( SELECT @r := 1243380097210384384, @l := 0 ) vars,
	table_name h 
WHERE
	@r > 0 
	) T1