Mysql Table 속성 복제하기

기존 Table의 속성과 동일한 Table을 사용하기 위해 여러가지 방법으로 생성할 수 있다.

그중 Mysql 에서는 create table [table_name_new] like [table_name] 와 같은 방법으로

테이블과 인덱스까지 한번에 생성할 수 있는 기능이 있다.

 

1. 기존 Table 스크립트 추출

     show create 구문으로 소스를 뽑아서 Table 명을 변경하여 만드는 방법이 있지만

    배치같은 프로그램상에서는 신규 테이블을 생성할 수 없다.

root@localhost:employees 09:05:15>show create table employees ;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                            |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) COLLATE utf8mb4_general_ci NOT NULL,
  `last_name` varchar(16) COLLATE utf8mb4_general_ci NOT NULL,
  `gender` enum('M','F') COLLATE utf8mb4_general_ci NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost:employees 09:05:32>

 

2. CTAS 방식으로 테이블 껍데기만 생성이 가능하지만 Index생성을 별도로 해주거야 한다.

root@localhost:employees 09:04:11>create table employees_ctas as select * from employees where 1=2 ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost:employees 09:04:15>desc employees_ctas ;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   |     | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

root@localhost:employees 09:04:22>show index from employees_ctas ;
Empty set (0.01 sec)

root@localhost:employees 09:04:37>

 

3. Like를 통한 테이블 복제시 Table과 Index를 한번에 생성이 가능하므로 프로그램을 통한 임시 테이블 생성과 같은 작업에 유용하게 활용할 수 있다. 데이터는 들어가지 않고 껍데기만 생성이 된다.

root@localhost:(none) 08:58:58>use employees ;
Database changed
root@localhost:employees 08:59:04>desc employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

root@localhost:employees 08:59:12>show index from employees ;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees |          0 | PRIMARY  |            1 | emp_no      | A         |      299556 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

root@localhost:employees 08:59:38>select count(*) from employees ;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.09 sec)
root@localhost:employees 08:59:45>
root@localhost:employees 09:02:00>create table employees_imsi like employees ;
Query OK, 0 rows affected (0.05 sec)

root@localhost:employees 09:02:20>desc employees_imsi ;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

root@localhost:employees 09:02:29>select count(*) from employees_imsi;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

root@localhost:employees 09:02:36>show index from employees_imsi;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees_imsi |          0 | PRIMARY  |            1 | emp_no      | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

root@localhost:employees 09:02:42>