MySQL树形结构查询代码实例

mysql树形结构查询代码实例

drop procedure if exists query_node_deep;
create procedure query_node_deep(in rootid int(11))
begin
    create temporary table if not exists tmptable(
        sno int primary key auto_increment,
        nid int(11),
        deep int(11)
    );
    delete from tmptable;

    call create_node_deep(rootid, 0, 1);
    select tmptable.*,api_node.* from tmptable,api_node where tmptable.nid=api_node.id order by tmptable.sno;

end;

drop procedure if exists create_node_deep;
create procedure create_node_deep(in rootid int(11),in ndepth int, in max_n int)
    begin
            declare done int default 0;
            declare b varchar(20);
            declare cur1 cursor for select id from api_node where parent_id=rootid;
            declare continue handler for not found set done = 1;
        if (max_n > 0) then
            insert into tmptable values (null,rootid,ndepth);
            set @@max_sp_recursion_depth = 7;
            open cur1;

            fetch cur1 into b;
            while done=0 do
                call create_node_deep(b,ndepth+1, max_n-1);
                fetch cur1 into b;
            end while;
            close cur1;
        end if;
    end;


call query_node_deep(17);