MySQL 재귀 CTE 마스터하기:계층 탐색 및 시리즈 생성

재귀 CTE(공통 테이블 표현식)는 자체 이름을 사용하여 자신을 참조하는 하위 쿼리입니다. WITH RECURSIVE을 사용하여 정의됩니다. 그리고 종료 조건이 있어야 합니다. 재귀 CTE는 계열 생성, 계층적 데이터 순회 및 그래프 순회에 유용합니다.

구문

WITH RECURSIVE cte_name (col1, col2, ...) AS (
 -- Non-recursive (base case): initial rows
 SELECT col1, col2 FROM table_name
 UNION ALL
 -- Recursive case: references cte_name
 SELECT col1, col2 FROM cte_name WHERE condition
)
SELECT * FROM cte_name;

• 첫 번째 SELECT 기본 사례는 초기 행을 제공합니다.
• UNION ALL 각 반복에서 행을 추가합니다(DISTINCT 중복을 제거합니다).
• 두 번째 SELECT WHERE 조건이 실패할 때까지 재귀 사례가 실행됩니다.

예 1:처음 5개 홀수 생성

WITH RECURSIVE odd_no (sr_no, n) AS (
 SELECT 1, 1
 UNION ALL
 SELECT sr_no + 1, n + 2
 FROM odd_no
 WHERE sr_no < 5
)
SELECT * FROM odd_no;

+-------+---+
| sr_no | n |
+-------+---+
| 1 | 1 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 9 |
+-------+---+

기본 사례는 (1, 1)을 반환합니다. 각 반복은 sr_no를 1씩, n을 2씩 증가시킵니다. sr_no가 5에 도달하면 재귀가 중지됩니다.

예 2:직원 계층 구조

관리자-직원 계층을 횡단하는 보다 실용적인 사용 ?

-- Assume: employees(id, name, manager_id)
WITH RECURSIVE org_chart (id, name, level) AS (
 -- Base: top-level manager (no manager)
 SELECT id, name, 0
 FROM employees
 WHERE manager_id IS NULL
 UNION ALL
 -- Recursive: find direct reports
 SELECT e.id, e.name, oc.level + 1
 FROM employees e
 JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;

이렇게 하면 최상위 관리자(레벨 0)부터 시작하여 조직 트리를 구축하고 각 레벨의 모든 보고서를 반복적으로 찾습니다.

핵심사항

• 무한 루프를 방지하려면 재귀 SELECT의 WHERE 절에 항상 종료 조건을 포함하세요.
• MySQL의 기본 재귀 제한은 1000회 반복입니다(cte_max_recursion_depth를 통해 구성 가능). ).
• UNION ALL 사용 성능을 위해; UNION DISTINCT 중복 항목을 제거해야 하는 경우에만.

결론

재귀적 CTE는 WITH RECURSIVE를 사용합니다. 기본 사례와 재귀 사례를 사용하여 자체 참조 쿼리를 정의합니다. 이는 MySQL의 계층적 데이터 순회(조직 차트, 범주 트리), 시리즈 생성 및 그래프 순회에 필수적입니다.