MySQL에서 테이블 복사하는 방법에 대해 알아보기

안녕하세요 DA사업본부 송영진입니다.

오늘은 MySQL에서 테이블을 복사하는 방법과 주의할 점에 대한 포스트를 써보려고 합니다.

테이블을 복사하는 방법은 보편적으로 두 가지 방법을 사용합니다.

• SELECT를 사용한 데이터 복사만으로 테이블 생성
• LIKE를 사용하여 같은 구조의 테이블 생성 및 데이터 INSERT

다음과 같은 테이블을 이용하여 어떤 차이점이 있는지 실제로 쿼리를 실행해보며 알아보겠습니다.

테이블 정의는 다음과 같습니다.

CREATE TABLE test 
(
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    primary key(id)
);

AUTO_INCREAMENT 설정이 되어있는 테이블에 데이터가 5건 들어있으니 현재의 AUTO_INCREMENT의 값은 6이 됩니다. 다음과 같은 쿼리로 확인이 가능합니다.

SELECT 
    AUTO_INCREMENT 
FROM 
    information_schema.tables 
WHERE 
    TABLE_NAME = 'test';

데이터 복사로 테이블 생성

자 이제 SELECT를 사용한 데이터 복사만으로 테이블 생성 방법을 사용하여 테이블을 만들어보고 테이블의 정의가 어떻게 되었는지 확인해보겠습니다.

사용할 SQL은 다음과 같습니다.

CREATE TABLE test2 AS SELECT * FROM test;

확인으로 사용하는 SQL은 다양한 방법이 있습니다. SHOW CREATE TABLE을 사용하는 방법, DESC를 사용하는 방법입니다. 결과를 확인해보겠습니다.

그렇습니다 PRIMARY KEY와 AUTO_INCREMENT가 사라져버렸습니다... 테이블의 SELECT한 결과로 만들었다보니까 당연하긴 한 결과인데 이렇게 만든 테이블은 백업으로서 사용하기는 적당하지 않습니다.

그렇다면 다른 방법은 어떨까요?

같은 구조의 테이블 생성 및 데이터 삽입

이번엔 LIKE를 사용하여 같은 구조의 테이블 생성 및 데이터 INSERT를 사용하여 테이블을 생성해보겠습니다.

사용할 SQL은 다음과 같습니다.

CREATE TABLE test3 LIKE test
INSERT INTO test3 SELECT * FROM test;

다시 생성한 테이블을 확인해보겠습니다.

PRIMARY KEY 정보와 AUTO_INCREMENT가 살아있습니다!!

테이블을 복사해서 AUTO_INCREMENT가 똑같이 6이 되어있는 것을 확인했는데요, 이 때 6이 되는 시점은 언제일까요? 테이블을 생성했을 때 오리지널 테이블의 값을 그대로 가져오나? 아니면 데이터를 삽입하는 시점에 증가하는 걸까요? 다시 테이블을 생성하면서 중간중간 AUTO_INCREMENT의 값을 다시 확인해보겠습니다.

테이블이 생성된 시점에서는 1로 초기화되고 데이터 5건이 삽입되었을 때 AUTO_INCREMENT의 값이 5 증가하게 되네요. 따라서 다음에 삽입되는 데이터의 AUTO_INCREMENT의 값은 6이 되겠습니다!

마지막으로

MySQL에서 테이블을 복사하는 두 가지 방법에 대해서 알아보았습니다. 복사한 테이블을 백업으로서 사용하고 싶은 경우에는 테이블 구조까지 같이 복사할 필요가 있으므로 LIKE를 사용하여 복사하는 편이 좋을 것 같습니다. 사실 이번 블로그는 제가 테이블을 데이터 복사로 생성한 테이블을 백업으로 쓰다가 기본키가와 AUTO_INCREMENT가 사라져있어서 문제가 있었기 때문에 적은 블로그입니다.

딱히 MySQL에서만 적용되는 이야기만은 아니라 다양한 데이터베이스에서 같은 케이스가 있을 것 같네요. 다른 분들께도 도움이 되었으면 좋겠습니다!