[MySQL] 계층 쿼리 (hierarchical query)

계층 쿼리란 무엇일까? 주로 트리 구조 데이터를 쿼리하는데 사용되며, 재귀쿼리(자기 자신을 반복적으로 참조, 부모-자식 참조)의 일종으로 계층적 데이터를 처리하는 쿼리이다. (이전 포스팅에서도 같은 설명)

 

mysql 은 oracle과는 다르게 2가지 방식으로 계층쿼리를 구할 수 있다1. 재귀 쿼리 : 서브 쿼리를 이용해서 자기자신을 호출 (재귀)2. CTE(Commen Table Expressions) : Mysql 8.0 이상 지원하며 키워드가 존재한다.

WITH RECURSIVE: 재귀적인 공통 테이블 표현식(CTE)을 정의
UNION ALL: 초기 쿼리와 재귀 쿼리를 결합하기 위해 사용,
                      초기 쿼리는 시작점을 정의,
                      재귀 쿼리는 부모-자식 관계를 탐색하여 계층을 생성
SELECT: 내부의 초기 쿼리와 재귀 쿼리에서 필요한 필드를 선택, 결과 집합에 포함될 열을 지정
JOIN: 부모와 자식 테이블을 연결, 부모-자식 관계를 기반으로 재귀적으로 데이터를 검색가능
ORDER BY: 정렬 순서를 지정, 일반적으로는 계층 구조를 표시할 때 레벨 순서로 정렬
LEVEL: 현재 깊이 레벨을 나타내는 열,재귀 쿼리에서 레벨을 증가시키면서 계층을 구성하는 데 사용
WHERE: 초기 쿼리에서 시작점을 정의, 일반적으로 특정 노드에서 계층적 탐색을 시작하기 위해 사용
JOIN 조건: JOIN 절 내에서 부모와 자식 테이블을 연결, 부모와 자식 테이블 간의 관계를 정의,
                     재귀적으로 데이터를 검색

 

예제를 생성해보자!

 

테이블 생성

CREATE TABLE `comments` (
  `comment_seq` int NOT NULL AUTO_INCREMENT,
  `lvl` int NOT NULL DEFAULT '0',
  `content` varchar(500) NOT NULL,
  `parent_comment_seq` int DEFAULT NULL,
  PRIMARY KEY (`comment_seq`),
  KEY `comments_FK_01` (`parent_comment_seq`),
  CONSTRAINT `comments_FK_01` FOREIGN KEY (`parent_comment_seq`) REFERENCES `comments` (`comment_seq`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 

 

데이터 추가

INSERT INTO comments (comment_seq, lvl, content, parent_comment_seq) VALUES (1, 0, '1번 댓글', NULL);
INSERT INTO comments (comment_seq, lvl, content, parent_comment_seq) VALUES (2, 0, '2번 댓글', NULL);
INSERT INTO comments (comment_seq, lvl, content, parent_comment_seq) VALUES (3, 1, '1번 대댓글', 1);
INSERT INTO comments (comment_seq, lvl, content, parent_comment_seq) VALUES (4, 2, '1번 대대댓글', 3);

 

 

1. 재귀 쿼리

SELECT a.*
FROM (SELECT c.comment_seq,
		     c.lvl,
		     c.content,
		     IFNULL(c.parent_comment_seq, c2.parent_comment_seq) AS parent_seq	  
FROM comments c 
LEFT JOIN comments c2 on c2.parent_comment_seq = c.comment_seq ) a
ORDER BY IFNULL(parent_seq,999999999), comment_seq ;

 

2. CTE(Commen Table Expressions)

WITH RECURSIVE CommentHierarchy AS (
    SELECT s.comment_seq, 
           s.lvl, 
           s.content, 
           IFNULL(s.parent_comment_seq, s1.parent_comment_seq) AS parent_seq
    FROM comments s
    LEFT JOIN comments s1 ON s1.parent_comment_seq = s.comment_seq
    
    UNION 
    
    SELECT c.comment_seq, 
           c.lvl, 
           c.content, 
           IFNULL(c.parent_comment_seq, ch.parent_seq) AS parent_seq
    FROM comments c
    JOIN CommentHierarchy ch ON c.parent_comment_seq = ch.comment_seq
)
SELECT *
FROM CommentHierarchy
ORDER BY IFNULL(parent_seq, 999999999), comment_seq;