查询所有子id,父id为2.
select id from oauth_tm_org where id = 2
union
select t.id
from (select * from oauth_tm_org where parent_id is not null order by parent_id,id) t,
(select @pid := 2) pd
where FIND_IN_SET(parent_id,@pid) > 0
and @pid := CONCAT(@pid,',',id)
查询所有父id,子id为2
SELECT
t2.id
FROM
(
SELECT
@r AS _id,
( SELECT @r := parent_id FROM oauth_tm_org WHERE id = _id ) AS parent_id,
@l := @l + 1 AS lvl
FROM
( SELECT @r := 2, @l := 0 ) vars,
oauth_tm_org
WHERE
@r != 0
) t1
JOIN oauth_tm_org t2 ON t1._id = t2.id
ORDER BY
t1.lvl DESC
查询所有子孙节点
SELECT
DATA.id
FROM
(
SELECT
@ids AS _ids,
( SELECT @ids := GROUP_CONCAT( id ) FROM oauth_tm_org WHERE FIND_IN_SET( parent_id, @ids ) ) AS cids,
@l := @l + 1 AS LEVEL
FROM
oauth_tm_org,
( SELECT @ids := '40', @l := 0 ) b
WHERE
@ids IS NOT NULL
) ID,
oauth_tm_org DATA
WHERE
FIND_IN_SET( DATA.id, ID._ids )
ORDER BY
LEVEL,
id
一个注意点,会导致查询缺失数据。group_concat的长度默认为1024.因此需要修改他的最大长度。
局部修改:
SET GLOBAL group_concat_max_len = 10240000;
SET SESSION group_concat_max_len = 10240000;
永久修改:
去mysql的init的文件添加一行
group_concat_max_len=10240000
查看:
show variables like 'group_concat_max_len';