Gewusst wie: Abfragen einer MySql-Tabelle zum anzeigen der root und die subchild.

Ist es technisch möglich, das zu tun rekursive hierarchische Abfragen in MySQL gespeicherte Prozeduren.

Hier ist eine angepasste Szenario:

CREATE TABLE `user` (
  `UserID` int(16) unsigned NOT NULL,
  `UserName` varchar(32),
  `ParentID` int(16) DEFAULT NULL,
  `TopID` int(16) DEFAULT NULL,
  PRIMARY KEY (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO user VALUES (1, 'abc', NULL, NULL), (2, 'edf', 1, 1), (3, 'gef', 1, 1), 
 (4, 'huj', 3, 1), (5, 'jdi', 4, 1), (6, 'das', 2, 1), (7, 'new', NULL, NULL), 
 (8, 'gka', 7, 7);

DELIMITER $$
DROP PROCEDURE IF EXISTS `Hierarchy` $$
CREATE PROCEDURE `Hierarchy` (IN GivenID INT, IN initial INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE next_id INT;

    -- CURSOR TO LOOP THROUGH RESULTS --
    DECLARE cur1 CURSOR FOR SELECT UserID FROM user WHERE ParentID = GivenID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- CREATE A TEMPORARY TABLE TO HOLD RESULTS --
    IF initial=1 THEN
        -- MAKE SURE TABLE DOESN'T CONTAIN OUTDATED INFO IF IT EXISTS (USUALLY ON ERROR) --
        DROP TABLE IF EXISTS OUT_TEMP; 
        CREATE TEMPORARY TABLE OUT_TEMP (userID int, UserName varchar(32));
    END IF;

    -- ADD OURSELF TO THE TEMPORARY TABLE --
    INSERT INTO OUT_TEMP SELECT UserID, UserName FROM user WHERE UserID = GivenID;

    -- AND LOOP THROUGH THE CURSOR --
    OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO next_id;

        -- NO ROWS FOUND, LEAVE LOOP --
        IF done THEN
        LEAVE read_loop;
        END IF;

        -- NEXT ROUND --
        CALL Hierarchy(next_id, 0);     
    END LOOP;

    CLOSE cur1;

    -- THIS IS THE INITIAL CALL, LET'S GET THE RESULTS --
    IF initial=1 THEN
    SELECT * FROM OUT_TEMP;
        -- CLEAN UP AFTER OURSELVES --
        DROP TABLE OUT_TEMP; 
    END IF;
END $$
DELIMITER ;

CALL Hierarchy(3,1);
+--------+----------+
| userID | UserName |
+--------+----------+
|      3 | gef      |
|      4 | huj      |
|      5 | jdi      |
+--------+----------+
3 rows in set (0.07 sec)

Query OK, 0 rows affected (0.07 sec)

CALL Hierarchy(1,1);
+--------+----------+
| userID | UserName |
+--------+----------+
|      1 | abc      |
|      2 | edf      |
|      6 | das      |
|      3 | gef      |
|      4 | huj      |
|      5 | jdi      |
+--------+----------+
6 rows in set (0.10 sec)

Query OK, 0 rows affected (0.10 sec)

Zeitpunkt einige Vorbehalte:

• Da diese den rekursiven Aufruf einer gespeicherten Prozedur, die Sie brauchen, um erhöhen Sie die Größe der max_sp_recursion_depth, die hat einen max Wert von 255 (Standardwert 0).

• Meine Ergebnisse auf einem nicht-ausgelasteten server mit der begrenzten test-Daten (10 Tupel der user Tabelle) nahm 0.07-0.10 Sekunden. Die Leistung ist so, dass es vielleicht am besten sein, legen Sie die Rekursion in Ihrer Anwendung Schicht.

• Ich nicht nutzen Ihre TopID Spalte, so könnte es eine Logik Fehler ist. Aber die beiden test-Fälle gab mir die erwarteten Ergebnisse.

_{Disclaimer: Dieses Beispiel war nur um zu zeigen, dass es kann getan werden in MySQL, nicht, dass ich befürworte es trotzdem. Gespeicherte Prozeduren, die temporäre Tabellen und Cursor sind vielleicht nicht der beste Weg, das zu tun dieses problem.}

Dies ist einer der besten Artikel, die ich gesehen habe für die Erklärung der "Modified Preorder Tree Traversal" - Methode, die Speicherung der Baum-wie Daten aus einer SQL-style database.

http://www.sitepoint.com/hierarchical-data-database/

Die MPTT Material beginnt auf Seite 2.

Im wesentlichen speichern Sie die "Links" und "Rechts" - Wert für jeden Knoten im Baum, in einer solchen Weise, dass, um alle Kinder ParentA bekommen Sie die Links und Rechts für ParentA, dann

SELECT * 
FROM TableName
WHERE Left > ParentLeft 
AND Right < ParentRight