1.根据父级ID递归查询所有下级ID
SELECT id FROM ( SELECT t1.id, IF ( FIND_IN_SET( t1.parent_id, @pids ) > 0, @pids := CONCAT( @pids, ',', t1.id ), 0 ) AS ischild FROM ( SELECT id, parent_id FROM table_name t ORDER BY parent_id, id ) t1, ( SELECT @pids := '16' ) t2 ) t3 WHERE ischild != 0 OR id = '16'此方法,通过FIND_IN_SET找出当前数据的parent_id是否在@pids的满足条件的字符串当中。如果符合,则将当前数据的id加入@pids；否则isChild设置为0。依次往下查询,最终找出isChild不为0的即为满足条件的数据id集合。
根据子级ID递归查询所有父级ID SELECT t2.id FROM ( SELECT @r AS _id, ( SELECT @r := parent_id FROM table_name WHERE id = _id ) AS parent_id, @s := @s + 1 AS sort FROM ( SELECT @r := 23, @s := 0 ) temp, table_name WHERE @r > 0 ) t1 JOIN table_name t2 ON t1._id = t2.id ORDER BY t1.sort DESC