Consultas recursivas en MySQL 8 con CTE

Recorrer árboles de categorías y jerarquías completas sin procedimientos almacenados.

Contenido
  1. La tabla
  2. Todos los descendientes
  3. El camino hasta la raíz (breadcrumbs)
  4. Límites y rendimiento
  5. Repositorio y recursos

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

VariableValor por defectoPara qué sirve
cte_max_recursion_depth1000Corta 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 path materializado; la CTE sigue siendo útil para reconstruirlo.

Repositorio y recursos