mysql - recursivas - tabla recursiva en base de datos
Cómo crear una consulta recursiva jerárquica de MySQL (13)
Algo que no se menciona aquí, aunque un poco similar a la segunda alternativa de la respuesta aceptada pero diferente y de bajo costo para la consulta de gran jerarquía y los elementos fáciles (insertar eliminación de actualizaciones), agregaría una columna de ruta persistente para cada elemento.
algo como:
id | name | path
19 | category1 | /19
20 | category2 | /19/20
21 | category3 | /19/20/21
22 | category4 | /19/20/21/22
Ejemplo:
-- get children of category3:
SELECT * FROM my_table WHERE path LIKE ''/19/20/21%''
-- Reparent an item:
UPDATE my_table SET path = REPLACE(path, ''/19/20'', ''/15/16'') WHERE path LIKE ''/19/20/%''
Optimice la longitud de la ruta y la ruta ORDER BY path utilizando la codificación base36 en lugar de la ID de ruta numérica real
// base10 => base36
''1'' => ''1'',
''10'' => ''A'',
''100'' => ''2S'',
''1000'' => ''RS'',
''10000'' => ''7PS'',
''100000'' => ''255S'',
''1000000'' => ''LFLS'',
''1000000000'' => ''GJDGXS'',
''1000000000000'' => ''CRE66I9S''
https://en.wikipedia.org/wiki/Base36
Suprimir también el separador barra inclinada ''/'' mediante el uso de longitud fija y relleno a la identificación codificada
Explicación detallada de la optimización aquí: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/
QUE HACER
construir una función o procedimiento para dividir el camino para antepasados retirados de un elemento
Tengo una tabla MySQL que es la siguiente:
id | name | parent_id
19 | category1 | 0
20 | category2 | 19
21 | category3 | 20
22 | category4 | 21
......
Ahora, quiero tener una única consulta de MySQL a la que simplemente proporcione el id [por ejemplo, decir ''id = 19''], entonces debería obtener todos sus identificadores secundarios [es decir, el resultado debe tener los ids ''20, 21,22 '']. ... Además, se desconoce la jerarquía de los niños, puede variar ...
Además, ya tengo la solución usando el ciclo for ... Déjame saber cómo lograr lo mismo usando una sola consulta MySQL si es posible.
Consulta simple para listar el hijo de la primera recursión:
select @pv:=id as id, name, parent_id
from products
join (select @pv:=19)tmp
where parent_id=@pv
Resultado:
id name parent_id
20 category2 19
21 category3 20
22 category4 21
26 category24 22
... con left join:
select
@pv:=p1.id as id
, p2.name as parent_name
, p1.name name
, p1.parent_id
from products p1
join (select @pv:=19)tmp
left join products p2 on p2.id=p1.parent_id -- optional join to get parent name
where p1.parent_id=@pv
La solución de @tincot para listar a todos los niños:
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) > 0
and @pv := concat(@pv, '','', id)
Pruébelo en línea con Sql Fiddle y vea todos los resultados.
Desde el blog Gestión de datos jerárquicos en MySQL
Estructura de la tabla
+-------------+----------------------+--------+
| 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'';
Salida
+-------------+----------------------+--------------+-------+
| 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 aprendieron que la gestión de datos jerárquicos no es lo que se pretende con 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 está naturalmente representada en una tabla de base de datos relacional. Lee mas
Consulte el blog para más detalles.
EDITAR:
select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv
Salida:
category_id name parent
19 category1 0
20 category2 19
21 category3 20
22 category4 21
Referencia: ¿Cómo hacer la consulta SELECCIONAR recursiva en Mysql?
El mejor enfoque con el que he surgido es
- Use lineage para almacenar / sort / trace trees. Eso es más que suficiente, y funciona miles de veces más rápido para leer que cualquier otro enfoque. También permite mantenerse en ese patrón incluso si DB cambiará (dado que CUALQUIER DB permitirá que se use ese patrón)
- Use la función que determina el linaje para una ID específica.
- Úselo como lo desee (en selecciones, o en operaciones CUD, o incluso por trabajos).
Lineage approach descr. se puede encontrar en cualquier lugar, por ejemplo Here o here . A partir de la función, that es lo que me fascinó.
Al final, obtuve una solución más o menos simple, relativamente rápida y SIMPLE.
Cuerpo de la función
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS text CHARSET utf8
READS SQL DATA
BEGIN
DECLARE v_rec INT DEFAULT 0;
DECLARE done INT DEFAULT FALSE;
DECLARE v_res text DEFAULT '''';
DECLARE v_papa int;
DECLARE v_papa_papa int DEFAULT -1;
DECLARE csr CURSOR FOR
select _id,parent_id -- @n:=@n+1 as rownum,T1.*
from
(SELECT @r AS _id,
(SELECT @r := table_parent_id FROM table WHERE table_id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := the_id, @l := 0,@n:=0) vars,
table m
WHERE @r <> 0
) T1
where T1.parent_id is not null
ORDER BY T1.lvl DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open csr;
read_loop: LOOP
fetch csr into v_papa,v_papa_papa;
SET v_rec = v_rec+1;
IF done THEN
LEAVE read_loop;
END IF;
-- add first
IF v_rec = 1 THEN
SET v_res = v_papa_papa;
END IF;
SET v_res = CONCAT(v_res,''-'',v_papa);
END LOOP;
close csr;
return v_res;
END
Y luego tu solo
select get_lineage(the_id)
Espero que ayude a alguien :)
Es un poco complicado, comprueba si funciona para ti
select a.id,if(a.parent = 0,@varw:=concat(a.id,'',''),@varw:=concat(a.id,'','',@varw)) as list from (select * from recursivejoin order by if(parent=0,id,parent) asc) a left join recursivejoin b on (a.id = b.parent),(select @varw:='''') as c having list like ''%19,%'';
Enlace de violín de SQL http://www.sqlfiddle.com/#!2/e3cdf/2
Reemplace con su campo y nombre de tabla de manera apropiada.
He hecho una consulta para ti. Esto le dará categoría recursiva con una sola consulta:
SELECT id,NAME,'''' AS subName,'''' AS subsubName,'''' AS subsubsubName FROM Table1 WHERE prent is NULL
UNION
SELECT b.id,a.name,b.name AS subName,'''' AS subsubName,'''' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id WHERE a.prent is NULL AND b.name IS NOT NULL
UNION
SELECT c.id,a.name,b.name AS subName,c.name AS subsubName,'''' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id WHERE a.prent is NULL AND c.name IS NOT NULL
UNION
SELECT d.id,a.name,b.name AS subName,c.name AS subsubName,d.name AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id LEFT JOIN Table1 AS d ON d.prent=c.id WHERE a.prent is NULL AND d.name IS NOT NULL
ORDER BY NAME,subName,subsubName,subsubsubName
Aquí hay un fiddle .
Hizo lo mismo para otra pregunta aquí
Mysql select recursive obtener todos los niños con múltiples niveles
La consulta será:
SELECT GROUP_CONCAT(lv SEPARATOR '','') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(id SEPARATOR '','') FROM table WHERE parent_id IN (@pv)) AS lv FROM table
JOIN
(SELECT @pv:=1)tmp
WHERE parent_id IN (@pv)) a;
Lo encontré más fácil para:
1) crea una función que verificará si un elemento está en algún lugar de la jerarquía principal de otro. Algo así (no escribiré la función, hazlo con WHILE DO):
is_related(id, parent_id);
en tu ejemplo
is_related(21, 19) == 1;
is_related(20, 19) == 1;
is_related(21, 18) == 0;
2) use una sub selección, algo como esto:
select ...
from table t
join table pt on pt.id in (select i.id from table i where is_related(t.id,i.id));
Para las versiones de MySql que no son compatibles con las expresiones de tabla comunes (hasta la versión 5.7), lo lograría 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 fiddle .
El valor especificado en @pv := ''19'' debe establecerse en el id del padre del que desea seleccionar todos los descendientes de.
Esto funcionará también si un padre tiene varios hijos. Sin embargo, se requiere que cada registro cumpla con la condición parent_id < id , de lo contrario, los resultados no estarán completos.
Esta consulta utiliza una sintaxis MySql específica: las variables se asignan y modifican durante su ejecución. Se hacen algunas suposiciones sobre el orden de ejecución:
- La cláusula
fromse evalúa primero. Entonces ahí es donde@pvse inicializa. - La cláusula
wherese evalúa para cada registro en el orden de recuperación de los alias de. Por lo tanto, aquí es donde se establece que una condición solo incluye registros para los cuales el padre ya estaba identificado como perteneciente al árbol descendiente (todos los descendientes del primario primario se agregan progresivamente a@pv). - Las condiciones en esta cláusula
wherese 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 elida la lista principal, y esto solo debería ocurrir si elidpasa la primera condición. La función delengthsolo se llama para asegurarse de que esta condición sea siempre cierta, incluso si la cadenapvpor algún motivo arrojara un valor falso.
Con todo, uno puede encontrar que estas suposiciones son demasiado arriesgadas como para confiar en ellas, no existe una garantía documentada para ellas y, aunque funciona de manera coherente, el orden de evaluación puede, en teoría, cambiar cuando utiliza esta consulta como una vista o sub - consulta en una consulta más grande.
También tenga en cuenta que para conjuntos de datos muy grandes, esta solución puede ser lenta, ya que la operación find_in_set no es la forma más ideal de encontrar un número en una lista, ciertamente no en una lista que alcanza un tamaño en el mismo orden de magnitud que el número de los registros devueltos.
Alternativa 1: WITH RECURSIVE , CONNECT BY
Cada vez más bases de datos implementan el SQL: estándar ISO 1999 WITH [RECURSIVE] sintaxis WITH [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 , MariaDB 10.2.2+ ). Y a partir de la versión 8.0, también MySql lo admite . Con esa sintaxis, la consulta se ve así:
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;
Algunas bases de datos tienen una sintaxis alternativa, no estándar para búsquedas jerárquicas, como la cláusula CONNECT BY disponible en bases de datos Oracle. DB2 también admite esta sintaxis alternativa.
MySql versión 5.7 no ofrece tal característica. Cuando su motor de base de datos proporciona esta sintaxis, esa es ciertamente la mejor opción. Si no, entonces también considere las siguientes alternativas.
Alternativa 2: Identificadores de estilo de camino
Las cosas se vuelven mucho más fáciles si se asignan valores de id que contienen la información jerárquica: una ruta. Por ejemplo, en tu caso esto podría verse así:
ID | NAME
19 | category1
19/1 | category2
19/1/1 | category3
19/1/1/1 | category4
Entonces su select se vería así:
select id,
name
from products
where id like ''19/%''
Alternativa 3: Autocombinaciones repetidas
Si conoce un límite superior de cuán profundo puede llegar a ser el árbol jerárquico, puede usar un sql estándar como este:
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 fiddle
La condición where especifica qué padre desea recuperar los descendientes de. Puede ampliar esta consulta con más niveles según sea necesario.
Prueba estos:
Definición de la 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 de envoltura 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 un ejemplo:
SELECT id, name, getpath(id) AS path FROM category;
Salida:
+----+-----------+-----------------------------------------+
| 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%'';
Salida:
+----+-----------+-----------------------------------------+
| id | name | path |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2 |
| 21 | category3 | category1/category2/category3 |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+
Puede hacerlo así en otras bases de datos con bastante facilidad con una consulta recursiva (YMMV en el rendimiento).
La otra forma de hacerlo es almacenar dos bits de datos adicionales, un valor izquierdo y derecho. El valor de la izquierda y la derecha se derivan de un recorrido de pre-pedido de la estructura de árbol que está representando.
Esto se conoce como Trayectoria de árbol preordenar modificado y le permite ejecutar una consulta simple para obtener todos los valores primarios a la vez. También se conoce con el nombre de "conjunto anidado".
Si necesita velocidad de lectura rápida, la mejor opción es usar una tabla de cierre. Una tabla de cierre contiene una fila para cada par ancestro / descendiente. Entonces en tu ejemplo, la tabla de cierre se vería como
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 obtener 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, hay una gran desventaja cada vez que utiliza datos desnormalizados como este. Debe mantener la tabla de cierre junto con su tabla de categorías. La mejor forma es, probablemente, utilizar activadores, pero es algo complejo seguir correctamente las inserciones / actualizaciones / eliminaciones para las tablas de cierre. Al igual que con cualquier cosa, necesita ver sus requisitos 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. Hay diferentes soluciones óptimas para diferentes situaciones.
Solo use la clase de php de BlueM/tree para hacer el árbol de una tabla de auto-relación en mysql.
Tree y Tree / Node son clases de PHP para manejar datos que están estructurados jerárquicamente utilizando referencias padre de ID. Un ejemplo típico es una tabla en una base de datos relacional donde el campo "principal" de cada registro hace referencia a la clave primaria de otro registro. Por supuesto, Tree no solo puede usar datos que se originan en una base de datos, sino cualquier cosa: usted suministra los datos y Tree los usa, independientemente de dónde provengan los datos y cómo se procesaron. Lee mas
Aquí hay un ejemplo del uso de BlueM / tree:
<?php
require ''/path/to/vendor/autoload.php''; $db = new PDO(...); // Set up your database connection
$stm = $db->query(''SELECT id, parent, title FROM tablename ORDER BY title'');
$records = $stm->fetchAll(PDO::FETCH_ASSOC);
$tree = new BlueM/Tree($records);
...