[MySQL] 재귀(recursive) 쿼리, heirarchy 구조 > db

mysql 계층형 재귀 쿼리 사용법

위와 같은 계층형 테이블에서 기준 id값으로 자식 노드를 반환하도록 하는 방법

- 테스트 준비

CREATE TABLE RecursionTest
(
id INTEGER NOT NULL,
name VARCHAR(128) NULL,
parent INTEGER NULL,
CONSTRAINT pk_RecursionTest PRIMARY KEY (id)
);

INSERT INTO RecursionTest (id, name, parent) VALUES (1, 'Root', NULL);
INSERT INTO RecursionTest (id, name, parent) VALUES (2, 'Branch A', 1);
INSERT INTO RecursionTest (id, name, parent) VALUES (3, 'Branch B', 1);
INSERT INTO RecursionTest (id, name, parent) VALUES (4, 'Branch C', 1);
INSERT INTO RecursionTest (id, name, parent) VALUES (5, 'Branch A2', 2);
INSERT INTO RecursionTest (id, name, parent) VALUES (6, 'Branch B2', 3);
INSERT INTO RecursionTest (id, name, parent) VALUES (7, 'Branch B3', 6);
INSERT INTO RecursionTest (id, name, parent) VALUES (8, 'Branch B4', 7);

테이블을 만들어주고 테스트 테이블을 만들어 주어 첫번째 구조와 같은 테이블을 생성 하였습니다.

1번 방법

select  id,
        name,
        parent
from    (select * from RecursionTest
         order by parent, id) products_sorted,
        (select @pv := '3') initialisation
where   find_in_set(parent, @pv) > 0
and     @pv := concat(@pv, ',', id)

3을 기준으로 자식 노드를 뽑는 쿼리로 실행 결과

계층 구조 

3 > 6 > 7 > 8  

결과 값(id)

6, 7, 8 

2번 방법

with recursive cte (id, name, parent) as
(
 select     id,
            name,
            parent
 from       recursiontest
 where      parent = 3
 union all
 select     r.id,
            r.name,
            r.parent
 from       recursiontest r
 inner join cte
         on r.parent = cte.id
)

select * from cte;

마찬가지 3기준 

결과 값은 동일 합니다.

하지만 첫번째는 하위 쿼리 등으로 사용시 동일한 결과 값을 보장 할 수 없기 때문에 

저의 경우 두번째 방법을 사용하였습니다.