Comment naviguer efficacement sur des données hiérarchiques avec des requêtes récursives dans MySQL?

Parcours de données hiérarchiques MySQL et requête récursive

Lorsque vous traitez des données structurées hiérarchiquement dans une base de données MySQL, parcourir et récupérer efficacement différents niveaux de données peut être un défi. Cet article présentera une approche complète de la création de requêtes récursives dans MySQL, fournissant des solutions pour les versions modernes et héritées.

Requête récursive dans MySQL 8.0

MySQL 8.0 introduit la puissante syntaxe WITH RECURSIVE, vous permettant d'écrire des requêtes récursives concises et efficaces. Par exemple :

<code class="language-sql">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 cte.id = p.parent_id
)
SELECT * FROM cte;</code>

Dans cette requête, une expression de table commune (CTE) nommée cte parcourt récursivement la table products, en commençant au parent_id spécifié (ici 19), et récupère tous ses nœuds enfants.

Requête récursive pour MySQL 5.x

Avant MySQL 8.0, les requêtes récursives directes n'étaient pas prises en charge. Cependant, vous pouvez prendre deux alternatives :

Affectation des variables :

<code class="language-sql">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));</code>

Cette requête complexe utilise l'affectation de variables au sein de la requête elle-même. Il identifie de manière itérative les nœuds enfants en fonction du parent_id fourni (19 dans ce cas). Cependant, cette approche peut souffrir de problèmes de performances sur de grands ensembles de données.

Identificateur de style de chemin :

L'utilisation d'une structure d'ID arborescente avec des identifiants de style chemin simplifie les requêtes récursives. Par exemple :

<code class="language-sql">CREATE TABLE products (id VARCHAR(255) PRIMARY KEY, name VARCHAR(255));
INSERT INTO products (id, name) VALUES ('19', 'category1');
INSERT INTO products (id, name) VALUES ('19/1', 'category2');
INSERT INTO products (id, name) VALUES ('19/1/1', 'category3');
INSERT INTO products (id, name) VALUES ('19/1/1/1', 'category4');</code>

Cette approche permet un parcours hiérarchique efficace à l'aide de caractères génériques :

<code class="language-sql">SELECT * FROM products WHERE id LIKE '19%';</code>

Résumé

La méthode que vous choisissez pour les requêtes récursives dans MySQL dépend de la version spécifique utilisée et de la taille de votre ensemble de données. Pour les versions MySQL modernes, la syntaxe WITH RECURSIVE fournit une solution élégante et efficace. Pour les versions héritées, les alternatives décrites ci-dessus sont disponibles, avec des performances variables.

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!