Wie erstellt man eine MySQL-hierarchische rekursive Abfrage?

Question

Ich habe eine MySQL-Tabelle, die wie folgt aussieht:

id

name

parent_id

19

Kategorie1

0

20

Kategorie2

19

21

Kategorie3

20

22

Kategorie4

21

...

...

...

Jetzt möchte ich eine einzige MySQL-Abfrage haben, der ich einfach die ID [zum Beispiel id=19] übergebe und dann alle Kind-IDs bekomme [also das Ergebnis sollten IDs '20,21,22' sein]....

Die Hierarchie der Kinder ist nicht bekannt; sie kann variieren....

Ich weiß, wie man es mit einer for-Schleife macht... aber wie kann man das gleiche mit einer einzigen MySQL-Abfrage erreichen?

Answer 1

Vom Blog Verwaltung hierarchischer Daten in MySQL

Tabellenstruktur

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELEKTRONIK           |   NULL |
|           2 | FERNSEHER            |      1 |
|           3 | RÖHRE                |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | TRAGBARE ELEKTRONIK  |      1 |
|           7 | MP3-SPIELER          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD-SPIELER           |      6 |
|          10 | 2-WEGE-RADIO         |      6 |
+-------------+----------------------+--------+

Abfrage:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELEKTRONIK';

Ausgabe

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELEKTRONIK  | FERNSEHER            | RÖHRE        | NULL  |
| ELEKTRONIK  | FERNSEHER            | LCD          | NULL  |
| ELEKTRONIK  | FERNSEHER            | PLASMA       | NULL  |
| ELEKTRONIK  | TRAGBARE ELEKTRONIK  | MP3-SPIELER  | FLASH |
| ELEKTRONIK  | TRAGBARE ELEKTRONIK  | CD-SPIELER   | NULL  |
| ELEKTRONIK  | TRAGBARE ELEKTRONIK  | 2-WEGE-RADIO | NULL  |
+-------------+----------------------+--------------+-------+

Die meisten Benutzer haben früher oder später mit hierarchischen Daten in einer SQL-Datenbank gearbeitet und sicher gelernt, dass die Verwaltung hierarchischer Daten nicht für eine relationale Datenbank gedacht ist. Die Tabellen einer relationalen Datenbank sind nicht hierarchisch (wie XML), sondern einfach eine flache Liste. Hierarchische Daten haben eine Eltern-Kind-Beziehung, die nicht natürlicherweise in einer relationalen Datenbanktabelle dargestellt wird. Mehr lesen

Verweisen Sie auf den Blog für weitere Details.

BEARBEITEN:

select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv

Ausgabe:

category_id name    parent
19  Kategorie1   0
20  Kategorie2   19
21  Kategorie3   20
22  Kategorie4   21

Referenz: Wie wird die rekursive SELECT-Abfrage in MySQL durchgeführt?

Answer 2

Probieren Sie diese:

Tabellendefinition:

DROP TABLE IF EXISTS category;
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    parent_id INT,
    CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
    REFERENCES category (id)
) engine=innodb;

Experimentelle Zeilen:

INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);

Rekursive gespeicherte Prozedur:

DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
    DECLARE catname VARCHAR(20);
    DECLARE temppath TEXT;
    DECLARE tempparent INT;
    SET max_sp_recursion_depth = 255;
    SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
    IF tempparent IS NULL
    THEN
        SET path = catname;
    ELSE
        CALL getpath(tempparent, temppath);
        SET path = CONCAT(temppath, '/', catname);
    END IF;
END$$
DELIMITER ;

Wrapper-Funktion für die gespeicherte Prozedur:

DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE res TEXT;
    CALL getpath(cat_id, res);
    RETURN res;
END$$
DELIMITER ;

Beispiel für Auswahl:

SELECT id, name, getpath(id) AS path FROM category;

Ausgabe:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1                               |
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA                     |
| 24 | categoryB | category1/categoryA/categoryB           |
| 25 | categoryC | category1/categoryA/categoryC           |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+

Filtern von Zeilen mit bestimmtem Pfad:

SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';

Ausgabe:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+

Answer 3

Ich habe dasselbe für eine andere Frage hier gemacht

Mysql select rekursiv alle Kinder mit mehreren Ebenen erhalten

Die Abfrage wird sein :

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
  SELECT @pv:=(
    SELECT GROUP_CONCAT(id SEPARATOR ',')
    FROM table WHERE parent_id IN (@pv)
  ) AS lv FROM table 
  JOIN
  (SELECT @pv:=1)tmp
  WHERE parent_id IN (@pv)
) a;

Answer 4

Basierend auf der Antwort von @trincot, sehr gut erklärt, verwende ich die WITH RECURSIVE ()-Anweisung, um ein Breadcrumb zu erstellen, das die id der aktuellen Seite verwendet und rückwärts in der Hierarchie geht, um jedes parent in meiner route-Tabelle zu finden.

Also ist die Lösung von @trincot hier in die entgegengesetzte Richtung angepasst, um Eltern anstelle von Nachkommen zu finden.

Ich habe auch den Wert depth hinzugefügt, der nützlich ist, um die Reihenfolge der Ergebnisse umzukehren (ansonsten wäre das Breadcrumb verkehrt herum).

WITH RECURSIVE cte (
    `id`,
    `title`,
    `url`,
    `icon`,
    `class`,
    `parent_id`,
    `depth`
) AS (
    SELECT   
        `id`,
        `title`,
        `url`,
        `icon`,
        `class`,
        `parent_id`,
        1 AS `depth` 
    FROM     `route`
    WHERE    `id` = :id

    UNION ALL 
    SELECT 
        P.`id`,
        P.`title`,
        P.`url`,
        P.`icon`,
        P.`class`,
        P.`parent_id`,
        `depth` + 1
    FROM `route` P

    INNER JOIN cte
        ON P.`id` = cte.`parent_id`
)
SELECT * FROM cte ORDER BY `depth` DESC;

Vor dem Upgrade auf mySQL 8+ habe ich Vars verwendet, aber sie sind veraltet und funktionieren nicht mehr in meiner Version 8.0.22!

EDIT 2021-02-19: Beispiel für hierarchisches Menü

Nach dem Kommentar von @david habe ich beschlossen, zu versuchen, ein vollständiges hierarchisches Menü mit allen Knoten zu erstellen und so zu sortieren, wie ich es möchte (mit der sorting-Spalte, die Elemente in jeder Ebene sortiert). Sehr nützlich für meine Benutzer-/Authorisierungsmatrix-Seite.

Dies vereinfacht wirklich meine alte Version mit einer Abfrage auf jeder Ebene (PHP-Schleifen).

Dieses Beispiel integriert ein INNER JOIN mit der url-Tabelle, um Routen nach Website zu filtern (Mehrfach-Websites-CMS-System).

Sie können die wesentliche path-Spalte sehen, die die CONCAT()-Funktion enthält, um das Menü auf die richtige Weise zu sortieren.

SELECT R.* FROM (
    WITH RECURSIVE cte (
        `id`,
        `title`,
        `url`,
        `icon`,
        `class`,
        `parent`,
        `depth`,
        `sorting`,
        `path`
    ) AS (
        SELECT 
            `id`,
            `title`,
            `url`,
            `icon`,
            `class`,
            `parent`,
            1 AS `depth`,
            `sorting`,
            CONCAT(`sorting`, ' ', `title`) AS `path`
        FROM `route`
        WHERE `parent` = 0
        UNION ALL SELECT 
            D.`id`,
            D.`title`,
            D.`url`,
            D.`icon`,
            D.`class`,
            D.`parent`,
            `depth` + 1,
            D.`sorting`,
            CONCAT(cte.`path`, ' > ', D.`sorting`, ' ', D.`title`)
        FROM `route` D
        INNER JOIN cte
            ON cte.`id` = D.`parent`
    )
    SELECT * FROM cte
) R

INNER JOIN `url` U
    ON R.`id` = U.`route_id`
    AND U.`site_id` = 1

ORDER BY `path` ASC

Answer 5

Wenn Sie eine schnelle Lesegeschwindigkeit benötigen, ist die beste Option die Verwendung einer Closure-Tabelle. Eine Closure-Tabelle enthält eine Zeile für jedes Vorfahr/Nachfahre-Paar. In Ihrem Beispiel würde die Closure-Tabelle so aussehen:

ancestor | descendant | depth
0        | 0          | 0
0        | 19         | 1
0        | 20         | 2
0        | 21         | 3
0        | 22         | 4
19       | 19         | 0
19       | 20         | 1
19       | 21         | 3
19       | 22         | 4
20       | 20         | 0
20       | 21         | 1
20       | 22         | 2
21       | 21         | 0
21       | 22         | 1
22       | 22         | 0

Sobald Sie diese Tabelle haben, werden hierarchische Abfragen sehr einfach und schnell. Um alle Nachkommen der Kategorie 20 zu erhalten:

SELECT cat.* FROM categories_closure AS cl
INNER JOIN categories AS cat ON cat.id = cl.descendant
WHERE cl.ancestor = 20 AND cl.depth > 0

Natürlich gibt es einen großen Nachteil, wann immer Sie denormalisierte Daten wie diese verwenden. Sie müssen die Closure-Tabelle neben Ihrer Kategorientabelle pflegen. Der beste Weg ist wahrscheinlich die Verwendung von Triggern, aber es ist etwas komplex, um Inserts/Updates/Deletes für Closure-Tabellen korrekt zu verfolgen. Wie bei allem müssen Sie Ihre Anforderungen prüfen und entscheiden, welcher Ansatz für Sie am besten ist.

Bearbeiten: Siehe die Frage Welche Möglichkeiten gibt es, hierarchische Daten in einer relationalen Datenbank zu speichern? für weitere Optionen. Es gibt unterschiedliche optimale Lösungen für unterschiedliche Situationen.