MySQLの「CREATE TABLE .. SELECT/LIKE」の挙動の違い

テーブルの複製のとき、お世話になります。

ちょっとしたバックアップ目的や、検証作業用にテーブルを複製するシーンはよくあります。
MySQLでは、テーブルの複製のためのコマンドが大きく2種類あるので、その違いを確かめた結果のお話です。

CREATE TABLE 複製先テーブル SELECT .. FROM 複製元テーブル
CREATE TABLE 複製先テーブル LIKE 複製元テーブル

MySQL 5.6.38で動かしていますが、バージョンにはあまり依存しない挙動となっているはずです。

一覧表

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17 CREATE TABLE 構文

あたりを読めば文章で書いてある話なのですが、ざくっと一覧化します。

コマンド	列定義省略	列定義変更	KEY継承	AutoIncrement定義継承	ストレージエンジン継承	データ継承
CREATE TABLE .. SELECT	可	可	不可	不可	不可（指定可）	可
CREATE TABLE .. LIKE	可	不可	可	可	可	不可

列定義関係

SELECT

省略すればSELECTで引っ張ってきた列定義をそのまま使うし、明記することで、複製元とは異なる定義を使うこともできます。
ただし、複製元に定義されていたAutoIncrement定義やKEY（およびIndex）は継承されません。

LIKE

複製元の構造そのまま複製するコマンドなので、異なる定義は使えません。
列そのものの増減や型指定の変更は、複製後にALTERする必要があります。

そのかわり、AutoIncrement定義やKEY（およびIndex）も継承されます。

ストレージエンジン

SELECT

省略すると、システム変数「default_storage_engine」に指定されているストレージエンジンでテーブルを作ります。
複製元とは異なるストレージエンジンを指定することも可能です。
列定義は省略しつつ、ストレージエンジンだけを指定することも可能です。（さっき知った）

LIKE

複製元と同じストレージエンジンでテーブルを作ります。
複製元と異なるストレージエンジンを指定することはできません。必要であれば後でALTERで対応します。

データ

SELECT

テーブル作成と同時にデータも複製されます。
というより、SELECT文で好きなように加工、WHEREによる絞込等、柔軟な制御が可能です。

Oracle脳の人は、テーブル定義だけ複製しようとする時に、「WHERE 1<>1」とか使いますね。

LIKE

データは複製されません。あくまで構造だけを複製します。

実験結果

「default_storage_engine=InnoDB」な環境に、MyISAMな複製元テーブルを作って、

hoge_select：CREATE TABLE .. SELECTで複製
hoge_like：CREATE TABLE .. LIKEで複製
hoge_select_engine：CREATE TABLE .. SELECTかつENGINE変更

の3ターンを実行した時の、定義とデータの出来上がり方が確認できます。

mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `fuga` (
    ->   `id` INT(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO fuga values ();
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM fuga;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> CREATE TABLE hoge_select SELECT * FROM fuga;
Query OK, 1 row affected (0.12 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE hoge_select\G
*************************** 1. row ***************************
       Table: hoge_select
Create Table: CREATE TABLE `hoge_select` (
  `id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> SELECT * FROM hoge_select;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> 
mysql> CREATE TABLE hoge_like LIKE fuga;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE hoge_like\G
*************************** 1. row ***************************
       Table: hoge_like
Create Table: CREATE TABLE `hoge_like` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> SELECT * FROM hoge_like;
Empty set (0.00 sec)

mysql> CREATE TABLE hoge_select_engine ENGINE=MyISAM SELECT * FROM fuga;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE hoge_select_engine\G
*************************** 1. row ***************************
       Table: hoge_select_engine
Create Table: CREATE TABLE `hoge_select_engine` (
  `id` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> SELECT * FROM hoge_select_engine;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)