MySQLのcreate tableサンプルコード集 カラム定義を自動出力する方法は?
  • facebookページ
  • twitterページ
  • 2019.11.18

    MySQLのcreate tableサンプルコード集 カラム定義を自動出力する方法は?

    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テーブルを生成します。

    phpMyAdminで確認すると、テーブル構造とプライマリキー、プライマリキーのインデックスが設定されていることが確認できます。

    create table テーブル名 select文

    select文で取得したデータを、新しいテーブルとして生成します。カラム名と型は生成されますが、プライマリキーやインデックスは生成されません。

    下記SQLは、employees(社員)テーブルのカラム名と型が同じemployees_copy2テーブルを生成します。プライマリキーは設定されません。

    なお、この構文は、上記の「create table テーブル名 (テーブル定義) …」と組み合わせることで、テーブル構造やプライマリキーを定義した上でデータをコピーすることも可能です。

    下記のSQLは、employees(社員)テーブルと同じ構造とプライマリキーでemployees_copy3テーブルを生成し、さらにemployeesテーブルからデータをコピーします。

    phpMyAdminで確認すると、プライマリキーが設定されておらず、インデックスも未設定なことが確認できます。

    ただし、データは全てコピーされています。

    create table テーブル名 like 元になるテーブル名

    create table テーブル名 like…は、コピー元のテーブル構造やプライマリキー、インデックスをそっくり複製します。データはコピーされず、空の状態になります。

    下記SQLは、employees(社員)テーブルの構造、キー、インデックスを複製して新テーブルemployees_copy4を生成します。

    phpMyAdminで確認すると、プライマリキーが設定されていて、プライマリキーを含む全てのインデックスが設定されていることが確認できます。

    ただし、データはコピーされていません。

    続けて、以下のinsert文を実行することで、employeesテーブルからデータをコピーすることが可能です。

    MySQL create tableの使い方

    IF NOT EXISTSでテーブル存在チェック

    テーブルが存在する状態でcreate tableを実行するとエラーとなります。

    例えば、employees_copy2テーブルが存在する状態で、上記SQLを実行すると、「#1050 – Table ‘employees_copy2’ already exists」エラーが出力されます。

    このエラーを回避するためには、IF NOT EXISTSをSQLに追加して実行します。

    上記SQLは、employees_copy2テーブルが存在する場合には、何もおこなわず、エラーも出力せずに終了します。

    一時テーブルの注意点

    以下のように、create tableにTEMPORARYを含めると、一時テーブルを作成することが可能です。

    一時テーブルは、セッションが終了するとともに自動的に削除されます。使い方によっては便利な一時テーブルですが、注意点が2つあります。

    一つは、create temporary tableが発行されたタイミングで、開いていたトランザクションは自動的にコミットされない点。

    もう一つは、データベース自体が削除されても、自動的に一時テーブルが削除されない点です。データベースをドロップしたのに一時テーブルのみ残っているという状況が起こります。

    既存テーブルから、create table文を生成するには

    既存テーブルから、カラム名、型、ルールなどを抽出してcreate table文を生成するには、show create tableを実行します。

    上記の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文を生成できる


    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE