MariaDB 강좌 / 재귀 CTE로 계층 데이터 조회하고 순환 막기

재귀 공통 테이블 식(Recursive Common Table Expression, 재귀 CTE)은 시작 행에서 출발해 부모·자식 관계를 반복 조회하는 SQL 표현입니다. 계층을 탐색할 때는 시작 조건뿐 아니라 종료 조건, 경로의 자료형, 순환 방지와 최종 정렬까지 함께 설계해야 합니다.

일반 CTE와 재귀 CTE의 차이

일반 CTE는 긴 조회를 이름 있는 단계로 나누는 데 사용합니다. 재귀 CTE는 그 이름을 자신의 본문에서 다시 참조합니다. 처음 행을 만드는 앵커 부분과, 직전 단계의 행을 이용해 다음 행을 만드는 재귀 부분을 UNION ALL로 연결합니다.

조직도나 상품 분류처럼 깊이가 고정되지 않은 구조에 적합합니다. 다만 모든 계층 조회를 재귀로 바꿔야 하는 것은 아닙니다. 깊이가 두 단계로 고정된 데이터는 JOIN이 더 간단할 수 있고, 매우 큰 트리는 자식 검색 인덱스와 최대 깊이를 함께 검토해야 합니다. MariaDB 재귀 CTE 설명은 반복 계산과 앵커 자료형의 역할을 설명합니다.

서로 독립적인 분류 여섯 개 준비하기

다음 SQL은 전용 실습 데이터베이스 appops_lab에 category_tree를 만듭니다. 관리자 또는 해당 스키마의 CREATE 권한이 있는 실습 계정으로 실행하세요. 이미 같은 이름의 테이블이 있다면 생성문을 반복하지 말고 기존 실습 상태를 확인합니다. 외래키는 부모 행의 존재를 검사하지만 계층 전체의 순환까지 막지는 않습니다.

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE category_tree (
  category_id INT NOT NULL PRIMARY KEY,
  parent_id INT NULL,
  category_name VARCHAR(40) NOT NULL,
  INDEX ix_parent (parent_id),
  CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
    REFERENCES category_tree(category_id)
) ENGINE=InnoDB;
INSERT INTO category_tree VALUES
  (1, NULL, 'Catalog'), (2, 1, 'Books'), (3, 1, 'Tools'),
  (4, 2, 'SQL'), (5, 2, 'Python'), (6, 3, 'Editors');

Catalog 아래에 Books와 Tools가 있고, Books 아래에 SQL·Python, Tools 아래에 Editors가 있습니다. parent_id 인덱스는 재귀 단계에서 자식을 찾는 조건을 지원합니다. 이 글의 데이터는 여섯 행이므로 실행 속도 수치를 일반화하지 않고 결과의 관계를 확인합니다.

시작 행·깊이·경로를 갖는 전체 쿼리

WITH RECURSIVE tree AS (
  SELECT category_id, parent_id, category_name, 0 AS depth,
         CAST(CONCAT('/', category_id, '/') AS CHAR(1000)) AS visited,
         CAST(category_name AS CHAR(1000)) AS name_path
  FROM category_tree WHERE category_id = 1
  UNION ALL
  SELECT c.category_id, c.parent_id, c.category_name, t.depth + 1,
         CONCAT(t.visited, c.category_id, '/'),
         CONCAT(t.name_path, ' > ', c.category_name)
  FROM category_tree c JOIN tree t ON c.parent_id = t.category_id
  WHERE t.depth < 8
    AND LOCATE(CONCAT('/', c.category_id, '/'), t.visited) = 0
)
SELECT category_id, parent_id, depth, name_path
FROM tree ORDER BY depth, category_id;

첫 SELECT는 ID 1을 깊이 0으로 반환합니다. 두 번째 SELECT는 c.parent_id = t.category_id인 자식을 찾아 깊이를 하나 늘립니다. visited는 숫자 ID를 슬래시로 구분한 경로입니다. 구분자가 있어 ID 1과 11을 다른 값으로 검사할 수 있습니다.

name_path와 visited는 앵커에서 CHAR(1000)으로 명시했습니다. 앵커의 짧은 문자열 길이만으로 자료형이 정해지면 재귀 과정에서 길어진 경로가 오류나 잘림을 일으킬 수 있습니다. 이 예제는 최대 깊이 8, 짧은 이름과 정수 ID를 전제로 하며 실제 최대 경로가 더 길면 허용 길이와 종료 기준을 다시 계산해야 합니다.

이 결과 순서는 마지막 ORDER BY depth, category_id가 정합니다. 재귀 처리 순서를 화면 정렬로 간주하지 마세요. 문자열 이름 경로의 사전순 정렬은 숫자 ID 순서와 다르므로 원하는 표시 기준을 별도로 정해야 합니다.

한 가지 조건을 바꾸어 탐색 범위 확인하기

전체 쿼리에서 t.depth < 8만 t.depth < 1로 바꾸고 다시 실행하세요. 깊이 0에서 생성하는 바로 아래 자식까지만 포함되어 ID 1·2·3의 세 행이 나옵니다. 그 뒤 조건을 8로 되돌립니다. 시작 조건을 category_id = 2로 바꾸면 Books를 새 깊이 0으로 하는 2·4·5의 부분 트리를 조회할 수 있습니다.

깊이 제한에 걸려 결과가 잘렸다면 정상적인 완전 조회라고 판단하면 안 됩니다. 실제 구조가 더 깊은지 확인하고, 허용 깊이 변경이 필요한지 판단하세요. 서버의 max_recursive_iterations 설정도 과도한 반복을 제한하지만 업무상 종료 조건이나 올바른 계층을 대신 설계해 주지는 않습니다.

순환 데이터를 넣고 원래대로 되돌리기

같은 연결에서 다음 두 문장을 실행한 뒤 위의 전체 재귀 쿼리를 다시 실행합니다. 다른 연결에서는 아직 확정되지 않은 변경이 보이지 않을 수 있습니다.

START TRANSACTION;
UPDATE category_tree SET parent_id = 4 WHERE category_id = 1;

1의 부모를 4로 바꾸면 1 → 2 → 4 → 1의 순환이 생깁니다. 명시적으로 ID 1에서 시작하는 이 쿼리는 이미 방문한 ID 1로 되돌아가는 분기를 제외하여 여섯 행에서 끝납니다. 관찰을 마쳤으면 반드시 다음 문장으로 데이터만 원래 상태로 되돌리고 확인합니다.

ROLLBACK;
SELECT category_id, parent_id FROM category_tree WHERE category_id = 1;

부모는 다시 NULL이어야 합니다. 시작 조건이 parent_id IS NULL이었다면 순환 때문에 루트가 사라져 탐색되지 않는 행도 생길 수 있습니다. 쿼리의 방문 검사만으로 데이터베이스 전체에 순환이 없다고 증명할 수는 없습니다. MariaDB의 CYCLE ... RESTRICT 구문도 대안이지만 이 예제는 방문 경로를 명시하여 탐색 규칙을 보여 줍니다.

운영 데이터에 적용하기 전 확인할 기준

• 앵커가 원하는 루트 한 개 또는 여러 개를 정확히 선택하는지 확인합니다.
• 부모 키 존재, 자기 자신을 부모로 지정한 행, 순환과 도달할 수 없는 행을 구분해 검사합니다.
• 깊이 제한과 경로 자료형이 허용하는 최대 크기를 함께 계산합니다.
• 자식 검색 인덱스와 EXPLAIN을 확인하고, 결과 행 수가 예상 관계와 맞는지 대조합니다.

실습을 정리하려면 이 글에서 만든 테이블만 확인한 후 DROP TABLE appops_lab.category_tree;를 실행합니다. 이 삭제는 ROLLBACK으로 취소되지 않으므로 업무 테이블 이름으로 바꾸어 실행하지 마세요.