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

MySQL では CREATE TABLE LIKE 文または CREATE TABLE SELECT 文を使用することで既存のテーブルを複製して新しいテーブルを作成することができます。ここでは MySQL で作成済みのテーブルを複製して新しいテーブルを作成する方法について解説します。

(Last modified: 2023年01月07日)

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

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

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

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)