この記事は 2019年11月30日 に書かれたものです
MySQLでテーブルをコピーします。
以下の2つのパターンのそれぞれの特徴についてメモします。
パターン1
CREATE TABLE newtable LIKE oldtable;
INSERT INTO newtable SELECT * FROM oldtable;
パターン2
CREATE TABLE newtable SELECT * FROM oldtable;
目次
テーブルをコピー
MySQLでは大きく2つの方法があり、それぞれ特徴があります。
１つずつ試してみたいと思います。
今回はコピー元のテーブルとして以下のテーブルを用意しました。
/*-- コピー元のテーブル --*/
CREATE TABLE foo(
id int AUTO_INCREMENT,
name varchar(32),
PRIMARY KEY(id),
INDEX idx_name(name)
);
/*-- 初期データ --*/
INSERT INTO foo(name) values('Apple');
INSERT INTO foo(name) values('Banana');
CREATE TABLE … LIKE 構文
構文
結論から言うと以下のSQLで一発です。
CREATE TABLE newtable LIKE oldtable;
ただし、この方法だと新しい「箱」は用意されますが、箱の中は空っぽです。データも必要な場合は追加でINSERTしてやります。
INSERT INTO newtable SELECT * FROM oldtable;
試してみる
試しにテーブルを用意してコピーしてみます。
CREATE TABLE foo2 LIKE foo;
実際のテーブルの内容を確認してみます。
インデックス、プライマリーキーなども含めてまるっとテーブルの構造がコピー出来ているのが確認できます。
mysql> desc foo2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
mysql> show index from foo2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| foo2 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| foo2 | 1 | idx_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> select * from foo2;
Empty set (0.00 sec)
データを入れて確認します。AUTO_INCREMENTはリセットされるようですね。
mysql> INSERT INTO foo2 SELECT * FROM foo;
mysql> select * from foo2;
+----+--------+
| id | name |
+----+--------+
| 1 | Apple |
| 2 | Banana |
+----+--------+
AUTO_INCREMENTの値を設定したい場合は以下を参照ください。
CREATE TABLE … SELECT 構文
構文
パターン2です。
CREATE TABLE newtable SELECT * FROM oldtable;
こちらはデータも一緒にコピーされカラム名やデータ型などもそのまま移りますが、インデックス、プライマリーキー、デフォルト値やAUTO_INCREMENTなどはあとから別途設定する必要があります。
試してみる
試しにコピーしてみます。
/*-- コピー --*/
CREATE TABLE foo3 SELECT * FROM foo;
コピーされた新しいテーブルの様子を確認します。
> select * from foo3;
+----+--------+
| id | name |
+----+--------+
| 1 | Apple |
| 2 | Banana |
+----+--------+
mysql> desc foo;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
mysql> desc foo3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> show index from foo3;
Empty set (0.00 sec)
参考ページ
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17 CREATE TABLE 構文
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17.1 CREATE TABLE … SELECT 構文
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5.1 INSERT … SELECT 構文
このブログを応援する
お寄せいただいたお気持ちは全額サーバ代や次の記事を執筆するための原資として活用させていただいております。この記事が参考になった場合などぜひご検討ください。
