MySQL テーブルのコピー

データを含めてテーブルをコピーする場合、インデックスやプライマリーキーも含めてコピーか、インデックスやプライマリーキーはコピーしない２つの方法があります。

• インデックスやプライマリーキーもコピー

インデックスやプライマリーキーも含めてコピーする場合、２つのコマンドを使用します。 最初にインデックスやプライマリーキーも含めた空のコピーテーブルを作成し、そのあとにデータをコピー元からインサートします。

CREATE TABLE [コピー先テーブル名] LIKE [コピー元テーブル名]

INSERT INTO [コピー先テーブル名] SELECT * FROM [コピー元テーブル名]

コピー先のテーブルが存在しない場合だけコピーしたい場合は、テーブル作成は以下を使います。

CREATE TABLE IF NOT EXISTS [コピー先テーブル名] LIKE [コピー元テーブル名]

例　item_countテーブルをコピーして、item_count_oldテーブルを作成します。

mysql> CREATE TABLE item_count_old LIKE item_count;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO item_count_old SELECT * FROM item_count;
Query OK, 16256 rows affected (0.14 sec)
Records: 16256  Duplicates: 0  Warnings: 0

コピー先を確認すると、プライマリーキーやauto_incrementがコピーされています。

mysql> desc item_count_old;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| item_id    | int(11)     | NO   | MUL | NULL    |                |
| carrier_id | smallint(6) | NO   |     | 1       |                |
| count      | int(11)     | NO   |     | 0       |                |
| created    | datetime    | NO   |     | NULL    |                |
| updated    | datetime    | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

インデックスもコピーされています。

mysql> show index from item_count_old;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| item_count_old |          0 | PRIMARY  |            1 | id          | A         |       15549 |     NULL | NULL   |      | BTREE      |         |               |
| item_count_old |          1 | item_id  |            1 | item_id     | A         |       15549 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

auto_incrementが、コピー元と同じ値の16258 になっています

mysql> show table status like 'item_count_old';
+----------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| Name           | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| item_count_old | InnoDB |      10 | Compact    | 15424 |            103 |     1589248 |               0 |       262144 |   7340032 |          16258 | 2018-06-02 23:06:06 | NULL        | NULL       | utf8_bin  |     NULL |                |         |
+----------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set (0.01 sec)

• インデックスやプライマリーキーはコピーしない

データのバックアップなどを目的として、インデックスやプライマリーキーをコピーしない場合、以下のコマンドを使用します。

CREATE TABLE [コピー先テーブル名] AS SELECT * FROM [コピー元テーブル名]

コピー先のテーブルが存在しない場合だけコピーしたい場合は、以下を使います。

CREATE TABLE IF NOT EXISTS [コピー先テーブル名] AS SELECT * FROM [コピー元テーブル名]

例　item_countテーブルをコピーして、item_count_oldテーブルを作成します。

mysql> CREATE TABLE item_count_test AS SELECT * FROM item_count;
Query OK, 16256 rows affected (0.09 sec)
Records: 16256  Duplicates: 0  Warnings: 0

コピー元では、カラムidにプライマリーキーとauto_incrementが設定されていましたが、コピー先には設定されていません。

mysql> desc item_count_test;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | NO   |     | 0       |       |
| item_id    | int(11)     | NO   |     | NULL    |       |
| carrier_id | smallint(6) | NO   |     | 1       |       |
| count      | int(11)     | NO   |     | 0       |       |
| created    | datetime    | NO   |     | NULL    |       |
| updated    | datetime    | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

コピー元では、インデックスがidとitem_idカラムに設定されていましたが、コピー先はインデックスがありません。

mysql> show index from item_count_test;
Empty set (0.00 sec)