Teraz, gdy MySQL 8.0 obsługuje zapytania rekurencyjne , możemy powiedzieć, że wszystkie popularne bazy danych SQL obsługują zapytania rekurencyjne w standardowej składni.
WITH RECURSIVE MyTree AS (
SELECT * FROM MyTable WHERE ParentId IS NULL
UNION ALL
SELECT m.* FROM MyTABLE AS m JOIN MyTree AS t ON m.ParentId = t.Id
)
SELECT * FROM MyTree;
Testowałem zapytania rekurencyjne w MySQL 8.0 w mojej prezentacji Recursive Query Throwdown w 2017 roku.
Poniżej moja oryginalna odpowiedź z 2008 roku:
Istnieje kilka sposobów przechowywania danych o strukturze drzewa w relacyjnej bazie danych. To, co pokazano w przykładzie, wykorzystuje dwie metody:
- Lista Adjacency (kolumna „macierzysta”) i
- Wyliczenie ścieżki (kropkowane liczby w kolumnie z Twoim imieniem).
Inne rozwiązanie nazywa się zestawami zagnieżdżonymi i można je również przechowywać w tej samej tabeli. Przeczytaj „ Drzewa i hierarchie w SQL for Smarties ” Joe Celko, aby uzyskać więcej informacji na temat tych projektów.
Zwykle wolę projekt o nazwie Tabela zamknięcia (aka „Adjacency Relation”) do przechowywania danych o strukturze drzewa. Wymaga kolejnej tabeli, ale wyszukiwanie drzew jest dość łatwe.
Omawiam tabelę zamknięcia w mojej prezentacji Modele danych hierarchicznych z SQL i PHP oraz w mojej książce Antypatterny SQL: unikanie pułapek programowania baz danych .
CREATE TABLE ClosureTable (
ancestor_id INT NOT NULL REFERENCES FlatTable(id),
descendant_id INT NOT NULL REFERENCES FlatTable(id),
PRIMARY KEY (ancestor_id, descendant_id)
);
Przechowuj wszystkie ścieżki w tabeli zamknięcia, gdzie istnieje bezpośrednie pochodzenie od jednego węzła do drugiego. Dołącz wiersz dla każdego węzła, aby sam się do niego odwoływał. Na przykład używając zestawu danych pokazanego w pytaniu:
INSERT INTO ClosureTable (ancestor_id, descendant_id) VALUES
(1,1), (1,2), (1,4), (1,6),
(2,2), (2,4),
(3,3), (3,5),
(4,4),
(5,5),
(6,6);
Teraz możesz uzyskać drzewo zaczynające się od węzła 1 w następujący sposób:
SELECT f.*
FROM FlatTable f
JOIN ClosureTable a ON (f.id = a.descendant_id)
WHERE a.ancestor_id = 1;
Dane wyjściowe (w kliencie MySQL) wyglądają następująco:
+----+
| id |
+----+
| 1 |
| 2 |
| 4 |
| 6 |
+----+
Innymi słowy, węzły 3 i 5 są wykluczone, ponieważ są częścią oddzielnej hierarchii, nie schodząc z węzła 1.
Re: komentarz e-satis na temat bezpośrednich dzieci (lub bezpośredniego rodzica). Możesz dodać path_lengthkolumnę „ ” ClosureTabledo, aby ułatwić zapytanie dotyczące bezpośredniego dziecka lub rodzica (lub dowolnej innej odległości).
INSERT INTO ClosureTable (ancestor_id, descendant_id, path_length) VALUES
(1,1,0), (1,2,1), (1,4,2), (1,6,1),
(2,2,0), (2,4,1),
(3,3,0), (3,5,1),
(4,4,0),
(5,5,0),
(6,6,0);
Następnie możesz dodać termin do wyszukiwania w celu zapytania o bezpośrednie elementy potomne danego węzła. Są to potomkowie, których path_lengthjest 1.
SELECT f.*
FROM FlatTable f
JOIN ClosureTable a ON (f.id = a.descendant_id)
WHERE a.ancestor_id = 1
AND path_length = 1;
+----+
| id |
+----+
| 2 |
| 6 |
+----+
Ponownie skomentuj @ashraf: „A co z sortowaniem całego drzewa [według nazwy]?”
Oto przykładowe zapytanie, aby zwrócić wszystkie węzły potomków węzła 1, dołączyć je do FlatTable, który zawiera inne atrybuty węzła, takie jak name, i posortować według nazwy.
SELECT f.name
FROM FlatTable f
JOIN ClosureTable a ON (f.id = a.descendant_id)
WHERE a.ancestor_id = 1
ORDER BY f.name;
Ponownie skomentuj @Nate:
SELECT f.name, GROUP_CONCAT(b.ancestor_id order by b.path_length desc) AS breadcrumbs
FROM FlatTable f
JOIN ClosureTable a ON (f.id = a.descendant_id)
JOIN ClosureTable b ON (b.descendant_id = a.descendant_id)
WHERE a.ancestor_id = 1
GROUP BY a.descendant_id
ORDER BY f.name
+------------+-------------+
| name | breadcrumbs |
+------------+-------------+
| Node 1 | 1 |
| Node 1.1 | 1,2 |
| Node 1.1.1 | 1,2,4 |
| Node 1.2 | 1,6 |
+------------+-------------+
Użytkownik zaproponował dziś edycję. Moderatorzy SO zaakceptowali zmianę, ale ja ją cofam.
Edycja sugerowała, że ORDER BY w ostatnim zapytaniu powyżej powinien być ORDER BY b.path_length, f.name, prawdopodobnie, aby upewnić się, że kolejność jest zgodna z hierarchią. Ale to nie działa, ponieważ porządkuje „Node 1.1.1” po „Node 1.2”.
Jeśli chcesz, aby porządkowanie pasowało do hierarchii w rozsądny sposób, jest to możliwe, ale nie tylko poprzez uporządkowanie według długości ścieżki. Na przykład zobacz moją odpowiedź na hierarchiczną bazę danych MySQL Closure Table - Jak wyciągać informacje we właściwej kolejności .