Créer des requêtes MySQL récursives pour les données hiérarchiques

Question

J'ai une table MySQL comme indiqué ci-dessous :

id	Nom	parent_id
19	Catégorie 1	0
20	Catégorie 2	19
21	Catégorie 3	20
22	Catégorie 4	21
...	...	...

Maintenant, je veux une requête MySQL où je fournis simplement l'identifiant [par exemple id=19] et ensuite je devrais obtenir tous ses sous-identifiants [c'est-à-dire que le résultat devrait avoir l'identifiant '20,21,22']....

La hiérarchie des enfants est inconnue ; elle peut varier...

Je sais comment faire cela en utilisant une boucle for... mais comment puis-je obtenir la même chose en utilisant une seule requête MySQL ?

Answer 1

Pour MySQL 8+ : Utilisez la syntaxe récursive 使用.
Pour MySQL 5.x : Utilisez des variables en ligne, des identifiants de chemin ou des auto-jointures.

MySQL 8+

with recursive cte (id, name, parent_id) as (
  select     id,
             name,
             parent_id
  from       products
  where      parent_id = 19
  union all
  select     p.id,
             p.name,
             p.parent_id
  from       products p
  inner join cte
          on p.parent_id = cte.id
)
select * from cte;

parent_id = 19 中指定的值应设置为您要选择其所有后代的父级的 id.

MySQL 5.x

Pour les versions de MySQL (jusqu'à la version 5.7) qui ne prennent pas en charge les expressions de table courantes, vous pouvez utiliser la requête suivante pour y parvenir :

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

C'est un violon.

Ici, @pv := '19' 中指定的值应设置为您要选择其所有后代的父级的 id.

Cela fonctionnera également si le parent a plusieurs enfants. Mais chaque enregistrement doit remplir la condition parent_id < id, sinon le résultat sera incomplet.

Assignation de variable dans la requête

Cette requête utilise une syntaxe spécifique à MySQL : les variables sont allouées et modifiées lors de l'exécution. J'ai fait quelques hypothèses sur l'ordre d'exécution :

• Évalue d'abord où from 子句。这就是 @pv est initialisé.
• Suite de from 别名检索的顺序对每条记录评估 where 子句。因此，这里设置的条件仅包括父级已被识别为位于后代树中的记录（主要父级的所有后代都将逐步添加到 @pv).
• Cette where 子句中的条件按顺序求值，一旦总结果确定，求值就会中断。因此，第二个条件必须位于第二位，因为它将 id 添加到父列表中，并且只有在 id 通过第一个条件时才会发生这种情况。调用 length 函数只是为了确保此条件始终为真，即使 pv chaîne produit de fausses valeurs pour une raison quelconque.

Dans l’ensemble, on peut trouver ces hypothèses trop risquées pour s’y fier. DocumentationAttention :

Ainsi, même s'il est cohérent avec la requête ci-dessus, l'ordre d'évaluation peut toujours changer, par exemple lorsque vous ajoutez des conditions ou utilisez cette requête comme vue ou sous-requête au sein d'une requête plus large. Il s'agit d'une "fonctionnalité" qui sera supprimée dans une future version de MySQL  :

Comme mentionné ci-dessus, à partir de MySQL 8.0, vous devez utiliser la syntaxe récursive with.

efficacité

Pour les très grands ensembles de données, cette solution peut être lente car l'opération find_in_set n'est pas le moyen le plus idéal pour trouver des nombres dans une liste, et certainement pas lorsqu'il s'agit de faire correspondre le nombre d'enregistrements renvoyés.

Alternative 1 :使用递归，连接

De plus en plus de bases de données implémentent la SQL : 1999 ISO standard WITH [RECURSIVE]递归查询的 la syntaxe (par exemple Postgres 8.4+ , SQL Server 2005+, DB2, Oracle 11gR2+, SQLite 3.8 .4+, Firebird 2.1 +, H2, HyperSQL 2.1.0+, Teradata, MariaDB 10.2.2+). À partir de la version 8.0, MySQL le prend également en charge. Voir le haut de cette réponse pour connaître la syntaxe à utiliser.

Certaines bases de données ont une syntaxe alternative non standard pour les recherches hiérarchiques, telles que Oracle, DB2, Informix, CUBRID et autres bases de données

.

MySQL version 5.7 ne fournit pas une telle fonctionnalité. Lorsque votre moteur de base de données fournit cette syntaxe ou que vous pouvez migrer vers un moteur de base de données fournissant cette syntaxe, c'est sans aucun doute le meilleur choix. Sinon, envisagez les alternatives suivantes.

Alternative 2 : Identificateur de style de chemin

Les choses deviennent beaucoup plus faciles si vous attribuez des valeurs d'identification contenant des informations hiérarchiques (chemin). Par exemple, dans votre cas, cela pourrait ressembler à ceci :

ID	Nom
19	Catégorie 1
19/1	Catégorie 2
19/1	Catégorie 3
19/1/1/1	Catégorie 4

Alors votre 选择 ressemblera à ceci :

select  id,
        name 
from    products
where   id like '19/%'

Alternative 3 : Répéter l'auto-adhésion

Si vous connaissez la limite supérieure de la profondeur de l'arborescence hiérarchique, vous pouvez utiliser la requête sql standard comme celle-ci :

select      p6.parent_id as parent6_id,
            p5.parent_id as parent5_id,
            p4.parent_id as parent4_id,
            p3.parent_id as parent3_id,
            p2.parent_id as parent2_id,
            p1.parent_id as parent_id,
            p1.id as product_id,
            p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id 
left join   products p3 on p3.id = p2.parent_id 
left join   products p4 on p4.id = p3.parent_id  
left join   products p5 on p5.id = p4.parent_id  
left join   products p6 on p6.id = p5.parent_id
where       19 in (p1.parent_id, 
                   p2.parent_id, 
                   p3.parent_id, 
                   p4.parent_id, 
                   p5.parent_id, 
                   p6.parent_id) 
order       by 1, 2, 3, 4, 5, 6, 7;

Voir ceci Violon

where La condition précise les descendants des parents que vous souhaitez récupérer. Vous pouvez étendre cette requête à plusieurs niveaux selon vos besoins.