MySQL 에서 table 복제시 primary key/Unique 및 index 보존하기

Owned by 정광섭
Last updated: 2013-Dec-08
2 min read

개요

Oracle 에서는 create table as select 구문을 실행하면 원래 테이블과 똑같은 스키마와 constraint(기본키, 외래키, 인덱스등)를 보존하지만 mysql은 이 정보가 유지되지 않는다.

 

다음과 같은 스키마가 있을 경우

CREATE TABLE test (
	a int AUTO_INCREMENT PRIMARY KEY,
	b varchar(100) unique ,
	c varchar(50) 
	);
create index idx_test_c on test(c);
mysql> desc test;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| a     | int(11)      | NO   | PRI | NULL    | auto_increment |
| b     | varchar(100) | YES  | UNI | NULL    |                |
| c     | varchar(50)  | YES  | MUL | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

 

create table as select 로 테이블 생성하면 아래처럼 Key 항목이 비어 있다.

mysql> create table t2 as select * from test;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc t2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | int(11)      | NO   |     | 0       |       |
| b     | varchar(100) | YES  |     | NULL    |       |
| c     | varchar(50)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>

해결방법

테이블 복제시 오라클처럼 똑같이 만드려면 Create table table_name like org_table 을 사용하면 된다.

mysql> create table t3 like test;
Query OK, 0 rows affected (0.06 sec)
mysql> desc t3;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| a     | int(11)      | NO   | PRI | NULL    | auto_increment |
| b     | varchar(100) | YES  | UNI | NULL    |                |
| c     | varchar(50)  | YES  | MUL | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

테이블 내용까지 복제가 필요하면 INSERT INTO 문으로 처리를 하면 된다.

INSERT INTO `t3` SELECT * FROM `test`;

 

Ref