MySQL 버전 : 8.0.32
MySQL recursive SQL 에 대해 설명
재귀 공통 테이블 식은 자체 이름을 참조하는 하위 쿼리가 있는 식입니다. 예를 들어 아래와 같은 형식입니다.
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
재귀 CTE(Common Table Expressions)의 구조는 다음과 같습니다.
- WITH 절의 CTE가 자신을 참조하는 경우 WITH 절은 WITH RECURSIVE로 시작해야 합니다.
- 재귀 CTE 하위 쿼리에는 UNION ALL 또는 UNION [DISTINCT]로 구분되는 두 부분이 있습니다.
SELECT ... -- return initial row set
UNION ALL
SELECT ... -- return additional row sets
첫 번째 SELECT는 CTE에 대한 초기 행을 생성하며 CTE 이름을 참조하지 않습니다. 두 번째 SELECT는 추가 행을 생성하고 FROM 절에서 CTE 이름을 참조하여 재귀합니다. 두 번째 SELECT가 새 행을 생성하지 않으면 재귀가 종료됩니다. Recursive CTE는 비재귀적 SELECT 부분과 재귀적 SELECT 부분으로 구성됩니다.
- CTE의 컬럼타입은 비재귀적 SELECT 부분의 컬럼타입이며 모두 null을 허용합니다.
- 비재귀 부분과 재귀 부분이 UNION [DISTINCT]로 구분되면 중복 행이 제거됩니다. 이는 무한 루프를 피하기 위해 전이적 폐쇄를 수행하는 쿼리에 유용합니다.
- 재귀 부분의 각 반복은 이전 반복에서 생성된 행에 대해서만 작동합니다.
다음 쿼리는 재귀적 CTE 하위 쿼리의 초기 행 집합을 생성하는 비재귀적 부분입니다.
CTE 하위의 아래 쿼리는 재귀 부분입니다.
SELECT n + 1 FROM cte WHERE n < 5
해당 SELECT는 이전 행 집합의 n 값보다 하나 더 큰 새 값을 가진 행을 생성합니다. 첫 번째 반복은 초기 행 세트(1)에 대해서 작동하고 1+1=2를 생성합니다. 두 번째 반복은 첫 번째 반복의 행 집합(2)에서 작동하고 2+1=3을 생성합니다. n이 5보다 작지 않을 때까지 계속됩니다.
CTE의 재귀 부분이 비재귀 부분보다 열에 대해 더 길은 값을 생성하는 경우 데이터 잘림을 방지하기 위해 비재귀 부분의 열을 확장해야 할 수 있습니다.
WITH RECURSIVE cte AS
(
SELECT 1 AS n, 'abc' AS str
UNION ALL
SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;
nonstrict SQL 모드에서는 위의 SQL문은 다음을 출력합니다.
비재귀적 SELECT가 열의 길이를 결정하기 때문에 str 열 값은 모두 'abc'입니다. 결과적으로 재귀 SELECT에 의해 생성된 더 길은 str 값이 잘립니다.
+------+------+
| n | str |
+------+------+
| 1 | abc |
| 2 | abc |
| 3 | abc |
+------+------+
strict SQL모드에서는 이 명령문은 오류를 발생합니다.
ERROR 1406 (22001): Data too long for column 'str' at row 1
이 문제를 해결하려면 명령문이 잘림이나 오류를 생성하지 않도록 비재귀 SELECT에서 CAST()를 사용하여 str 열의 길이를 크게 만들어야 합니다.
WITH RECURSIVE cte AS
(
SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
UNION ALL
SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;
이제 명령문은 잘림 없이 다음 결과를 생성합니다.
+------+--------------+
| n | str |
+------+--------------+
| 1 | abc |
| 2 | abcabc |
| 3 | abcabcabcabc |
+------+--------------+
일부 구문 제약 조건은 재귀 CTE 하위 쿼리 내에서 적용됩니다.
재귀 SELECT 부분은 다음 구문을 포함하지 않아야 합니다.
- Aggregate functions such as SUM()
- Window functions
- GROUP BY
- ORDER BY
- DISTINCT
이러한 제약 조건은 재귀 CTE의 비재귀 SELECT 부분에는 적용되지 않습니다.
재귀 SELECT 부분은 sub 쿼리가 아닌 FROM 절에서만 한 번만 CTE를 참조해야 합니다. CTE 이외의 테이블을 참조하고 CTE와 조인할 수 있습니다.
Limiting Common Table Expression Recursion
재귀 CTE의 경우 재귀 SELECT 부분에 재귀를 종료하는 조건이 포함되어야 합니다. 런어웨이 재귀 CTE를 방지하기 위한 개발 기법으로 실행 시간을 제한하여 강제 종료할 수 있습니다.
- cte_max_recursion_depth 시스템 변수는 CTE의 재귀 수준에 대한 제한을 적용합니다. 이 변수 값보다 더 많은 수준을 반복하는 모든 CTE의 실행을 종료합니다.
- max_execution_time 시스템 변수는 현재 세션 내에서 실행되는 SELECT 문에 대한 실행 제한 시간을 적용합니다.
- MAX_EXECUTION_TIME 옵티마이저 힌트는 나타나는 SELECT 문에 대해 쿼리당 실행 제한 시간을 적용합니다.
재귀 CTE가 재귀 실행 종료 조건 없이 실수로 작성되었다고 가정합니다.
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte
)
SELECT * FROM cte;
-- cte_max_recursion_depth=1000 설정 시 오류 발생 --
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations.
Try increasing @@cte_max_recursion_depth to a larger value.
기본적으로 cte_max_recursion_depth 값은 1000이므로 CTE가 1000 수준을 초과하여 재귀하면 종료됩니다.
MySQL 8.0.19부터 재귀 쿼리 내에서 LIMIT를 사용하여 가장 바깥쪽 SELECT에 반환할 최대 행 수를 지정할 수도 있습니다. 예를 들면 다음과 같습니다.
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte LIMIT 900
)
SELECT * FROM cte;
Recursive Common Table Expression Examples
Hierarchical Data Traversal
재귀 공통 테이블 표현식은 계층 구조에 유용합니다. 아래의 예시를 살펴봅니다. 최상위 직원(CEO)의 관리자 ID는 NULL(관리자 없음)입니다.
CREATE TABLE employees (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
manager_id INT NULL,
INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333), # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);
각 직원의 관리 체인(즉, CEO에서 직원으로의 경로)이 포함된 조직도를 생성하려면 재귀 CTE를 사용합니다.
아래 예제는 사번ID의 체인경로를 조회한다. 이름으로 하고 싶은 경우에는 id 대신 name 을 사용하면 된다.
WITH RECURSIVE employee_paths (id, name, path) AS
(
SELECT id, name, CAST(id AS CHAR(200))
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, CONCAT(ep.path, '->', e.id)
FROM employee_paths AS ep JOIN employees AS e
ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;
+------+---------+--------------------+
| id | name | path |
+------+---------+--------------------+
| 333 | Yasmina | 333 |
| 198 | John | 333->198 |
| 29 | Pedro | 333->198->29 |
| 4610 | Sarah | 333->198->29->4610 |
| 72 | Pierre | 333->198->29->72 |
| 692 | Tarek | 333->692 |
| 123 | Adil | 333->692->123 |
+------+---------+--------------------+
MySQL recursive SQL로 데이터 생성하기
아래와 같은 WITH RECURSIVE SQL로 임시 데이터를 생성할 수 있습니다.
임시 데이터를 입력할 테이블을 생성합니다.
CREATE TABLE cte_populated
(
id INT NOT NULL PRIMARY KEY,
val varchar(20)
) ENGINE = InnoDB;
INSERT 문장과 WITH RECURSIVE 문장으로 임시 데이터를 입력합니다. 아래 예제는 1부터 2000000까지 2000000건의 데이터를 입력하는 문장입니다. 이를 응용하여 다양하게 데이터를 생성할 수 있습니다.
INSERT cte_populated(id, val)
WITH RECURSIVE int_seq AS (
SELECT 1 AS id, cast('test1' as char(200)) as pass
UNION ALL
SELECT id + 1, concat('test',id)
FROM int_seq
WHERE id < 2000000)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 3M) */ id, pass as id FROM int_seq;
/*+ SET_VAR(cte_max_recursion_depth = 3M) */ 힌트는 서버에 cte_max_recursion_depth 파라미터의 기본값이 1000으로 설정되어 있어 depth가 1000 이상인 경우 오류가 발생합니다. 이럴 경우 1000 건 이상 입력 시 힌트를 사용하여 원하는 만큼의 데이터를 생성할 수 있습니다. 3M에서 'M'은 백만입니다.
