Consultas recursivas en MySQL 8 con CTE
Recorrer árboles de categorías y jerarquías completas sin procedimientos almacenados.
Contenido
Antes de MySQL 8, recorrer un árbol de profundidad desconocida implicaba procedimientos almacenados o N consultas desde la aplicación. Con WITH RECURSIVE basta una sola consulta.
La tabla
consultasql
CREATE TABLE categories (
id INT PRIMARY KEY AUTO_INCREMENT,
parent_id INT NULL,
name VARCHAR(100) NOT NULL,
FOREIGN KEY (parent_id) REFERENCES categories(id)
);Todos los descendientes
consultasql
WITH RECURSIVE tree AS (
SELECT id, parent_id, name, 0 AS lvl,
CAST(name AS CHAR(500)) AS path
FROM categories
WHERE id = 1 -- nodo raíz
UNION ALL
SELECT c.id, c.parent_id, c.name, t.lvl + 1,
CONCAT(t.path, ' › ', c.name)
FROM categories c
JOIN tree t ON c.parent_id = t.id
)
SELECT CONCAT(REPEAT(' ', lvl), name) AS categoria, path
FROM tree
ORDER BY path;Ojo: el CAST en la parte ancla define el tamaño de la columna path; sin él, MySQL la trunca al largo del primer valor.
El camino hasta la raíz (breadcrumbs)
consultasql
WITH RECURSIVE up AS (
SELECT id, parent_id, name FROM categories WHERE id = 42
UNION ALL
SELECT c.id, c.parent_id, c.name
FROM categories c JOIN up ON c.id = up.parent_id
)
SELECT name FROM up;Límites y rendimiento
| Variable | Valor por defecto | Para qué sirve |
|---|---|---|
cte_max_recursion_depth | 1000 | Corta ciclos accidentales en los datos. |
Índice en parent_id | — | Imprescindible: cada iteración hace un JOIN por esa columna. |
Si el árbol cambia poco y se lee mucho, considera además guardar el
pathmaterializado; la CTE sigue siendo útil para reconstruirlo.