データベースを利用しているとバックアップを取る際や、既存テーブルと同じ構成のテーブルを作成したいなどの理由で、テーブルをコピーしたいケースに遭遇します。
今回はMySQLで既存テーブルを新規テーブルとしてコピーする方法について、ご紹介していきたいと思います。
テーブルから定義だけをコピーするSQLコマンド
最初にご紹介するのは、テーブルから定義情報だけをコピーした新しいテーブルを作成するSQLコマンドです。
既存のテーブルに格納されているデータについてはコピーされません。
コピー用のテーブル作成
まず今回サンプルとしてコピーするためのテーブルを作成します。
シンプルな「user」という名前のテーブルを作成しましょう。
CREATE TABLE user(id int primary key, name varchar(30) not null, age int default 0);
作成したテーブルの定義は下記のコマンドで確認可能です。
show create table user \G
確認コマンドで末尾に「;」を付けるとエラーが表示されます。
上記コマンドでは、末尾に「;」を付ける必要はありません。
実行すると下記のようにテーブル情報が表示されます。
Table: user Create Table: CREATE TABLE `user` ( `id` int NOT NULL, `name` varchar(30) NOT NULL, `age` int DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
データ投入
テーブル定義だけをコピー出来ているか確認するために、いくつかのデータを投入しておきたいと思います。
今回は下記のデータを格納しました。
+----+-----------------+------+ | id | name | age | +----+-----------------+------+ | 1 | 山田太郎 | 30 | | 2 | 山田花子 | 25 | | 3 | 鈴木じろう | 20 | +----+-----------------+------+
あくまでサンプルですので、投入するデータはなんでも構いません。
テーブル定義コピーコマンド
では実際にテーブルの定義のみをコピーして新しいテーブルを作成してみましょう。
基本構文は下記の通りです。
CREATE TABLE 新しいテーブル名 LIKE コピー元のテーブル名;
実際に新しいテーブル名を「user_copy」という名前で作成してみたいと思います。
CREATE TABLE user_copy LIKE user;
上記でご紹介した「SHOW」コマンドを利用してテーブル定義を確認してみましょう。
Table: user_copy Create Table: CREATE TABLE `user_copy` ( `id` int NOT NULL, `name` varchar(30) NOT NULL, `age` int DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec)
「user」テーブルと全く同じ定義で作成されています。
ちなみにselect文でデータを確認してみると下記のように空の状態が確認できます。
Empty set (0.00 sec)
テーブルのデータを丸ごと新規テーブルにコピーするSQLコマンド
続いてテーブルに格納されたデータを丸ごとコピーした状態で、新規テーブルを作成する方法についてご紹介したいと思います。
コピー元テーブルには、上記で作成した「user」テーブルを引き続き利用していきます。
テーブルコピー(データ含)
データを含んで新規テーブルにテーブルをコピーするには「SELECT」コマンドを利用します。
基本構文は下記の通りです。
CREATE TABLE 新しいテーブル名 SELECT * FROM コピー元のテーブル名;
今回は新しいテーブル名を「user_copy2」としてコマンドを実行してみたいと思います。
CREATE TABLE user_copy2 SELECT * FROM user;
実行した結果をSELECT文で確認すると、データが丸ごと新規テーブルで格納されていることがわかります。
+----+-----------------+------+ | id | name | age | +----+-----------------+------+ | 1 | 山田太郎 | 30 | | 2 | 山田花子 | 25 | | 3 | 鈴木じろう | 20 | +----+-----------------+------+
同じくSHOWコマンドでテーブル定義についても確認しておきましょう。
Table: user_copy2 Create Table: CREATE TABLE `user_copy2` ( `id` int NOT NULL, `name` varchar(30) NOT NULL, `age` int DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
コピー元と同じテーブル名とデータ型でテーブルが作成されていますね。
PRIMARYキーなど引き継がれない定義も存在するため、完全に同じテーブル定義としてコピーされるわけではありません。
テーブル構造をそのままコピーしたい場合には、最初にご紹介したLIKEを利用したコマンドでテーブルをコピーし、データは別途インサートする必要があります。
特定のカラムだけでコピーすることも可能
コピー元のテーブルから、必要なカラムだけを抽出した新規テーブルを作成することも可能です。
CREATE TABLE 新しいテーブル名 SELECT カラム名(, カラム名, ...) FROM コピー元のテーブル名;
今回はサンプルからidとnameのカラムだけに絞った新規テーブルを作成してみましょう。
CREATE TABLE user_copy3 SELECT id, name FROM user;
実行した後、SELECT文で内容を確認すると下記のように表示されます。
+----+-----------------+ | id | name | +----+-----------------+ | 1 | 山田太郎 | | 2 | 山田花子 | | 3 | 鈴木じろう | +----+-----------------+
テーブル定義についても確認してみましょう。
Table: user_copy3 Create Table: CREATE TABLE `user_copy3` ( `id` int NOT NULL, `name` varchar(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
指定したカラムの情報だけがコピーされていますね。
さいごに：SQLでのテーブルコピーは意外とよく利用される
本記事では、SQLでテーブルをコピーする方法についてご紹介してきました。
上述したように、大きく2種類の方法が存在し、どちらもテーブルをコピーすることに変わりありませんが、内容が若干異なります。
ご自身の利用用途に合わせて、適切なSQLのテーブルコピーコマンドを活用してみてください。
ご紹介する内容はMySQLで利用可能なコマンドです。
データベース毎に利用出来るコマンドが異なりますので、適宜読み替えてお試しください。