Cours MariaDB / Parcourir une hiérarchie avec une CTE récursive et éviter les cycles
Une expression de table commune récursive, ou CTE récursive, part d’un ensemble initial de lignes et suit leurs relations pour produire les niveaux suivants. Pour interroger une hiérarchie de façon fiable, il faut définir le point de départ, la condition d’arrêt, une largeur suffisante pour les chemins, le traitement des cycles et un tri final explicite.
Savoir quand utiliser une CTE récursive
Une CTE ordinaire définie avec WITH donne un nom à une étape de requête, pendant la durée de cette instruction. Une CTE récursive utilise aussi ce nom dans sa propre définition : la partie initiale, appelée ancre, fournit les premières lignes ; la partie récursive recherche les suivantes à partir du résultat précédent. L’exemple ci-dessous réunit ces deux parties avec UNION ALL.
Un arbre de catégories ou un organigramme peut comporter un nombre variable de niveaux. La récursion permet de parcourir cette profondeur sans écrire une jointure différente pour chaque niveau. Pour une relation limitée à deux niveaux connus, une jointure peut rester plus simple. Dans une grande hiérarchie, prévoyez une limite de profondeur et un index adapté à la recherche des enfants par leur parent ; le guide des index simples et composites explique comment choisir les colonnes selon la requête.
La présentation officielle des CTE récursives MariaDB détaille le calcul itératif et le rôle du type des colonnes de l’ancre.
Préparer un arbre de six catégories
Dans le client MariaDB, exécutez la préparation avec un administrateur ou un compte d’apprentissage autorisé à créer un schéma. Utilisez uniquement la base dédiée appops_lab. Si elle existe déjà, confirmez qu’il s’agit bien de votre environnement d’exercice ; si la table category_tree existe, examinez l’exercice précédent avant de relancer sa création.
CREATE DATABASE IF NOT EXISTS appops_lab
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE category_tree (
category_id INT NOT NULL PRIMARY KEY,
parent_id INT NULL,
category_name VARCHAR(40) NOT NULL,
INDEX ix_parent (parent_id),
CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
REFERENCES category_tree(category_id)
) ENGINE=InnoDB;
INSERT INTO category_tree VALUES
(1, NULL, 'Catalog'), (2, 1, 'Books'), (3, 1, 'Tools'),
(4, 2, 'SQL'), (5, 2, 'Python'), (6, 3, 'Editors');
Les valeurs enregistrées restent celles du jeu SQL : Catalog contient Books et Tools, soit un catalogue avec des livres et des outils. Books contient SQL et Python ; Tools contient Editors, les éditeurs. Les identifiants du schéma ci-dessous sont les mêmes que ceux des requêtes et résultats.
L’index ix_parent facilite la recherche des enfants par parent_id. Avec six lignes, l’exercice démontre surtout les relations et l’exactitude du parcours ; il ne mesure pas un gain général de performance. La clé étrangère vérifie l’existence du parent référencé, mais ne garantit pas l’absence de cycles dans toute la hiérarchie.
Exécuter une requête complète avec profondeur et chemins
Restez dans appops_lab, puis exécutez cette instruction complète :
WITH RECURSIVE tree AS (
SELECT category_id, parent_id, category_name, 0 AS depth,
CAST(CONCAT('/', category_id, '/') AS CHAR(1000)) AS visited,
CAST(category_name AS CHAR(1000)) AS name_path
FROM category_tree WHERE category_id = 1
UNION ALL
SELECT c.category_id, c.parent_id, c.category_name, t.depth + 1,
CONCAT(t.visited, c.category_id, '/'),
CONCAT(t.name_path, ' > ', c.category_name)
FROM category_tree c JOIN tree t ON c.parent_id = t.category_id
WHERE t.depth < 8
AND LOCATE(CONCAT('/', c.category_id, '/'), t.visited) = 0
)
SELECT category_id, parent_id, depth, name_path
FROM tree ORDER BY depth, category_id;
Le premier SELECT sélectionne la catégorie 1 à la profondeur zéro. Le SELECT récursif rejoint les lignes dont parent_id correspond à la catégorie courante et augmente la profondeur d’une unité. La colonne visited conserve les identifiants déjà traversés. Les barres obliques encadrent chaque nombre : rechercher /1/ ne confond ainsi pas l’identifiant 1 avec 11.
Dans l’ancre, les deux chemins sont explicitement convertis en CHAR(1000). Sans largeur suffisante dès cette première partie, les chaînes concaténées aux étapes suivantes peuvent dépasser la largeur déduite du type initial. Ici, les identifiants sont entiers, les noms courts et la profondeur limitée à huit. Recalculez la largeur et la borne pour des données réelles ; 1000 n’est pas une valeur universellement suffisante.
| ID | Parent | Profondeur | Chemin |
|---|---|---|---|
| 1 | NULL | 0 | Catalog |
| 2 | 1 | 1 | Catalog > Books |
| 3 | 1 | 1 | Catalog > Tools |
| 4 | 2 | 2 | Catalog > Books > SQL |
| 5 | 2 | 2 | Catalog > Books > Python |
| 6 | 3 | 2 | Catalog > Tools > Editors |
Seul le ORDER BY depth, category_id final garantit cet ordre d’affichage. Ne déduisez pas un ordre de présentation de l’évaluation récursive. Trier un chemin textuel donne par ailleurs un ordre lexical qui peut différer de l’ordre numérique des identifiants.
Modifier une seule condition et observer le périmètre
Remplacez uniquement t.depth < 8 par t.depth < 1, puis relancez la requête. Vous devez obtenir les identifiants 1, 2 et 3 : l’ancre de profondeur zéro et ses enfants directs. La condition porte sur la profondeur du parent déjà parcouru ; quand celle-ci vaut zéro, le niveau un est encore produit. Rétablissez ensuite la borne huit.
Pour une seconde comparaison, conservez cette borne et remplacez uniquement l’ancre category_id = 1 par category_id = 2. Le sous-arbre de Books contient les identifiants 2, 4 et 5, avec Books à la profondeur zéro. Rétablissez l’ancre 1 avant l’expérience de cycle.
Une hiérarchie coupée par la borne de profondeur n’est pas nécessairement complète. Vérifiez la profondeur réelle attendue avant d’augmenter la limite. La variable serveur max_recursive_iterations impose une autre limite au calcul répété ; elle ne remplace ni une condition d’arrêt métier ni la validation des relations parent-enfant.
Introduire un cycle puis annuler la modification
Dans la même connexion, exécutez ces deux instructions, puis relancez la requête récursive complète avec l’ancre 1 et la borne huit. Une autre connexion peut ne pas voir cette modification du parent, encore non validée.
START TRANSACTION;
UPDATE category_tree SET parent_id = 4 WHERE category_id = 1;
Attribuer le parent 4 à la catégorie 1 crée le cycle 1 → 2 → 4 → 1 lorsqu’on suit les enfants. La requête démarre explicitement à l’identifiant 1 ; son test sur les identifiants déjà visités empêche de revenir à cette ligne et le résultat contient toujours six lignes. Le parent de la ligne 1 apparaît toutefois temporairement comme 4. Restaurez les données initiales immédiatement après l’observation :
ROLLBACK;
SELECT category_id, parent_id FROM category_tree WHERE category_id = 1;
Le parent de la catégorie 1 doit être à nouveau NULL. Si une instruction de l’expérience échoue, annulez également la transaction dans cette connexion avant de poursuivre. Ne remplacez pas cette annulation par un COMMIT, qui conserverait la relation cyclique.
Une requête ancrée sur parent_id IS NULL pourrait manquer toute une composante cyclique dont la racine a disparu. Éviter les revisites dans un parcours donné ne prouve donc pas que l’ensemble de la base est sans cycles. MariaDB propose aussi la syntaxe CYCLE ... RESTRICT ; ici, le chemin explicite rend visible la règle qui empêche une revisite.
Vérifier la hiérarchie avant une utilisation réelle
- Confirmez que l’ancre sélectionne exactement la ou les racines voulues.
- Distinguez les parents manquants, les catégories se prenant pour parent, les cycles et les composantes inaccessibles depuis l’ancre.
- Définissez une profondeur maximale et calculez une largeur de chemin compatible avec les identifiants et les noms.
- Examinez l’index de recherche des enfants et le plan
EXPLAIN, puis confrontez le nombre et le contenu des lignes aux relations attendues.
Pour nettoyer l’exercice, confirmez que cette table précise peut être supprimée avant d’exécuter DROP TABLE appops_lab.category_tree;. Cette modification du schéma n’est pas annulée par un ROLLBACK ordinaire. Ne remplacez pas le nom de la table d’exercice par celui d’une table métier et ne supprimez pas toute la base si elle contient d’autres travaux.





