MySQL CTE와 파생 테이블의 기본 개념부터 활용 방법

CTE

CTE란 Common Table Expression의 약자로 서브 쿼리로 사용되는 파생 테이블과 비슷한 개념입니다.

 

데이터베이스에서 view와 다른 점은 view는 가상 테이블을 생성하고 유지가 되지만, cte는 일회성 테이블로 하나의 쿼리문이 끝나면 자동으로 삭제됩니다.

 

CTE 사용 상황

• View를 생성할 수 없는 권한일 때 대체제로 사용 가능합니다.
• 일회성 가상 테이블을 생성할 수 있습니다.

저는 CTE를 데이터 값 업데이트 하는 데 사용하였습니다.

백업 데이터를 만들어야 하는 경우가 있었는데, 과거 데이터를 현재 데이터보다 id 값을 적게 해주어야 하는 요구사항이 있었습니다.

 

Why? 그냥 id를 auto_increment로 증가하면 되지 않나,

관리자 페이지에서 백업 데이터가 추가되는 테이블을 id 값을 기준으로 최신순 정렬하고 있어서, id 값 조정이 필요했습니다.
이를 위해서, 현재 저장된 테이블의 pk 값을 백업 데이터 개수만큼 + N 하였습니다.

 

PK 값을 변경하면 문제가 있지 않은지?

PK 값을 참조하고 있는 테이블이 있으면 같이 변동해주어야 데이터 일관성에 문제가 생기지 않습니다. 해당 테이블의 경우에는 다른 테이블에서 참조하고 있는 pk 값이 아니어서 CTE를 이용하여 처리했습니다.

 

처음 작성한 SQL 문

UPDATE table1 tb
SET tb.id = tb.id + 3000
WHERE tb.id IN (
	SELECT tbb.id
    FROM table1 tbb
    WHERE tbb.id >= 0
)

 

문제점

1. id 값을 작은 순서대로 하는 경우 pk 값이 중복되는 문제가 있었습니다.
2. Sub Query에 있는 값이 변동되는 문제가 있었습니다.

이를 MySQL의 CTE와 order by 문을 통해 해결하였습니다.

 

CTE를 사용한 경우

WITH ids_to_update AS (
    SELECT tbb.id
    FROM table1 tbb
    WHERE tbb.id >= 0
)
UPDATE table1 tb
SET tb.id = tb.id + 3000
WHERE tb.id IN (SELECT id FROM ids_to_update)
order by id desc;

• with {cte_name} as ( select 문 ) 으로 작성합니다.
• update 쿼리 마지막에 order by id desc;를 통해 id 값이 큰 순서대로 변경하여 중복 값이 생성되는 것을 방지하였습니다.

 

CTE 알아보기

with cte(col1, col2) as (
	select 1,2
	union all
	select 3,4
)

위 처럼 컬럼 값도 설정할 수 있습니다.

 

 

참고자료

https://jjon.tistory.com/entry/MySQL-80-신기능-CTECommon-Table-Expression-활용

https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive