¿Cómo crear una consulta recursiva jerárquica MySQL?

Tengo una tabla MySQL que es la siguiente:

identificación	nombre	Identificación de los padres
19	Categoría 1	0
20	categoría2	19
21	categoría3	20
22	categoría4	21
...	...	...

Ahora, quiero tener una única consulta MySQL a la que simplemente le proporciono la identificación [por ejemplo, digamos id=19] y luego debería obtener todas sus identificaciones secundarias [es decir, el resultado debe tener las identificaciones '20,21,22']....

Se desconoce la jerarquía de los niños; puede variar....

Sé cómo hacerlo usando un forbucle... pero ¿cómo lograr lo mismo usando una única consulta MySQL?

Nov 26 '13 18:11 Tarun Parswani

Para MySQL 8+: use la sintaxis recursiva with.
Para MySQL 5.x: use variables en línea, ID de ruta o autouniones.

mysql 8+

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 p.parent_id = cte.id
)
select * from cte;

El valor especificado en parent_id = 19debe establecerse en el iddel padre del que desea seleccionar todos los descendientes.

MySQL 5.x

Para las versiones de MySQL que no admiten expresiones de tabla comunes (hasta la versión 5.7), esto se puede lograr con la siguiente consulta:

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))

Aquí hay un violín .

Aquí, el valor especificado en @pv := '19'debe establecerse en el iddel padre del que desea seleccionar todos los descendientes.

Esto también funcionará si un padre tiene varios hijos. Sin embargo, se requiere que cada registro cumpla la condición parent_id < id, de lo contrario los resultados no estarán completos.

Asignaciones de variables dentro de una consulta

Esta consulta utiliza una sintaxis específica de MySQL: las variables se asignan y modifican durante su ejecución. Se hacen algunas suposiciones sobre el orden de ejecución:

La fromcláusula se evalúa primero. Ahí es donde @pvse inicializa.
La wherecláusula se evalúa para cada registro en el orden de recuperación de los fromalias. Entonces, aquí es donde se establece una condición para incluir solo registros para los cuales el padre ya estaba identificado como en el árbol de descendientes (todos los descendientes del padre principal se agregan progresivamente @pv).
Las condiciones de esta wherecláusula se evalúan en orden y la evaluación se interrumpe una vez que el resultado total es seguro. Por lo tanto, la segunda condición debe estar en segundo lugar, ya que agrega ida la lista principal, y esto solo debería suceder si idpasa la primera condición. La lengthfunción solo se llama para garantizar que esta condición sea siempre verdadera, incluso si la pvcadena por alguna razón arrojara un valor falso.

En definitiva, uno puede considerar que estas suposiciones son demasiado arriesgadas como para confiar en ellas. La documentación advierte:

es posible que obtenga los resultados que espera, pero esto no está garantizado [...] el orden de evaluación de las expresiones que involucran variables de usuario no está definido.

Entonces, aunque funciona de manera consistente con la consulta anterior, el orden de evaluación aún puede cambiar, por ejemplo, cuando agrega condiciones o usa esta consulta como vista o subconsulta en una consulta más grande. Es una "característica" que se eliminará en una futura versión de MySQL :

Las versiones anteriores de MySQL permitieron asignar un valor a una variable de usuario en declaraciones distintas a SET. Esta funcionalidad es compatible con MySQL 8.0 por compatibilidad con versiones anteriores, pero está sujeta a eliminación en una versión futura de MySQL.

Como se indicó anteriormente, desde MySQL 8.0 en adelante debes usar la withsintaxis recursiva.

Eficiencia

Para conjuntos de datos muy grandes, esta solución puede resultar lenta, ya que la find_in_setoperación no es la forma más ideal de encontrar un número en una lista, y menos aún en una lista que alcanza un tamaño del mismo orden de magnitud que el número de registros devueltos.

Alternativa 1: `with recursive`,`connect by`

Cada vez más bases de datos implementan la sintaxis estándar ISO SQL:1999WITH [RECURSIVE] para consultas recursivas (por ejemplo, Postgres 8.4+ , SQL Server 2005+ , DB2 , Oracle 11gR2+ , SQLite 3.8.4+ , Firebird 2.1+ , H2 , HyperSQL 2.1.0+ , Teradata , MaríaDB 10.2.2+ ). Y a partir de la versión 8.0, MySQL también lo admite . Consulte la parte superior de esta respuesta para conocer la sintaxis a utilizar.

Algunas bases de datos tienen una sintaxis alternativa no estándar para búsquedas jerárquicas, como la CONNECT BYcláusula disponible en Oracle , DB2 , Informix , CUBRID y otras bases de datos.

La versión 5.7 de MySQL no ofrece dicha característica. Cuando su motor de base de datos proporciona esta sintaxis o puede migrar a uno que la proporcione, entonces esa es sin duda la mejor opción. Si no es así, considere también las siguientes alternativas.

Alternativa 2: Identificadores de estilo de ruta

Las cosas se vuelven mucho más fáciles si asigna idvalores que contengan la información jerárquica: una ruta. Por ejemplo, en su caso esto podría verse así:

IDENTIFICACIÓN	NOMBRE
19	Categoría 1
19/1	categoría2
19/1/1	categoría3
19/1/1/1	categoría4

Entonces tu selectlucirías así:

select  id,
        name 
from    products
where   id like '19/%'

Alternativa 3: Autouniones repetidas

Si conoce un límite superior de cuán profundo puede llegar a ser su árbol jerárquico, puede utilizar una sqlconsulta estándar como esta:

select      p6.parent_id as parent6_id,
            p5.parent_id as parent5_id,
            p4.parent_id as parent4_id,
            p3.parent_id as parent3_id,
            p2.parent_id as parent2_id,
            p1.parent_id as parent_id,
            p1.id as product_id,
            p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id 
left join   products p3 on p3.id = p2.parent_id 
left join   products p4 on p4.id = p3.parent_id  
left join   products p5 on p5.id = p4.parent_id  
left join   products p6 on p6.id = p5.parent_id
where       19 in (p1.parent_id, 
                   p2.parent_id, 
                   p3.parent_id, 
                   p4.parent_id, 
                   p5.parent_id, 
                   p6.parent_id) 
order       by 1, 2, 3, 4, 5, 6, 7;

Mira este violín

La wherecondición especifica de qué padre desea recuperar los descendientes. Puede ampliar esta consulta con más niveles según sea necesario.

Nov 16 '2015 14:11 trincot

Del blog Gestión de datos jerárquicos en MySQL

Estructura de la mesa

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

Consulta:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

Producción

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+

La mayoría de los usuarios en un momento u otro han tratado con datos jerárquicos en una base de datos SQL y sin duda han aprendido que la gestión de datos jerárquicos no es para lo que está diseñada una base de datos relacional. Las tablas de una base de datos relacional no son jerárquicas (como XML), sino que son simplemente una lista plana. Los datos jerárquicos tienen una relación padre-hijo que no se representa de forma natural en una tabla de base de datos relacional. Leer más

Consulte el blog para obtener más detalles.

EDITAR:

select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv

Producción:

category_id name    parent
19  category1   0
20  category2   19
21  category3   20
22  category4   21

Referencia: ¿Cómo hacer la consulta SELECT recursiva en Mysql?

Nov 26 '2013 11:11 Damodaran

Pruebe estos:

Definición de tabla:

DROP TABLE IF EXISTS category;
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    parent_id INT,
    CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
    REFERENCES category (id)
) engine=innodb;

Filas experimentales:

INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);

Procedimiento almacenado recursivo:

DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
    DECLARE catname VARCHAR(20);
    DECLARE temppath TEXT;
    DECLARE tempparent INT;
    SET max_sp_recursion_depth = 255;
    SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
    IF tempparent IS NULL
    THEN
        SET path = catname;
    ELSE
        CALL getpath(tempparent, temppath);
        SET path = CONCAT(temppath, '/', catname);
    END IF;
END$$
DELIMITER ;

Función contenedora para el procedimiento almacenado:

DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE res TEXT;
    CALL getpath(cat_id, res);
    RETURN res;
END$$
DELIMITER ;

Seleccione ejemplo:

SELECT id, name, getpath(id) AS path FROM category;

Producción:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1                               |
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA                     |
| 24 | categoryB | category1/categoryA/categoryB           |
| 25 | categoryC | category1/categoryA/categoryC           |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+

Filtrar filas con cierta ruta:

SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';

Producción:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+

Mar 11 '2017 10:03 Fandi Susanto

Si necesita una velocidad de lectura rápida, la mejor opción es utilizar una tabla de cierre. Una tabla de cierre contiene una fila para cada par de antepasados/descendientes. Entonces, en su ejemplo, la tabla de cierre se vería así

ancestor | descendant | depth
0        | 0          | 0
0        | 19         | 1
0        | 20         | 2
0        | 21         | 3
0        | 22         | 4
19       | 19         | 0
19       | 20         | 1
19       | 21         | 3
19       | 22         | 4
20       | 20         | 0
20       | 21         | 1
20       | 22         | 2
21       | 21         | 0
21       | 22         | 1
22       | 22         | 0

Una vez que tenga esta tabla, las consultas jerárquicas se vuelven muy fáciles y rápidas. Para conseguir todos los descendientes de la categoría 20:

SELECT cat.* FROM categories_closure AS cl
INNER JOIN categories AS cat ON cat.id = cl.descendant
WHERE cl.ancestor = 20 AND cl.depth > 0

Por supuesto, existe una gran desventaja cuando se utilizan datos desnormalizados como este. Debe mantener la tabla de cierre junto con la tabla de categorías. Probablemente la mejor manera sea utilizar activadores, pero es algo complejo realizar un seguimiento correcto de las inserciones/actualizaciones/eliminaciones de las tablas de cierre. Como ocurre con todo, debe analizar sus necesidades y decidir qué enfoque es mejor para usted.

Editar : consulte la pregunta ¿Cuáles son las opciones para almacenar datos jerárquicos en una base de datos relacional? para más opciones. Existen diferentes soluciones óptimas para diferentes situaciones.

Jan 13 '2016 17:01 Justin Howard

Basado en la respuesta de @trincot, muy bien explicada, uso WITH RECURSIVE ()una declaración para crear una ruta de navegación usando idla página actual y retrocedo en la jerarquía para encontrar cada parenten mi routetabla.

Entonces, la solución @trincot se adapta aquí en la dirección opuesta para encontrar padres en lugar de descendientes.

También agregué depthvalor que es útil para invertir el orden de los resultados (de lo contrario, la ruta de navegación estaría al revés).

WITH RECURSIVE cte (
    `id`,
    `title`,
    `url`,
    `icon`,
    `class`,
    `parent_id`,
    `depth`
) AS (
    SELECT   
        `id`,
        `title`,
        `url`,
        `icon`,
        `class`,
        `parent_id`,
        1 AS `depth` 
    FROM     `route`
    WHERE    `id` = :id
      
    UNION ALL 
    SELECT 
        P.`id`,
        P.`title`,
        P.`url`,
        P.`icon`,
        P.`class`,
        P.`parent_id`,
        `depth` + 1
    FROM `route` P
        
    INNER JOIN cte
        ON P.`id` = cte.`parent_id`
)
SELECT * FROM cte ORDER BY `depth` DESC;

Antes de actualizar a mySQL 8+, estaba usando vars pero está en desuso y ya no funciona en mi versión 8.0.22 .

EDITAR 2021-02-19 : Ejemplo de menú jerárquico

Después del comentario de @david, decidí intentar hacer un menú jerárquico completo con todos los nodos y ordenados como quiero (con sortingcolumnas que clasifican los elementos en cada profundidad). Muy útil para mi página de matriz de usuario/autorización.

Esto realmente simplifica mi versión anterior con una consulta en cada profundidad (bucles PHP) .

Matriz de autorización ERP

Este ejemplo integra un INNER JOIN con urluna tabla para filtrar la ruta por sitio web (sistema CMS de múltiples sitios web).

Puede ver la pathcolumna esencial que contiene CONCAT()la función para ordenar el menú de la manera correcta.

SELECT R.* FROM (
    WITH RECURSIVE cte (
        `id`,
        `title`,
        `url`,
        `icon`,
        `class`,
        `parent`,
        `depth`,
        `sorting`,
        `path`
    ) AS (
        SELECT 
            `id`,
            `title`,
            `url`,
            `icon`,
            `class`,
            `parent`,
            1 AS `depth`,
            `sorting`,
            CONCAT(`sorting`, ' ' , `title`) AS `path`
        FROM `route`
        WHERE `parent` = 0
        UNION ALL SELECT 
            D.`id`,
            D.`title`,
            D.`url`,
            D.`icon`,
            D.`class`,
            D.`parent`,
            `depth` + 1,
            D.`sorting`,
            CONCAT(cte.`path`, ' > ', D.`sorting`, ' ' , D.`title`)
        FROM `route` D
        INNER JOIN cte
            ON cte.`id` = D.`parent`
    )
    SELECT * FROM cte
) R

INNER JOIN `url` U
    ON R.`id` = U.`route_id`
    AND U.`site_id` = 1

ORDER BY `path` ASC

Jan 07 '2021 13:01 Meloman

¿Cómo crear una consulta recursiva jerárquica MySQL?

mysql 8+

MySQL 5.x

Asignaciones de variables dentro de una consulta

Eficiencia

Alternativa 1: with recursive,connect by

Alternativa 2: Identificadores de estilo de ruta

Alternativa 3: Autouniones repetidas

Alternativa 1: `with recursive`,`connect by`