W przypadku MySQL 8+: użyj withskładni rekurencyjnej .
W przypadku MySQL 5.x: użyj zmiennych wbudowanych, identyfikatorów ścieżek lub połączeń wewnętrznych.
MySQL 8+
with recursive cte (id, name, parent_id) as (
select id,
name,
parent_id
from products
where parent_id = 19
union all
select p.id,
p.name,
p.parent_id
from products p
inner join cte
on p.parent_id = cte.id
)
select * from cte;
Podana wartość parent_id = 19powinna być ustawiona na idwartość rodzica, dla którego chcesz wybrać wszystkich potomków.
MySQL 5.x
W przypadku wersji MySQL, które nie obsługują typowych wyrażeń tabelowych (do wersji 5.7), można to osiągnąć za pomocą następującego zapytania:
select id,
name,
parent_id
from (select * from products
order by parent_id, id) products_sorted,
(select @pv := '19') initialisation
where find_in_set(parent_id, @pv)
and length(@pv := concat(@pv, ',', id))
Oto skrzypce .
Tutaj wartość podana w @pv := '19'powinna być ustawiona na idwartość rodzica, dla którego chcesz wybrać wszystkich potomków.
Działa to również wtedy, gdy rodzic ma wiele dzieci. Wymagane jest jednak, aby każdy rekord spełniał warunek parent_id < id, w przeciwnym razie wyniki nie będą kompletne.
Zmienne przypisania w zapytaniu
To zapytanie używa określonej składni MySQL: zmienne są przypisywane i modyfikowane podczas jego wykonywania. Poczyniono pewne założenia dotyczące kolejności wykonania:
fromKlauzula jest oceniana jako pierwszy. To właśnie tam @pvzostaje zainicjowany.whereKlauzula jest obliczane dla każdego rekordu w celu wydobycia od fromaliasów. Zatem w tym przypadku warunek obejmuje tylko rekordy, dla których rodzic został już zidentyfikowany jako znajdujący się w drzewie potomków (do potomków głównego rodzica dodawane są stopniowo @pv).- Warunki w tym
wherepunkcie są oceniane w kolejności, a ocena jest przerywana, gdy całkowity wynik jest pewny. Dlatego drugi warunek musi znajdować się na drugim miejscu, ponieważ dodaje on iddo listy nadrzędnej, i powinno to się zdarzyć tylko wtedy, gdy idspełni pierwszy warunek. lengthFunkcja nazywa się tylko upewnić, warunek ten jest zawsze prawdziwe, nawet jeśli pvciąg będzie z jakiegoś powodu, uzyskując wartość falsy.
Podsumowując, można uznać, że założenia te są zbyt ryzykowne, aby na nich polegać. Dokumentacja ostrzega:
możesz uzyskać oczekiwane wyniki, ale nie ma gwarancji, [...] że kolejność obliczania wyrażeń zawierających zmienne użytkownika jest niezdefiniowana.
Nawet jeśli działa to spójnie z powyższym zapytaniem, kolejność oceny może się jeszcze zmienić, na przykład po dodaniu warunków lub użyciu tego zapytania jako widoku lub zapytania podrzędnego w większym zapytaniu. Jest to „funkcja”, która zostanie usunięta w przyszłej wersji MySQL :
Poprzednie wersje MySQL umożliwiały przypisywanie wartości zmiennej użytkownika w instrukcjach innych niż SET. Ta funkcja jest obsługiwana w MySQL 8.0 w celu zachowania zgodności wstecznej, ale może zostać usunięta w przyszłej wersji MySQL.
Jak wspomniano powyżej, od MySQL 8.0 należy używać withskładni rekurencyjnej .
Wydajność
W przypadku bardzo dużych zestawów danych to rozwiązanie może find_in_setdziałać wolniej, ponieważ operacja nie jest najbardziej idealnym sposobem znalezienia liczby na liście, a na pewno nie na liście, która osiąga rozmiar w tym samym rzędzie wielkości, co liczba zwracanych rekordów.
Alternatywa 1: with recursive,connect by
Coraz więcej baz danych implementuje standardową WITH [RECURSIVE]składnię SQL: 1999 ISO dla zapytań rekurencyjnych (np. Postgres 8.4+ , SQL Server 2005+ , DB2 , Oracle 11gR2 + , SQLite 3.8.4+ , Firebird 2.1+ , H2 , HyperSQL 2.1.0+ , Teradata , MariaDB 10.2.2+ ). Od wersji 8.0 MySQL obsługuje to . Zobacz na górę tej odpowiedzi, aby użyć składni.
Niektóre bazy danych mają alternatywną, niestandardową składnię do wyszukiwania hierarchicznego, na przykład CONNECT BYklauzula dostępna w Oracle , DB2 , Informix , CUBRID i innych bazach danych.
MySQL w wersji 5.7 nie oferuje takiej funkcji. Gdy aparat bazy danych udostępnia tę składnię lub możesz przeprowadzić migrację do takiej, która to robi, jest to z pewnością najlepsza opcja. Jeśli nie, rozważ także następujące alternatywy.
Alternatywa 2: Identyfikatory w stylu ścieżki
Sprawy stają się o wiele łatwiejsze, jeśli przypisujesz idwartości zawierające informacje hierarchiczne: ścieżkę. Na przykład w twoim przypadku może to wyglądać tak:
ID | NAME
19 | category1
19/1 | category2
19/1/1 | category3
19/1/1/1 | category4
Wtedy twój selectwyglądałby tak:
select id,
name
from products
where id like '19/%'
Alternatywa 3: Powtarzane samodzielne dołączanie
Jeśli znasz górną granicę głębokości, na jaką może stać się Twoje drzewo hierarchii, możesz użyć standardowego sqlzapytania takiego:
select p6.parent_id as parent6_id,
p5.parent_id as parent5_id,
p4.parent_id as parent4_id,
p3.parent_id as parent3_id,
p2.parent_id as parent2_id,
p1.parent_id as parent_id,
p1.id as product_id,
p1.name
from products p1
left join products p2 on p2.id = p1.parent_id
left join products p3 on p3.id = p2.parent_id
left join products p4 on p4.id = p3.parent_id
left join products p5 on p5.id = p4.parent_id
left join products p6 on p6.id = p5.parent_id
where 19 in (p1.parent_id,
p2.parent_id,
p3.parent_id,
p4.parent_id,
p5.parent_id,
p6.parent_id)
order by 1, 2, 3, 4, 5, 6, 7;
Zobacz to skrzypce
Te whereokreśla, które rodzic stan chcesz pobrać potomków. W razie potrzeby możesz rozszerzyć to zapytanie o więcej poziomów.
