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

Der beste Ansatz, den ich entwickelt habe, ist

1. Verwenden Sie Abstammung, um Bäume zu speichern\zu sortieren\zu verfolgen. Das ist mehr als genug und funktioniert tausendmal schneller beim Lesen als jeder andere Ansatz. Es ermöglicht auch, bei diesem Muster zu bleiben, auch wenn sich die DB ändert (da JEDE DB dieses Muster erlauben wird).
2. Verwenden Sie eine Funktion, die die Abstammung für eine bestimmte ID bestimmt.
3. Verwenden Sie sie wie gewünscht (in Selects oder bei CUD-Operationen oder sogar durch Jobs).

Eine Beschreibung des Abstammungsansatzes finden Sie beispielsweise hier oder hier. Was die Funktion betrifft - das hat mich inspiriert.

Am Ende - eine mehr oder weniger einfache, relativ schnelle und EINFACHE Lösung.

Der Körper der Funktion

-- --------------------------------------------------------------------------------
-- Routinendatendefinition
-- Hinweis: Kommentare vor und nach dem Routinenkörper werden nicht vom Server gespeichert
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS text CHARSET utf8
    READS SQL DATA
BEGIN

 DECLARE v_rec INT DEFAULT 0;

 DECLARE done INT DEFAULT FALSE;
 DECLARE v_res text DEFAULT '';
 DECLARE v_papa int;
 DECLARE v_papa_papa int DEFAULT -1;
 DECLARE csr CURSOR FOR 
  select _id,parent_id -- @n:=@n+1 as rownum,T1.* 
  from 
    (SELECT @r AS _id,
        (SELECT @r := table_parent_id FROM table WHERE table_id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := the_id, @l := 0,@n:=0) vars,
        table m
    WHERE @r <> 0
    ) T1
    where T1.parent_id is not null
 ORDER BY T1.lvl DESC;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open csr;
    read_loop: LOOP
    fetch csr into v_papa,v_papa_papa;
        SET v_rec = v_rec+1;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- add first
        IF v_rec = 1 THEN
            SET v_res = v_papa_papa;
        END IF;
        SET v_res = CONCAT(v_res,'-',v_papa);
    END LOOP;
    close csr;
    return v_res;
END

Und dann einfach

select get_lineage(the_id)

Ich hoffe, es hilft jemandem :)

Answer 2

Etwas, das hier nicht erwähnt wird, obwohl es ein wenig ähnlich wie die zweite Alternative der akzeptierten Antwort ist, aber anders ist und für große Hierarchieabfragen kostengünstig und einfach (einfügen aktualisieren löschen) Elemente, wäre das Hinzufügen einer persistenten Pfadspalte für jedes Element.

manche mögen:

id | name        | path
19 | Kategorie1   | /19
20 | Kategorie2   | /19/20
21 | Kategorie3   | /19/20/21
22 | Kategorie4   | /19/20/21/22

Beispiel:

-- Kinder von Kategorie3 abrufen:
SELECT * FROM meine_tabelle WHERE path LIKE '/19/20/21%'
-- Verschieben eines Elements:
UPDATE meine_tabelle SET path = REPLACE(path, '/19/20', '/15/16') WHERE path LIKE '/19/20/%'

Optimieren Sie die Pfadlänge und ORDER BY path mit Base36-Codierung anstelle von echten numerischen Pfad-IDs

 // Base10 => Base36
 '1' => '1',
 '10' => 'A',
 '100' => '2S',
 '1000' => 'RS',
 '10000' => '7PS',
 '100000' => '255S',
 '1000000' => 'LFLS',
 '1000000000' => 'GJDGXS',
 '1000000000000' => 'CRE66I9S'

https://de.wikipedia.org/wiki/Base36

Unterdrückung auch des Schrägstrich '/' Trennzeichens durch Verwendung einer festen Länge und Auffüllen mit der codierten ID

Detaillierte Optimierungserklärung hier: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/

AUFTRAG

Erstellen einer Funktion oder Prozedur zum Aufteilen des Pfads, um Vorfahren eines Elements abzurufen

Answer 3

Einfache Abfrage zur Auflistung von Kindern der ersten Rekursion:

select @pv:=id as id, name, parent_id
from products
join (select @pv:=19)tmp
where parent_id=@pv

Ergebnis:

id  name        parent_id
20  Kategorie2   19
21  Kategorie3   20
22  Kategorie4   21
26  Kategorie24  22

...mit left join:

select
    @pv:=p1.id as id
  , p2.name as parent_name
  , p1.name name
  , p1.parent_id
from products p1
join (select @pv:=19)tmp
left join products p2 on p2.id=p1.parent_id -- optional join to get parent name
where p1.parent_id=@pv

Die Lösung von @tincot zur Auflistung aller Kinder:

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) > 0
and     @pv := concat(@pv, ',', id)

Testen Sie es online mit Sql Fiddle und sehen Sie alle Ergebnisse.

http://sqlfiddle.com/#!9/a318e3/4/0

Answer 4

Sie können dies in anderen Datenbanken ganz einfach mit einer rekursiven Abfrage (YMMV zur Leistung) tun.

Der andere Weg, dies zu tun, besteht darin, zwei zusätzliche Datenbits zu speichern, einen linken und einen rechten Wert. Der linke und der rechte Wert werden aus einem Pre-Order-Durchlauf der Baumstruktur abgeleitet, die Sie darstellen.

Dies wird als modifizierter Preorder-Tree-Traversal bezeichnet und ermöglicht es Ihnen, eine einfache Abfrage auszuführen, um alle Elternwerte auf einmal zu erhalten. Es wird auch als "verschachtelte Menge" bezeichnet.

Answer 5

Verwenden Sie einfach die BlueM/tree PHP-Klasse, um einen Baum einer selbstbezogenen Tabelle in MySQL zu erstellen.

Tree und Tree\Node sind PHP-Klassen zur Verarbeitung von hierarchisch strukturierten Daten unter Verwendung von Eltern-ID-Verweisen. Ein typisches Beispiel ist eine Tabelle in einer relationalen Datenbank, in der das Feld "Eltern" jedes Datensatzes auf den Primärschlüssel eines anderen Datensatzes verweist. Natürlich kann Tree nicht nur Daten aus einer Datenbank verwenden, sondern alles: Sie liefern die Daten und Tree verwendet sie, unabhängig davon, woher die Daten stammen und wie sie verarbeitet wurden. Weiterlesen

Hier ist ein Beispiel für die Verwendung von BlueM/tree:

query('SELECT id, parent, title FROM tabellenname ORDER BY title'); 
$records = $stm->fetchAll(PDO::FETCH_ASSOC); 
$tree = new BlueM\Tree($records); 
...