Иерархический запрос в MySQL II

Надеюсь, кто-то согласился бы, что это не ответ на ответ без объяснений, поскольку код полностью документирован.

В принципе, это таблица самосоединения со строкой, имеющей ссылку на то, кто ее родитель. Хранимый proc будет использовать рабочий стол для поиска детей, детей-детей и т. Д. И поддерживать уровень.

Например, уровень = 1 представляет детей, уровень = 2 представляет внуков и т. Д.

В конце отсчитываются отсчеты. Поскольку идентификаторы находятся в рабочей таблице, расширяйте, как хотите.

схема

 create schema TaxonSandbox; -- create a separate database so it does not mess up your stuff use TaxonSandbox; -- use that db just created above (stored proc created in it) -- drop table t; CREATE TABLE t ( N int auto_increment primary key, Taxon varchar(50) not null, Parent int not null, -- 0 can mean top-most for that branch, or NULL if made nullable NameCommon varchar(50) not null, Rank int not null, key(parent) ); -- truncate table t; insert t(taxon,parent,NameCommon,rank) values ('FrogGrandpa',0,'',0); -- N=1 insert t(taxon,parent,NameCommon,rank) values ('FrogDad',1,'',0); -- N=2 (my parent is N=1) insert t(taxon,parent,NameCommon,rank) values ('FrogMe',2,'',0); -- N=3 (my parent is N=2) insert t(taxon,parent,NameCommon,rank) values ('t4',1,'',0); -- N=4 (my parent is N=2) insert t(taxon,parent,NameCommon,rank) values ('t5',4,'',0),('t6',4,'',0),('t7',5,'',0),('t8',5,'',0),('t9',7,'',0),('t10',7,'',0),('t11',7,'',0),('t12',11,'',0); 

Хранимая процедура

 use TaxonSandbox; drop procedure if exists showHierarchyUnder; DELIMITER $$ -- will be discussed separately at bottom of answer create procedure showHierarchyUnder ( theId int -- the id of the Taxon to search for it's decendants (my awkward verbiage) ) BEGIN -- theId parameter means i am anywhere in hierarchy of Taxon -- and i want all decendent Taxons declare bDoneYet boolean default false; declare working_on int; declare next_level int; -- parent's level value + 1 declare theCount int; CREATE temporary TABLE xxFindChildenxx ( -- A Helper table to mimic a recursive-like fetch N int not null, -- from OP's table called 't' processed int not null, -- 0 for not processed, 1 for processed level int not null, -- 0 is the id passed in, -1=trying to figure out, 1=children, 2=grandchildren, etc parent int not null -- helps clue us in to figure out level -- NOTE: we don't care about level or parent when N=parameter theId passed into stored proc -- in fact we will be deleting that row near the bottom or proc ); set bDoneYet=false; insert into xxFindChildenxx (N,processed,level,parent) select theId,0,0,0; -- prime the pump, get sp parameter in here -- stay inside below while til all retrieved children/children of children are retrieved while (!bDoneYet) do -- see if there are any more to process for children -- simply look in worktable for ones where processed=0; select count(*) into theCount from xxFindChildenxx where processed=0; if (theCount=0) then -- found em all, we are done inside this while loop set bDoneYet=true; else -- one not processed yet, insert its children for processing SELECT N,level+1 INTO working_on,next_level FROM xxFindChildenxx where processed=0 limit 1; -- order does not matter, just get one -- insert the rows where the parent=the one we are processing (working_on) insert into xxFindChildenxx (N,processed,level,parent) select N,0,next_level,parent from t where parent=working_on; -- mark the one we "processed for children" as processed -- so we processed a row, but its children rows are yet to be processed update xxFindChildenxx set processed=1 where N=working_on; end if; end while; delete from xxFindChildenxx where N=theId; -- don't really need the top level row now (stored proc parameter value) select level,count(*) as lvlCount from xxFindChildenxx group by level; drop table xxFindChildenxx; END $$ -- tell mysql that it has reached the end of my block (this is important) DELIMTER ; -- sets the default delimiter back to a semi-colon 

Тестирование сохраненного Proc

 use TaxonSandbox; -- create a separate database so it does not mess up your stuff call showHierarchyUnder(1); +-------+----------+ | level | lvlCount | +-------+----------+ | 1 | 2 | | 2 | 3 | | 3 | 2 | | 4 | 3 | | 5 | 1 | +-------+----------+ 

Итак, есть 2 ребенка, 3 внука, 2 правнука, 3 великих и 1 великий великий

Если бы кто-то передал идентификатор сохраненному процессу, который не существует, или тот, у которого нет детей, строки результатов не возвращаются.

Редактировать: другие комментарии, из-за того, что вы оставите OP, зависящий от понимания его первого хранимого процесса создания, я считаю. Плюс другие вопросы, которые указывают здесь.

Разделители

Разделители важны для того, чтобы обернуть блок сохраненного создания proc. Причина в том, что mysql понимает, что последовательность последующих инструкций остается частью хранимой процедуры до тех пор, пока она не достигнет указанного разделителя. В приведенном выше случае я составил один из них, называемый $$, который отличается от разделителя по умолчанию в виде двоеточия, к которому мы все привыкли. Таким образом, когда в заархивированном процессе во время создания встречается двоеточие, движок db будет рассматривать его как один из многих утверждений внутри него, а не прекращать сохраненное создание proc. Не делая этого ограничительного обертывания, можно потратить часы, пытаясь создать свою первую сохраненную процедуру, получив ошибку Error 1064 Syntax. В конце блока create у меня есть строка

 $$ 

которые сообщают mysql, что это конец моего блока создания, а затем разделитель по умолчанию для столбца с запятой устанавливается с вызовом

 DELIMITER ; 

Страница руководства Mysql Использование разделителей с MySqlScript . Не отличная справочная страница imo, но поверьте мне на эту. Такая же проблема при создании триггеров и событий .

PHP

Чтобы вызвать этот сохраненный proc из php, это всего лишь строка «call showHierarchyUnder (1)». Он возвращает набор результатов, как описано выше, который, как описано, может возвращать набор результатов без строк.

Помните, что 1 является параметром хранимой процедуры. И это существует в созданной базе данных, называемой TaxonSandbox если вы следовали вышеперечисленному.