Данные хранятся в одной таблице, каждая ячейка ссылается на родительскую. Таким образом формируется все дерево.
Пример таблички:
[cce lang=»mysql»] CREATE TABLE catalog(id INT NOT NULL PRIMARY,
pid INT NOT NULL,
someData text NOT NULL)[/cc]
Используем хранимую процедуру и временную таблицу
[cc lang=»mysql»]DELIMITER $$DROP PROCEDURE IF EXISTS `getIndexToTmpTable`$$
/**
main_id — идентификатор корня или метка поиска
search_id — идентификатор для начала поиска
zlevel — максимальный уровень вложенности, чтобы не упрыгать слишком глубоко,
— установить -1 — чтобы отключить ограничение по вложенности
sublevel — текущий уровень вложенности, для того чтобы складировать в базу
— установить 1
*/
CREATE PROCEDURE `getIndexToTmpTable` (
in main_id INT,
in search_id INT,
in zlevel INT,
in sublevel INT )
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE catalog_id INT;
DECLARE catalog_pid INT;
DECLARE cur1 CURSOR FOR select id,pid from catalog where pid=search_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
IF sublevel<=1 THEN /** чтобы установть только раз*/
IF zlevel<=0 THEN
/** число наобум */
SET max_sp_recursion_depth= 15;
ELSE
SET max_sp_recursion_depth= zlevel+1;
END IF;
END IF;
OPEN cur1;
IF main_id = search_id THEN
/** вставим саму запись, чтобы был полный боекомплект */
insert into tmp__index set
id = main_id,
pid =(select pid from catalog where id=main_id limit 1),
rid =main_id,
level = sublevel-1;
END IF;
/** нужно влючить глубину рекурсии */
REPEAT
FETCH cur1 INTO catalog_id,catalog_pid;
IF NOT done THEN
/** вставим текущую найденную запись */
insert into tmp__index set
id = catalog_id,
pid = catalog_pid,
rid = main_id,
level = sublevel;
/** спустимся по ниже */
call getIndexToTmpTable(main_id,catalog_id,zlevel,sublevel+1);
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END$$
DELIMITER ;[/cc]
Вызывать так:
[cc lang=»mysql»]CREATE TEMPORARY TABLE IF NOT EXISTS tmp__index(id int,pid int ,rid int, level int);DELETE from tmp__index;
call getIndexToTmpTable(1,1,1,1);
select c.* from catalog as c join tmp__index as t on t.id=c.id[/cc]
Стянул с хабра, чуть подправив.