MySQL树结构递归查询

在日常开发中我们经常会遇到树形结构数据的处理，如：组织机构之类的情况。在表结构通常会采用id、parent_id这种设计方案。一个常见的需求：查询某个节点下的所有子节点。

初始化表数据

drop table if exists tbl_tree;
create table tbl_tree (
    id int(11) not null auto_increment
  , parent_id int(11)
  , name varchar(255)
  , primary key (id)
);

insert into tbl_tree (id, parent_id, name) values 
('1', '0', '家配成品类'),
('2', '0', '营销物料类'),
('3', '1', '家配'),
('4', '1', '寝具'),
('5', '1', '衣百货'),
('6', '2', '物料'),
('7', '3', '凳类'),
('8', '3', '椅类'),
('9', '3', '床类'),
('10', '3', '餐椅类'),
('11', '3', '桌台类'),
('12', '3', '沙发类'),
('13', '3', '窗帘类'),
('14', '3', '茶几类'),
('15', '3', '床头柜类'),
('16', '3', '软床类'),
('17', '3', '按摩护理类'),
('18', '3', '其它配套类'),
('19', '4', '软床类'),
('20', '4', '床垫类'),
('21', '4', '床配类'),
('22', '4', '床头柜类'),
('23', '4', '排骨架类'),
('24', '4', '销售道具'),
('25', '5', '礼包类'),
('26', '5', '日用品类'),
('27', '5', '家居饰品类'),
('28', '5', '家居小家电类'),
('29', '5', '其它功能五金类'),
('30', '6', '物料类'),
('31', '6', '服装物料类'),
('32', '6', '衣柜线宣传物料类'),
('33', '6', '其他展示产品'),
('34', '20', '床垫A'),
('35', '20', '床垫B')
;

递归查询

查询某个节点所有子节点或父节点

• 方法一: 通过CET递归查询

mysql8.0 新特性

• 下面的sql从最高级往下找，执行速度比上面的sql要快

with recursive tb as (
    select parent_id as pid
        , id
        , name
        , parent_id
        , 1 as lv
        , concat_ws(',',id,name) as _path
        , 0 as last_flg
    from tbl_tree 
    where id=1

    union all

    select a.id as pid
        , b.id
        , b.name
        , b.parent_id
        , coalesce(a.lv,0)+1 as lv
        , concat(a._path, "->", concat_ws(',',b.id,b.name)) as _path
        , case when b.id is null then 1 else 0 end as last_flg
    from tb a
    left join tbl_tree b on a.id = b.parent_id
        where a.id is not null
) 
select a.id
    , a.name
    , a.parent_id
    , a.lv
    , a._path
    , b.last_flg
from tb a
left join tb b on a.id=b.pid
where a.id is not null 
    and b.last_flg=1
;

结果如下图所示：

• 从底层出发，往上层找，直到找到parent_id为空的记录，复杂度比较高

with recursive tb as (
    select id
        , name
        , parent_id
        , 1 as lv
        , concat_ws(',',id,name) as _path
        , 0 as last_flg
    from tbl_tree
    where id=33

    union all

    select b.id
        , b.name
        , b.parent_id
        , a.lv + 1 as lv
        , concat(a._path, "<-", concat_ws(',',b.id,b.name)) as _path
        , case when b.parent_id=0 then 1 else 0 end as last_flg
    from tb a
    left join tbl_tree b on a.parent_id=b.id
        where a.parent_id<>0
) 
select id
    , name
    , parent_id
    , lv
    , _path
    , last_flg
from tb
where last_flg=1
;

结果如下图所示：

• 方法二: SQL实现

此方法建议在数据量比较少的情况下使用

select id
    , name
    , parent_id 
    , lv
    , concat_ws('->',paths,cid) as _path
from (
select id
    , parent_id
    , name
        , cid
        , pid
    , @le:=if(parent_id=0,1,if(locate(concat('|',pid,':'),@pathlevel)>0,substring_index(substring_index(@pathlevel,concat('|',pid,':'),-1),'|',1) +1,@le+1)) lv
    , @pathlevel:=concat(@pathlevel,'|',cid,':',@le ,'|') as pathlevel
    , @pathnodes:=if(parent_id=0,pid,concat_ws('->',if(locate(concat('|',pid,':'),@pathall)>0,substring_index(substring_index(@pathall,concat('|',pid,':'),-1),'|',1),@pathnodes),pid)) as paths
    , @pathall:=concat(@pathall,'|',cid,':', @pathnodes ,'|') pathall 
from (
    select concat_ws(',',a.id,a.name) as cid
        , case when b.id is null then concat_ws(',',a.parent_id,'根节点') else concat_ws(',',b.id,b.name) end as pid
        , a.id
        , a.parent_id
        , a.name 
    from tbl_tree a
    left join tbl_tree b on a.parent_id=b.id
) t, 
(select @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv
order by parent_id,id
) src
order by parent_id

结果如下图所示：