서론
MySQL에서 데이터베이스를 작업을 할 때,
기존 테이블 구조를 그대로 활용하여 새로운 테이블을 만들어야 하는 상황이 빈번히 발생한다.
예를 들어, 대량의 데이터를 담고 있는 테이블에서 특정 조건에 맞는 데이터만 추출해서 새로운 테이블에 저장해야 한다거나
임시 테이블을 생성하여 테스트 환경을 구축해야 하는 경우 등이 있다.
설마 이런 상황에서 테이블을 직접 생성하여 컬럼의 이름과 타입을 똑같이 설정하고 있진 않은가?
나 역시도 그랬었다.
회사 데이터베이스의 테이블에 새로운 데이터를 추가하는 과정에서
임시 테이블을 생성해두어야 하는데 관련 테이블이 대략 10개 가량이 되었다.
처음에는 테이블 생성 스크립트를 뽑아 테이블을 새로 생성하는 방식으로 진행했었다.
테이블 생성 스크립트를 뽑고, 테이블을 생성하고…
또 다시 테이블 생성 스크립트를 뽑고 생성하고…
한 두 번은 괜찮지만, 이런 작업이 반복될수록 작업 효율이 너무 떨어진다는 것을 체감하게 된다.
문득 이런 생각을 하게 된다.
“조금 더 편하게 테이블을 복사하는 방법이 없을까?“
이번 포스팅에서는 쿼리를 이용하여 간편하게 테이블의 구조 및 데이터를 복사하는 방법에 대해 다룬다.
테이블 복사하기
테이블을 복사하는 방법에는 여러 가지가 존재한다.
CREATE TABLE LIKE: 테이블의 구조와 속성 복사
설명
CREATE TABLE ... LIKE
구문은 기존 테이블의 구조를 기반으로 새로운 테이블을 생성하는 데 사용된다.
즉, 데이터를 제외한 테이블 스키마(열 이름, 데이터 유형, 인덱스 등)를 그대로 복사하여 새로운 테이블을 만들 수 있다.
단, 복사하여 새로 생성된 테이블과 기존 테이블과의 독립성을 유지하기 위해 외래키는 복사되지 않는다.
기본 구문
기본적인 구문은 다음과 같다.
/* 테이블 구조와 속성 복사 */
CREATE TABLE 새 테이블 이름 LIKE 기존 테이블 이름;사용 예시
다음과 같은 예시 테이블(cart)이 있다.
- 기본키
- cart_id
- 외래키
- product_id
- user_id
- 인덱스
- product_id
- user_id
cart 테이블의 구조와 속성을 복사해 보자.
CREATE TABLE cart2 LIKE cart;아래의 이미지를 참고하여 테이블이 어떻게 복사되었는지 확인해 보자.
컬럼과 데이터타입의 구조, 기본키, 인덱스가 정상적으로 복사되었다.
또한, 외래키와 테이블의 데이터는 는 복사가 되지 않은 것을 확인할 수 있다.
데이터까지 복사하고 싶다면? INSERT INTO SELECT 활용!
CREATE TABLE ... LIKE
구문은 테이블 구조만 복사하고 데이터는 복사하지 않는다.
만약 데이터까지 복사하고 싶다면
INSERT INTO ... SELECT
구문을 함께 사용하여야 한다.
INSERT INTO 복사한 테이블 SELECT * FROM 기존 테이블
/* 사용 예시 */
-- 테이블 구조 복사
CREATE TABLE cart2 LIKE cart;
-- 데이터 복사
INSERT INTO cart2 SELECT * FROM cart;이렇게 하면 기존 테이블의 모든 데이터가 복사한 테이블에 복사된다.
물론,
WHERE
절을 사용하여 특정 조건에 맞는 데이터만 선택적으로 복사할 수도 있다.
활용 예시
- 테스트 환경 구축: 실제 데이터를 사용하지 않고 테스트 환경을 구축할 때 유용하다.
- 데이터 분석: 특정 조건에 맞는 데이터만 추출하여 분석할 때 유용하다.
- 데이터 백업: 기존 테이블의 백업 테이블을 생성할 때 유용하다.
✅ 알아두기
- 해당 구문은 테이블의 구조와 외래키를 제외한 속성만 복사한다.
- 테이블의 데이터를 포함하지 않는다.
CREATE TABLE AS SELECT: 테이블 구조와 데이터 복사
설명
CREATE TABLE ... AS SELECT
구문은 기존 테이블의 구조와 데이터를 기반으로 새로운 테이블을 생성한다.
이 명령은 데이터뿐만 아니라 데이터 타입도 복사한다.
하지만 인덱스, 기본 키, 외래 키와 같은 테이블의 제약 조건은 복사하지 않는다.
기본 구문
기본적인 구문은 다음과 같다.
CREATE TABLE 새 테이블 이름
AS
SELECT * FROM 기존 테이블 이름;사용 예시
CREATE TABLE ... LIKE
구문에서 사용한 예시 테이블(cart)을 이용하여 복사해 보자.
- 기본키
- cart_id
- 외래키
- product_id
- user_id
- 인덱스
- product_id
- user_id
cart 테이블의 구조와 데이터를 복사해 보자.
CREATE TABLE cart3
AS
SELECT * FROM cart;이번에도 아래의 이미지를 참고하여 테이블이 어떻게 복사되었는지 확인해 보자.
컬럼과 데이터타입의 구조 그리고 데이터는 정상적으로 복사되었다.
하지만 테이블의 속성(제약 조건)은 복사가 되지 않은 것을 확인할 수 있다.
활용 예시
특정 열만 복사하기
CREATE TABLE new_table
AS
SELECT column1, column2 FROM existing_table;조건에 맞는 데이터만 복사하기
CREATE TABLE new_table
AS
SELECT * FROM existing_table WHERE condition;데이터 백업 테이블 생성하기
CREATE TABLE customers_backup_{DATE}
AS
SELECT * FROM customers;테이블 구조 변경하기
기존 테이블의 구조를 변경하고 싶지만 데이터는 유지하고 싶을 때,
CREATE TABLE ... AS SELECT
쿼리를 사용하여 새로운 구조의 테이블을 생성하고 데이터를 복사한 후,
기존 테이블을 삭제하고 새로운 테이블의 이름을 변경할 수 있다.
✅ 알아두기
- 해당 구문은 테이블의 구조와 데이터를 복사한다.
- 테이블의 속성(제약 조건)은 포함하지 않는다.