MySQL 재귀 호출 관련

https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query

 

CREATE TABLE category(         category_id INT AUTO_INCREMENT PRIMARY KEY,         name VARCHAR(20) NOT NULL,         parent INT DEFAULT NULL ); INSERT INTO category VALUES(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); SELECT * FROM category ORDER BY category_id; +-------------+----------------------+--------+ | 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 | +-------------+----------------------+--------+

MySQL 버전 8 이상

 

To produce the organizational chart with the management chain for each employee (that is, the path from CEO to employee), use a recursive CTE:

WITH RECURSIVE employee_paths (id, name, path) AS (   SELECT id, name, CAST(id AS CHAR(200))     FROM employees     WHERE manager_id IS NULL   UNION ALL   SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)     FROM employee_paths AS ep JOIN employees AS e       ON ep.id = e.manager_id ) SELECT * FROM employee_paths ORDER BY path;

The CTE produces this output:

+------+---------+-----------------+ | id   | name    | path            | +------+---------+-----------------+ |  333 | Yasmina | 333             | |  198 | John    | 333,198         | |   29 | Pedro   | 333,198,29      | | 4610 | Sarah   | 333,198,29,4610 | |   72 | Pierre  | 333,198,29,72   | |  692 | Tarek   | 333,692         | |  123 | Adil    | 333,692,123     | +------+---------+-----------------+

 

https://dev.mysql.com/doc/refman/8.0/en/with.html#commn-table-expressions-recursive

 

 

WITH RECURSIVE cte_name AS (     initial_query  -- anchor member     UNION ALL     recursive_query -- recursive member that references to the CTE name ) SELECT * FROM cte_name;

 

https://www.mysqltutorial.org/mysql-recursive-cte/

 

Simple MySQL recursive CTE example

WITH RECURSIVE cte_count (n)  AS (       SELECT 1       UNION ALL       SELECT n + 1        FROM cte_count        WHERE n < 3     ) SELECT n  FROM cte_count;

 

I have a MySQL table which is as follows:

idnameparent_id

19	category1	0
20	category2	19
21	category3	20
22	category4	21
...	...	...

Now, I want to have a single MySQL query to which I simply supply the id [for instance say id=19] then I should get all its child ids [i.e. result should have ids '20,21,22']....

 

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;

 

MySQL 5.x

For MySQL versions that do not support Common Table Expressions (up to version 5.7), you would achieve this with the following query:

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

 

프로시져

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; 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); Recursive Stored procedure: 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 ; Wrapper function for the stored procedure: 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 ; SELECT id, name, getpath(id) AS path FROM category; +----+-----------+-----------------------------------------+ | 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 | +----+-----------+-----------------------------------------+ SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%'; Output: +----+-----------+-----------------------------------------+ | id | name      | path                                    | +----+-----------+-----------------------------------------+ | 20 | category2 | category1/category2                     | | 21 | category3 | category1/category2/category3           | | 22 | category4 | category1/category2/category3/category4 | +----+-----------+-----------------------------------------+