MySQL查询中的递归循环是否可能?

好吧,一旦有了初始ID,就不能快速使用PHP循环生成一组变量,用于为SQL查询生成“where”语句吗?

你可以用一个简单的查询来完成,不需要连接。。。如果您更改模式以使信息易于提取。查找 nested set model .

Recursively check the parents of a child in a database

-- TABLES

drop table if exists pages;
create table pages
(
page_id smallint unsigned not null auto_increment primary key,
title varchar(255) not null,
parent_page_id smallint unsigned null,
key (parent_page_id)
)
engine = innodb;

-- TEST DATA

insert into pages (title, parent_page_id) values
('Page 1',null), 
('Page 2',null), 
   ('Page 1-2',1), 
      ('Page 1-2-1',3), 
      ('Page 1-2-2',3), 
   ('Page 2-1',2), 
   ('Page 2-2',2);


-- STORED PROCEDURES

drop procedure if exists page_parents;

delimiter #

create procedure page_parents
(
in p_page_id smallint unsigned
)
begin

declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;

create temporary table hier(
 parent_page_id smallint unsigned, 
 page_id smallint unsigned, 
 depth smallint unsigned default 0
)engine = memory;

insert into hier select parent_page_id, page_id, v_depth from pages where page_id = p_page_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table tmp engine=memory select * from hier;

while not v_done do

    if exists( select 1 from pages pg inner join hier on pg.page_id = hier.parent_page_id and hier.depth = v_depth) then

        insert into hier 
            select pg.parent_page_id, pg.page_id, v_depth + 1 from pages pg
            inner join tmp on pg.page_id = tmp.parent_page_id and tmp.depth = v_depth;

        set v_depth = v_depth + 1;          

        truncate table tmp;
        insert into tmp select * from hier where depth = v_depth;

    else
        set v_done = 1;
    end if;

end while;

select 
 pg.page_id,
 pg.title as page_title,
 b.page_id as parent_page_id,
 b.title as parent_page_title,
 hier.depth
from 
 hier
inner join pages pg on hier.page_id = pg.page_id
left outer join pages b on hier.parent_page_id = b.page_id
order by
 hier.depth, hier.page_id;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #

delimiter ;

-- TESTING (call this stored procedure from php)

call page_parents(5);
call page_parents(7);

如果假设用户使用论坛的物理层次结构导航,只需使用大量左连接,如下所示:

select current.forum as current,
        parent1.forum as history1,
        parent2.forum as history2,
        parent3.forum as history3,
        parent4.forum as history4,
        parent5.forum as history5,
        parent6.forum as history6
from forum current
left join forum parent1 on parent1.id = current.parentid
left join forum parent2 on parent2.id = parent1.parentid
left join forum parent3 on parent3.id = parent2.parentid
left join forum parent4 on parent4.id = parent3.parentid
left join forum parent5 on parent5.id = parent4.parentid
left join forum parent6 on parent6.id = parent5.parentid