[DB] MYSQL WITH RECURSIVE 예제

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

 

1. 기본 재귀 코드

 

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

      

      

      

    
    

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

    SELECT * FROM CTE1

 

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

 

 

2. 타 컬럼 추가 재귀 코드

 

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

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

      

      

      

    
    

      <sql />
    
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연산자를 이용해 필요한만큼 지정해놓고 사용하는 방법이 존재합니다.

      

      

      

    
    

      <sql />
    
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;