[DB] MYSQL WITH RECURSIVE 예제

MYSQL에서 WITH 구문절에 RECURSIVE 키워드를 이용해 재귀 CTE를 구성할 수 있습니다.

 

기본 재귀 코드

 

이러한 재귀 CTE는 보통 연속된 값들을 구현하기 위해서 사용합니다.

WITH RECURSIVE CTE1 AS (
	SELECT 0 RNUM
    FROM DUAL
    
    UNION ALL
    
    SELECT RNUM+1
    FROM CTE1
    WHERE RNUM < 20
)

    SELECT * FROM CTE1

 

위의 쿼리를 수행하게 되면 0부터 20까지의 연속된 숫자를 가지고 있는 데이터를 생성 할 수 있습니다.

 

 

타 컬럼 추가 재귀 코드

 

추가적으로 연속된 값 말고도, 다른 컬럼을 생성할 수 있습니다. 

대신에 타 컬럼을 추가할때는 크기 값을 주의 해서 사용해야됩니다!!

WITH RECURSIVE CTE1 AS (
	SELECT 0 RNUM , 'STR'
    FROM DUAL
    
    UNION ALL
    
    SELECT RNUM+1 , STR
    FROM CTE1
    WHERE RNUM < 2
)

    SELECT * FROM CTE1

 

재귀적이지 않은 컬럼이 추가 될 경우, 재귀 CTE의 데이터 크기 값은 재귀적이지 않은 파트 = STR 초기 컬럼의 데이터 크기 사이즈로 측정됩니다. (현재 데이터 크기는 3)

 

그로인해 CONCAT 연산자를 이용해서 문자열을 연결할 경우에는 아래와 같이 오류가 발생됩니다.

 

' SQL Error [1406] [22001]: Data truncation: Data too long for column 'STR' at row 1 ' 의 오류가 발생됩니다.

데이터 크기가 3으로 지정되어 있는데, CONCAT 연산자를 통해서 추가하다보니 컬럼크기값을 넘어 발생한 오류입니다.

 

이를 위해서는 초기 컬럼값의 크기를 CAST연산자를 이용해 필요한만큼 지정해놓고 사용하는 방법이 존재합니다.

WITH RECURSIVE CTE1 AS (
	SELECT 0 AS RNUM, CAST('STR' AS CHAR(11)) AS STR
    FROM DUAL
    
    UNION ALL
    
    SELECT RNUM+1 , CONCAT(STR,RNUM+1)
    FROM CTE1
    WHERE RNUM < 8
)

    SELECT * FROM CTE1;