[DB] Mysql WITH RECURSIVE 구문 사용 방법

 

안녕하세요!

 

Oracle의 경우 재귀 CTE 구문을 제공했지만, Mysql에서는 제공하지 못해 따로 구성해야됩니다

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;

 

 

마치면서

Mysql WITH 구문절에 RECURSIVE 키워드를 이용해 재귀 CTE를 구성할 경우 유용하게 다방면으로 사용 가능합니다.

이를 활용해서 다양한 쿼리를 생성 할 수 있으므로 유용하게 사용되었으면 좋겠습니다 :)