MySQLのcreate tableについて、サンプルSQLを紹介しながらまとめています。
以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。
MySQLのcreate table構文
MySQLのcreate table構文は3種類あります。
- create table テーブル名 (テーブル定義) …
- create table テーブル名 select文
- create table テーブル名 like 元になるテーブル名
【関連記事】
▶SQL create tableでテーブル生成 DBMSごとに差異あり
create table テーブル名 (テーブル定義) …
カラムごとに、カラム名、型、NULLの許可などを定義します。
プライマリキーなどキー指定も可能です。プライマリキー以外のインデックスは、別途alter tableで指定する必要があります。
テーブル構造のみ作り、データは空の状態になります。
下記SQLは、employees（社員）テーブルの構造とプライマリキーが全く同じのemployees_copyテーブルを生成します。
CREATE TABLE `employees_copy` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (emp_no)
)
phpMyAdminで確認すると、テーブル構造とプライマリキー、プライマリキーのインデックスが設定されていることが確認できます。
create table テーブル名 select文
select文で取得したデータを、新しいテーブルとして生成します。カラム名と型は生成されますが、プライマリキーやインデックスは生成されません。
下記SQLは、employees（社員）テーブルのカラム名と型が同じemployees_copy2テーブルを生成します。プライマリキーは設定されません。
create table employees_copy2 select * from employees;
なお、この構文は、上記の「create table テーブル名 (テーブル定義) …」と組み合わせることで、テーブル構造やプライマリキーを定義した上でデータをコピーすることも可能です。
下記のSQLは、employees（社員）テーブルと同じ構造とプライマリキーでemployees_copy3テーブルを生成し、さらにemployeesテーブルからデータをコピーします。
CREATE TABLE `employees_copy3` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (emp_no)
)
select * from employees;
phpMyAdminで確認すると、プライマリキーが設定されておらず、インデックスも未設定なことが確認できます。
ただし、データは全てコピーされています。
create table テーブル名 like 元になるテーブル名
create table テーブル名 like…は、コピー元のテーブル構造やプライマリキー、インデックスをそっくり複製します。データはコピーされず、空の状態になります。
下記SQLは、employees（社員）テーブルの構造、キー、インデックスを複製して新テーブルemployees_copy4を生成します。
create table employees_copy4 like employees;
phpMyAdminで確認すると、プライマリキーが設定されていて、プライマリキーを含む全てのインデックスが設定されていることが確認できます。
ただし、データはコピーされていません。
続けて、以下のinsert文を実行することで、employeesテーブルからデータをコピーすることが可能です。
insert into employees_copy4 select * from employees
MySQL create tableの使い方
IF NOT EXISTSでテーブル存在チェック
テーブルが存在する状態でcreate tableを実行するとエラーとなります。
create table employees_copy2 select * from employees;
例えば、employees_copy2テーブルが存在する状態で、上記SQLを実行すると、「#1050 – Table ‘employees_copy2’ already exists」エラーが出力されます。
このエラーを回避するためには、IF NOT EXISTSをSQLに追加して実行します。
create table IF NOT EXISTS employees_copy2 select * from employees;
上記SQLは、employees_copy2テーブルが存在する場合には、何もおこなわず、エラーも出力せずに終了します。
一時テーブルの注意点
以下のように、create tableにTEMPORARYを含めると、一時テーブルを作成することが可能です。
create TEMPORARY table employees_tmp select * from employees;
一時テーブルは、セッションが終了するとともに自動的に削除されます。使い方によっては便利な一時テーブルですが、注意点が２つあります。
一つは、create temporary tableが発行されたタイミングで、開いていたトランザクションは自動的にコミットされない点。
もう一つは、データベース自体が削除されても、自動的に一時テーブルが削除されない点です。データベースをドロップしたのに一時テーブルのみ残っているという状況が起こります。
既存テーブルから、create table文を生成するには
既存テーブルから、カラム名、型、ルールなどを抽出してcreate table文を生成するには、show create tableを実行します。
show create table employees
上記のSQLを実行すると、employees（社員）テーブルのcreate table文を表示します。
phpMyAdminのエクスポート機能で出力させる方法もあります。
phpMyAdminで、テーブル名をクリックして選択し、「エクスポート」タブをクリックします。
フォーマットを「SQL」に指定して、実行をクリックすると、データがSQLとして出力されます。テキストエディタでファイルを開くと、「テーブルの構造」の部分にcreate table文が生成されてます。
phpMyAdminで複数テーブルを選択すると、選択したテーブルのcreate table文が出力されます。
まとめ
- MySQLのcreate table構文は3種類ある
- create table … selectはデータをコピーできるが、プライマリキーやインデックス情報はコピーできない
- create table … likeは、テーブル構造をコピーできるが、データ自体はコピーできない
- なるべく簡単にテーブル構造とデータをコピーするには、create table … likeとinsert into … selectを組み合わせる。
- IF NOT EXISTSでテーブル重複エラーを回避できる
- phpMyAdminのエクスポート機能で、create table文を生成できる
