1. データを含めてテーブルをコピー

テーブルのデータと構造をコピーする方法

データを含めてテーブルをコピー

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

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

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

    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)