WITH RECURSIVE CTE 사용하기 (MySQL)

 

아래 글의 문제는 WITH RECURSIVE CTE의 다른 사용 예시

2024.07.05 - [MySQL] - [프로그래머스] 입양 시각 구하기(2) (MySQL) - WITH RECURSIVE CTE

2024.09.09 - [MySQL] - [프로그래머스] LV.5 멸종위기의 대장균 찾기 (MySQL) - WITH RECURSIVE cte

 

MySQL :: MySQL 8.4 Reference Manual :: 15.2.20 WITH (Common Table Expressions)

WITH RECURSIVE CTE를 이해하기 위해 MySQL 공식문서에서 CTE를 찾아봤다.

 

+)
글을 이해하려면 기본적으로 SELECT 0 FROM TABLE1 쿼리의 결과를 알아야 한다.

위 쿼리는 TABLE1의 원 컬럼들과 상관없이, TABLE1의 총 ROW 수(=데이터의 개수) 만큼의 0을 반환한다.

SELECT 0 [AS ZEROS] ~  처럼 새로운 컬럼명을 명시할 수도 있지만, 생략한다면 컬럼명도 0이 된다.

이러한 쿼리는 주로 행의 개수를 카운트하거나, 특정 로직을 테스트하기 위한 임시 데이터로 사용할 때 유용하다.

 

아래처럼 WITH와 UNION ALL 또는 UNION을 이용해 완전히 새로운 테이블을 만드는 것도 가능하다.

 

 

 

 

 

이제 본론인 WITH RECURSIVE CTE를 살펴본다.

아래 쿼리가 대표 예제 격인 것 같다. 

 

구조는 다음과 같다.

▲ UNION [ALL] 이전 SELECT에서는 첫 번째 행만 반환하고, 이후 SELECT에서 나머지 행을 반환한다.

▲ 두 번째 SELECT에서 보통 iteration을 돌리고, WHERE의 중단조건에 걸리면 멈춘다. 

 

 

 

추가로, 반복에 따라 데이터의 길이가 계속 길어지는 쿼리라면 뒷부분이 Truncate되거나 에러가 발생할 수 있다.

nonstrict mode에서는 첫 번째 SELECT에서 반환한 데이터의 길이에 맞춰 나머지 데이터들도 truncate된다.

strict mode에서는 'Data too long for column '컬럼명' at row 1' 에러가 난다.

 

이 문제를 예방하려면 첫 번째 SELECT(=nonrecursive SELECT)에서 데이터의 길이를 길게 설정해주면 된다. 

CAST(' ' AS CHAR(원하는 길이)) 를 이용한다.

 

 

 

 

 

참고자료

https://dev.mysql.com/doc/refman/8.4/en/sql-mode.html#sql-mode-strict