mysql 테이블 복사하는 방법 CTAS 등

mysql 에서 table 을 복사하는 몇가지 방법 및 특징을 알아보자

1. CTAS 

 CREATE TABLE NEW_TAB  AS  SELECT * FROM OLD_TAB ;

 - 테이블 구조와 모든 데이터를 복사

 - 인덱스, 기본 키, 외래 키, auto_increment 속성 등은 복사되지 않음

 - charset, collate 는 default 속성을 따라감

2. LIKE 

 CREATE TABLE NEW_TAB LIKE OLD_TAB ;

- 원본 테이블의 구조(컬럼, 인덱스, 제약 조건 등)를 그대로 복사

- FK 제약조건은 복사가 되지 않는다. 따로 추가해야 함

SELECT CONCAT( 'ALTER TABLE `B`.`', TABLE_NAME, '` ADD CONSTRAINT `', CONSTRAINT_NAME, '` ', 'FOREIGN KEY (`', COLUMN_NAME, '`) ', 'REFERENCES `B`.`', REFERENCED_TABLE_NAME, '` (`', REFERENCED_COLUMN_NAME, '`);' ) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'A' AND REFERENCED_TABLE_SCHEMA IS NOT NULL;

3. SHOW CREATE TABLE 

 SHOW CREATE TABLE OLD_TAB; => DDL 추출해서 생성

4. 덤프로 복사

테이블이 여러개이면 dump& load 가 최고. 테이블 구조, 데이터, 인덱스, 외래 키 제약 조건이 복사된다.

mysqldump -u root -p A > A_schema_backup.sql

CREATE DATABASE B;

mysql -u root -p B < A_schema_backup.sql