Curso de MariaDB / Consultar jerarquías y evitar ciclos con CTE recursivas
Una expresión de tabla común recursiva, o CTE recursiva, parte de un conjunto inicial y sigue relaciones repetidamente para obtener nuevas filas. Una consulta de jerarquía necesita un punto de inicio, una regla de parada, columnas de ruta con suficiente capacidad, tratamiento de ciclos y una ordenación final explícita.
Reconocer cuándo conviene la recursión
Una CTE ordinaria da nombre a una etapa de consulta. Una CTE recursiva utiliza también ese nombre dentro de su definición: la parte inicial, o ancla, produce las primeras filas y la parte recursiva busca las siguientes a partir de la etapa anterior. Este ejemplo reúne ambas con UNION ALL.
Los árboles de categorías y los organigramas pueden tener profundidad variable. Para una relación fija de dos niveles, quizá baste un JOIN. En una jerarquía grande también necesita localizar hijos por su clave de padre y definir un límite de profundidad; la guía de índices ayuda a revisar ese acceso. La referencia oficial de CTE recursivas explica el cálculo y los tipos definidos por el ancla.
Preparar un árbol de seis categorías
Ejecute la preparación en appops_lab con una conexión administrativa o una cuenta de prácticas con permisos para crear la base y sus tablas. Compruebe antes SELECT VERSION(), DATABASE(); y las bases y tablas existentes. Si appops_lab ya se usa para las prácticas Docker, conserve su contenido; CREATE DATABASE IF NOT EXISTS no sustituye esa revisión. Si category_tree existe, inspeccione la práctica anterior antes de repetir su creación.
La clave foránea comprueba que exista el padre indicado; no demuestra que toda la jerarquía esté libre de ciclos.
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');
Los nombres ingleses son literales del juego de datos: Catalog es el catálogo raíz; contiene Books (libros) y Tools (herramientas). Books contiene SQL y Python; Tools contiene Editors (editores). El índice parent_id ayuda a localizar hijos. Seis filas permiten estudiar las relaciones y el resultado, sin afirmar mejoras generales de velocidad.
Ejecutar la consulta completa con profundidad y rutas
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;
El primer SELECT devuelve la categoría 1 en profundidad cero. El SELECT recursivo encuentra las categorías cuyo parent_id coincide con la categoría actual e incrementa la profundidad. La ruta visited rodea los ID numéricos con barras, para que buscar el ID 1 no lo confunda con el 11.
El ancla convierte ambas rutas a CHAR(1000). Si una cadena inicial corta determina un tipo demasiado estrecho, las concatenaciones posteriores pueden exceder su capacidad. Aquí se utilizan ID enteros, nombres cortos y una profundidad máxima de ocho. Recalcule la capacidad y los límites para sus rutas; 1000 no es un tamaño seguro universal.
| ID | Padre | Profundidad | Ruta |
|---|---|---|---|
| 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 |
El ORDER BY final por profundidad e ID garantiza esta presentación. No deduzca el orden de salida a partir de la evaluación recursiva. Ordenar una ruta de texto produce un orden lexicográfico que puede diferir del orden numérico de los ID.
Cambiar una condición y observar el alcance
Cambie únicamente t.depth < 8 por t.depth < 1 y vuelva a consultar. Obtiene el ancla de profundidad cero y sus hijos inmediatos: ID 1, 2 y 3. Restaure después el límite a ocho. La condición compara la profundidad del padre, por lo que el padre de profundidad cero todavía puede generar hijos de profundidad uno.
Otra comparación independiente consiste en cambiar solo el ancla a category_id = 2. Devuelve el subárbol Books: ID 2, 4 y 5, con Books ahora en profundidad cero. Restaure el ancla a 1 antes de la prueba de ciclo siguiente.
Un resultado cortado por el límite no equivale necesariamente a la jerarquía completa. Determine primero si existen niveles más profundos. La variable max_recursive_iterations del servidor impone otro límite a la repetición, pero no sustituye una regla de parada de negocio ni valida las relaciones entre padres.
Introducir un ciclo y revertirlo
En la misma conexión, ejecute estas sentencias y vuelva a ejecutar la consulta recursiva completa anterior. Otra conexión podría no ver el cambio de padre sin confirmar.
START TRANSACTION;
UPDATE category_tree SET parent_id = 4 WHERE category_id = 1;
Asignar el padre 4 a la categoría 1 crea el ciclo 1 → 2 → 4 → 1 al seguir los hijos. Como la consulta empieza expresamente en la categoría 1, el control de ID visitados excluye el regreso a una fila ya visitada y termina igualmente con seis filas. La fila raíz muestra temporalmente el padre 4. Restaure los datos inmediatamente después de observarlo:
ROLLBACK;
SELECT category_id, parent_id FROM category_tree WHERE category_id = 1;
El padre de la categoría 1 debe volver a NULL. Un ancla basada en parent_id IS NULL podría omitir todo ese componente cíclico al desaparecer su raíz sin padre. Evitar revisitas durante un recorrido no demuestra que la base completa carezca de ciclos ni que no haya componentes inaccesibles. MariaDB también dispone de la sintaxis CYCLE ... RESTRICT; aquí la ruta explícita permite observar la regla de recorrido.
Comprobar la jerarquía antes de utilizarla en una aplicación
- Confirme que el ancla selecciona las raíces previstas.
- Distinga padres ausentes, referencias a uno mismo, ciclos y componentes inaccesibles.
- Elija una profundidad máxima y una capacidad de ruta compatible.
- Revise el índice de búsqueda de hijos y EXPLAIN y concilie el recuento con las relaciones esperadas.
Para limpiar, termine cualquier transacción abierta, confirme que esta tabla es prescindible y ejecute DROP TABLE appops_lab.category_tree;. La operación elimina estructura y registros y no se revierte con un ROLLBACK ordinario. No sustituya en ese comando el nombre de una tabla de negocio ni elimine la base compartida con otras prácticas.





