テーブルを複製して新しいテーブルを作成する(CREATE TABLE LIKE文、CREATE TABLE SELECT文)

広告
eyecatch

作成済みのテーブルを複製して新しいテーブルを作成する方法について解説します。

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する

最初に別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する方法です。次の書式を使用します。

CREATE TABLE new_tbl LIKE orig_tbl;

この書式ではテーブルの定義だけをコピーしてテーブルに格納されているデータはコピーされません。

ではコピー元のテーブルとして次のようなテーブルを作成しました。

create table address(id int auto_increment primary key, name varchar(10) not null, address varchar(20) default 'NoData');

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(1)

作成したテーブルを SHOW CREATE TABLE 文で確認してみます。

show create table address¥G

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(2)

確認のためにテーブルにデータを 1 つ追加します。

insert into address (name, address) values('Yamada', 'Tokyo');

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(3)

それでは address テーブルのカラムやインデックスの定義をコピーして新しいテーブル newaddress テーブルを作成します。次のように実行してください。

create table newaddress like address;

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(4)

テーブルが作成されました。それでは作成したテーブルを SHOW CREATE TABLE 文で確認してみます。

show create table newaddress¥G

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(5)

コピー元のテーブルとコピーしたテーブルで比較してみます。

addressテーブル
Create Table: CREATE TABLE `address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `address` varchar(20) DEFAULT 'NoData',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

newaddressテーブル
Create Table: CREATE TABLE `newaddress` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `address` varchar(20) DEFAULT 'NoData',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

カラムの定義や設定した制約、作成したインデックスまですべてコピーされていることが確認できます。

なおテーブルに格納したデータはコピーされていません。新しく作成したテーブルにはデータが何も入っていません。

select * from newaddress;

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(6)

別のテーブルの指定したカラムの定義とデータから新しいテーブルを作成する

次に別のテーブルに含まれる一部のカラムの定義とデータをコピーして新しいテーブルを作成する方法です。次の書式を使用します。

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

SELECT 文で指定したカラムの定義とデータをコピーします。この書式の場合、インデックスはコピーされません。

ではコピー元のテーブルとして次のようなテーブルを作成しました。

create table address(id int auto_increment primary key, name varchar(10) not null, address varchar(20) default 'NoData');

別のテーブルの指定したカラムの定義とデータから新しいテーブルを作成する(1)

作成したテーブルを SHOW CREATE TABLE 文で確認してみます。

show create table address¥G

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(2)

確認のためにテーブルにデータをいくつか追加します。

insert into address (name, address) values('Yamada', 'Tokyo');
insert into address (name, address) values('Suzuki', 'Osaka');
insert into address (name, address) values('Hanada', 'Tokyo');
insert into address (name, address) values('Endou', 'Kyoto');

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(3)

-- --

それでは最初に address テーブルのすべてのカラムを取得して新しいテーブル newaddress テーブルを作成します。次のように実行してください。

create table newaddress select * from address;

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(4)

テーブルが作成されました。それでは作成したテーブルを SHOW CREATE TABLE 文で確認してみます。

show create table newaddress¥G

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(5)

コピー元のテーブルとコピーしたテーブルで比較してみます。

addressテーブル
Create Table: CREATE TABLE `address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `address` varchar(20) DEFAULT 'NoData',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

newaddressテーブル
Create Table: CREATE TABLE `newaddress` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(10) NOT NULL,
  `address` varchar(20) DEFAULT 'NoData'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

コピー元で設定されていた PRIMARY KEY 制約がコピー先では設定されておらず、また コピー元で id カラムに設定されていた AUTO_INCREMENT がコピー先では DEFAULT '0' に置き換わっています。このようにこの書式を用いた場合にはインデックスはコピーされず、 AUTO_INCREMENT など一部の設定もコピーされません。

テーブルに格納されていたデータは新しいテーブルにコピーされています。

select * from newaddress;

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(6)

-- --

次に address テーブルの id カラムと name カラム取得して新しいテーブル newaddress テーブルを作成します。この時、新しいテーブルでは別のカラム company を追加し、さらに address カラムからコピーした id カラムに対して PRIMARY KEY 制約を設定してみます。次のように実行してください。

create table companyaddress (company varchar(10), primary key (id)) select id, name from address;

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(7)

テーブルが作成されました。それでは作成したテーブルを SHOW CREATE TABLE 文で確認してみます。

show create table companyaddress¥G

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(8)

新しいテーブルが、新しいテーブル側で作成した company カラムと、 address カラムからコピーされた id カラムと name カラムの3つのカラムで作成されていることが確認できました。また id カラムにはあらためて PRIMARY KEY が設定されています。

テーブルに格納されていたデータを取得してみます。

select * from companyaddress;

別のテーブルのカラムやインデックスの定義をコピーして新しいテーブルを作成する(9)

id カラムと name カラムに関しては address テーブルからコピーされています。新しく追加した compnay カラムにはデータの数だけデフォルト値の NULL が格納されています。

-- --

作成済みのテーブルを複製して新しいテーブルを作成する方法について解説しました。

MySQLの使い方の他の記事を見てみる

( Written by Tatsuo Ikura )

関連記事 (一部広告含む)
Profile
profile_img

著者 / TATSUO IKURA

初心者~中級者の方を対象としたプログラミング方法や開発環境の構築の解説を行うサイトの運営を行っています。