Comment interroger de manière récursive une structure arborescente dans MySQL pour rechercher les descendants d'un nœud parent ?

Récursion d'une structure arborescente dans MySQL

La gestion des données hiérarchiques peut être un défi dans les bases de données relationnelles, en particulier lorsque des requêtes récursives sont requises. Dans MySQL, nous avons un problème spécifique : comment récupérer efficacement tous les descendants d'un emplacement parent donné, quelle que soit la profondeur de la hiérarchie.

Le problème :

Considérez le schéma de base de données suivant pour les emplacements :

location (id, ....)
location_parent (location_id, parent_id)

La table location stocke les informations d'emplacement, tandis que la table location_parent définit la relation parent-enfant entre les emplacements.

Supposons que nous ayons un emplacement avec ID 5. Nous souhaitons récupérer tous ses descendants, y compris ceux qui sont profonds sur plusieurs niveaux.

Solution initiale :

Une approche des requêtes récursives consiste à utiliser une auto- join comme suit :

<code class="sql">SELECT DISTINCT l.id
FROM location AS l
LEFT JOIN location_parent AS lp ON l.id = lp.child_id
WHERE lp.parent_id = 5
;</code>

Cette requête récupérera les enfants directs de l'emplacement 5. Cependant, pour les descendants plus profonds, nous devons répéter la requête plusieurs fois, en utilisant à chaque fois les résultats de la requête précédente comme entrée pour le suivant. Cette méthode est inefficace et peu pratique pour les grandes structures hiérarchiques.

Solution recommandée :

La solution recommandée à ce problème consiste à utiliser l'expression de table commune (CTE) récursive. Un CTE est une table temporaire qui peut être définie et référencée au sein d'une seule requête.

<code class="sql">WITH RECURSIVE descendants AS (
    SELECT id, parent_id
    FROM location_parent
    WHERE parent_id = 5
    UNION ALL
    SELECT lp.child_id, lp.parent_id
    FROM descendants AS d
    JOIN location_parent AS lp ON d.id = lp.parent_id
)
SELECT id
FROM descendants
;</code>

Cette requête crée un CTE récursif appelé descendants, qui commence par les enfants directs de l'emplacement 5. La clause UNION ALL puis ajoute les enfants des descendants précédemment sélectionnés, élargissant ainsi la requête pour inclure tous les descendants à n'importe quelle profondeur.

La deuxième partie de la requête sélectionne la colonne id du CTE des descendants, ce qui nous donne la liste de tous les descendants emplacements. Cette approche est beaucoup plus efficace que la méthode d'auto-jointure et peut gérer des structures hiérarchiques de n'importe quelle profondeur.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!