[mysql] MySQL 계층 적 재귀 쿼리를 만드는 방법

Program / 글쓴이

다음과 같은 MySQL 테이블이 있습니다.

id | name        | parent_id
19 | category1   | 0
20 | category2   | 19
21 | category3   | 20
22 | category4   | 21
......

이제 단일 ID를 제공하는 단일 MySQL 쿼리 (예 : ‘id = 19’)를 원하면 모든 자식 ID를 가져와야합니다 (예 : 결과는 ID ’20, 21,22 ‘이어야 함). … 또한 어린이의 계층 구조는 다양 할 수 있습니다 ….

또한 이미 for 루프를 사용하는 솔루션을 가지고 있습니다 ….. 가능한 경우 단일 MySQL 쿼리를 사용하여 동일한 결과를 얻는 방법을 알려주십시오.

답변

들어 MySQL은 8 + : 재귀 사용하는 with구문을.
들어 MySQL의 5.x를 : 사용 인라인 변수, 경로 ID를, 또는 자체 조인.

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;

에 지정된 값은 모든 자손을 선택하려는 부모 의 값 parent_id = 19으로 설정해야합니다 id.

MySQL 5.x

공통 테이블 표현식 (최대 버전 5.7)을 지원하지 않는 MySQL 버전의 경우 다음 쿼리를 사용하여이를 달성 할 수 있습니다.

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

여기 바이올린이 있습니다.

여기에 지정된 값 @pv := '19' 은id 모든 자손을 선택하려는 부모 .

부모가 여러 개인 경우에도 작동 합니다 자녀를 둔 합니다. 그러나 각 레코드가 조건을 충족해야합니다 parent_id < id. 그렇지 않으면 결과가 완료되지 않습니다.

쿼리 내 변수 할당

이 쿼리는 특정 MySQL 구문을 사용합니다. 실행 중에 변수가 할당 및 수정됩니다. 실행 순서에 대한 몇 가지 가정이 있습니다.

  • from절은 먼저 평가됩니다. 그래서 여기가@pv 초기화 입니다.
  • 이 where절은 from별명 에서 검색 순서대로 각 레코드에 대해 평가됩니다 . 따라서 여기에는 부모가 이미 자손 트리에있는 것으로 식별 된 레코드 만 포함되는 조건이 있습니다 (기본 부모의 모든 자손은@pv ).
  • 이 where절의 조건은 순서대로 평가되며 전체 결과가 확실 해지면 평가가 중단됩니다. 따라서 두 번째 조건은 id상위 목록에 추가되므로 두 번째 조건에 있어야하며 id이는 첫 번째 조건을 통과 한 경우에만 발생합니다 . length기능은 반드시이 조건이 경우에도, 항상 true인지 확인하기 위해 호출되는 pv문자열이 어떤 이유에 대한 falsy 값을 얻을 것입니다.

대체로, 이러한 가정은 의지하기에는 너무 위험 할 수 있습니다. 문서는 경고 :

당신은 당신이 기대하는 결과를 얻을 수 있지만 이것은 보장되지 않습니다 […] 사용자 변수와 관련된 표현식에 대한 평가 순서는 정의되어 있지 않습니다.

따라서 위의 쿼리와 일관되게 작동하더라도 조건을 추가하거나이 쿼리를 더 큰 쿼리에서 뷰 또는 하위 쿼리로 사용하는 경우와 같이 평가 순서가 계속 변경 될 수 있습니다. 향후 MySQL 릴리스에서 제거 될 “기능”입니다 .

이전 MySQL 릴리스에서는 이외의 명령문에서 사용자 변수에 값을 할당 할 수 SET있었습니다. 이 기능은 이전 버전과의 호환성을 위해 MySQL 8.0에서 지원되지만 향후 MySQL 릴리스에서는 제거 될 예정입니다.

위에서 언급했듯이 MySQL 8.0부터는 재귀를 사용해야합니다. with 구문을 합니다.

능률

매우 큰 데이터 세트의 경우 find_in_set조작이 목록에서 숫자를 찾는 가장 이상적인 방법이 아니므로 리턴 된 레코드 수와 동일한 크기의 크기에 도달하는 목록이 아닌 경우이 솔루션이 느려질 수 있습니다 .

대안 1 : with recursive,connect by

점점 더 많은 데이터베이스 가 재귀 쿼리에 대해 SQL : 1999 ISO 표준 WITH [RECURSIVE]구문 을 구현 합니다 (예 : 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+ ). 그리고 버전 8.0부터 MySQL도 지원합니다 . 사용할 구문은이 답변의 상단을 참조하십시오.

일부 데이터베이스에는 Oracle , DB2 , Informix , CUBRID 및 기타 데이터베이스에서 CONNECT BY사용 가능한 절 과 같은 계층 적 조회를위한 비표준 대체 구문이 있습니다.

MySQL 버전 5.7은 이러한 기능을 제공하지 않습니다. 데이터베이스 엔진이이 구문을 제공하거나 제공하는 구문으로 마이그레이션 할 수 있으면 이것이 가장 적합한 옵션 일 것입니다. 그렇지 않은 경우 다음 대안도 고려하십시오.

대안 2 : 경로 스타일 식별자

id계층 적 정보를 포함하는 값인 경로를 할당하면 상황이 훨씬 쉬워집니다 . 예를 들어 귀하의 경우 다음과 같이 보일 수 있습니다.

ID       | NAME
19       | category1
19/1     | category2
19/1/1   | category3
19/1/1/1 | category4

그렇다면 당신의 select모습은 다음과 같습니다.

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

대안 3 : 반복되는 자체 조인

계층 구조 트리의 깊이에 대한 상한을 알고 있으면 다음 sql과 같은 표준 쿼리를 사용할 수 있습니다 .

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;

이 바이올린을 참조하십시오

where하는 부모 조건을 지정 당신의 자손을 검색합니다. 필요에 따라 더 많은 레벨로이 쿼리를 확장 할 수 있습니다.

답변

블로그에서 MySQL에서 계층 데이터 관리 에서

테이블 구조

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

질문:

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';

산출

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

한 번에 대부분의 사용자는 SQL 데이터베이스에서 계층 적 데이터를 처리했으며 계층 적 데이터 관리가 관계형 데이터베이스의 목적이 아니라는 것을 의심하지 않았습니다. 관계형 데이터베이스의 테이블은 XML과 같이 계층 적이 지 않지만 단순한 목록입니다. 계층 적 데이터에는 관계형 데이터베이스 테이블에 자연스럽게 표현되지 않는 부모-자식 관계가 있습니다.
더 읽어보기

자세한 내용은 블로그를 참조하십시오.

편집하다:

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

산출:

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

참조 : MySQL에서 재귀 SELECT 쿼리를 수행하는 방법은 무엇입니까?

답변

이것들을보십시오 :

테이블 정의 :

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

재귀 저장 프로 시저 :

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 ;

저장 프로 시저에 대한 랩퍼 기능 :

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%';

산출:

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

답변

내가 생각해 낸 가장 좋은 방법은

  1. 계보를 사용하여 트리를 저장 / 정렬 \ 추적합니다. 그것은 충분하고, 다른 어떤 접근법보다 읽기에 수천 배나 빠르게 작동합니다. 또한 DB가 변경 되더라도 해당 패턴을 유지할 수 있습니다 (모든 DB에서 해당 패턴을 사용할 수 있기 때문에)
  2. 특정 ID의 연계를 결정하는 기능을 사용하십시오.
  3. 선택, CUD 작업 또는 작업별로 원하는대로 사용하십시오.

계보 접근 방식 설명 어디서나 찾을 수 있습니다 (예 :
Here 또는 here) . 기능적으로- 그것이 저를 고무 시켰습니다.

결국 단순하고 비교적 빠르고 간단한 솔루션을 얻었습니다.

기능의 몸

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

그리고 당신은 단지

select get_lineage(the_id)

누군가에게 도움이되기를 바랍니다 🙂

답변

다른 질문에 대해서도 같은 일을 했습니까?

MySQL의 선택 재귀는 여러 수준의 모든 자녀를 얻습니다.

쿼리는 다음과 같습니다.

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;

답변

빠른 읽기 속도가 필요한 경우 가장 좋은 방법은 클로저 테이블을 사용하는 것입니다. 클로저 테이블에는 각 조상 / 후손 쌍에 대한 행이 포함됩니다. 따라서 귀하의 예에서 클로저 테이블은 다음과 같습니다.

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

이 테이블이 있으면 계층 적 쿼리가 매우 쉽고 빠릅니다. 카테고리 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

물론 이와 같이 비정규 화 된 데이터를 사용할 때마다 큰 단점이 있습니다. 카테고리 테이블과 함께 클로저 테이블을 유지 보수해야합니다. 가장 좋은 방법은 트리거를 사용하는 것이지만 클로저 테이블의 삽입 / 업데이트 / 삭제를 올바르게 추적하는 것은 다소 복잡합니다. 다른 것과 마찬가지로 요구 사항을보고 어떤 방법이 가장 적합한 지 결정해야합니다.

편집 : 질문 참조 관계형 데이터베이스에 계층 적 데이터를 저장하기위한 옵션은 무엇입니까? 더 많은 옵션. 상황에 따라 다른 최적의 솔루션이 있습니다.

답변

자식의 첫 재귀를 나열하는 간단한 쿼리 :

select @pv:=id as id, name, parent_id
from products
join (select @pv:=19)tmp
where parent_id=@pv

결과:

id  name        parent_id
20  category2   19
21  category3   20
22  category4   21
26  category24  22

… 왼쪽 조인 사용시 :

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

모든 자녀를 나열하는 @tincot의 솔루션 :

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)

Sql Fiddle로 온라인 테스트 모든 결과를 확인하십시오.

http://sqlfiddle.com/#!9/a318e3/4/0