MySQLのカラム追加 サンプルコード集 一括で複数追加する方法は?
  • facebookページ
  • twitterページ
  • 2019.11.18

    MySQLのカラム追加 サンプルコード集 一括で複数追加する方法は?

    MySQLのテーブルカラム追加の方法について、サンプルSQLを紹介しながらまとめています。

    以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

    MySQLのカラム追加はALTER TABLE

    MySQLのカラム追加(ALTER TABLE ADD)の構文は以下の通りです。

    employeesテーブルのコピーを作って、カラム追加を試してみましょう。

    上記SQLは、employees(社員)テーブルの構造のみコピーして、employees_copyテーブルを作成します。

    employees_copyテーブルに、dept_no(部署コード、char(4))と、dept_name(部署名、varrchar(40))のカラムを追加してみます。それぞれNULLは禁止(NOT NULL)とします。

    まずは、dept_noを追加します。

    続いて、dept_nameを追加します。

    show columnsを実行して、employees_copyのカラム構成を確認してみましょう。

    結果はこうなります。

    テーブルの末尾に、dept_noとdept_nameが追加されました。

    追加時のカラム位置の指定方法

    AFTERまたはFIRSTを指定することで、カラムを指定の位置に追加できます。

    dept_noカラム追加のSQLを以下のようにすると、employees_copyテーブルにemp_noカラムの次にdept_noを追加します。

    結果はこうなります。

    firstを指定すると、先頭にカラムが追加されます。

    実行結果はこうなります。

    カラム追加のサンプルコード集

    複数のカラムを追加するには

    「,」(カンマ)で区切って実行すると、複数のカラムを一度に追加できます。

    カラムを削除する「drop」を組み合わせることも可能です。

    同様に、change、modifyも1クエリにまとめて実行が可能です。

    カラム追加時にデフォルト値を設定するには

    カラム追加時にカラムのコメントを追加するには

    以下のSQLで、追加したカラムにコメントを設定できます。

    【関連記事】
    SQLのコメントの使い方 主要DBMSだけでもこれだけ違う 独自拡張機能も

    追加済みカラムの位置などを変更するには

    カラムの位置に限らず、追加済みカラムの設定を変更するには、alter table changeを使用します。

    変更箇所だけでなく、全てのカラム構造の情報を指定する必要があります。

    alter table modifyは、フィールド名とデータ型のみ変更することができます。alter table changeとは少々書式が異なります。

    データありテーブルに、not NULL制約のカラムを追加すると、型ごとに異なる初期値が入る

    データが存在するテーブルに、not NULL制約つきのカラムを追加すると値はどうなるのか試してみます。

    まずは、employeesテーブルの構造とデータをコピーした、employees_copy2を用意します。

    employees_copy2テーブルの中身はこうなってます。

    not NULL制約のついたcol_char4(char型)、col_int(int型)、col_date(date型)のカラムを追加します。

    employees_copy2の中身はこうなります。

    char(4)のカラムには、”(空文字)、int型のカラムには0、date型のカラムには「0000-00-00」が初期値として入りました。

    ALTER TABLE ADDのカラム追加が遅くなるケース

    500万行のテーブルに対して、カラムを1つ追加したら1時間以上の待ちが発生した、というケースがあるようです。

    参考)大きめのテーブルにカラムやインデックスを追加する際の注意 – LukeSilvia’s diary

    ただし、MySQLのバージョンは4.1と古く、MyISAMテーブルを使っていて、キーキャッシュのサイズ(key_buffer_size)が小さかったのが原因のようです。

    最近では、他に同様のトラブルは見られない点から、あまり気にする必要はないのかも知れませんね。

    まとめ

    ポテパンダの一言メモ
    • MySQLのテーブルカラム追加は、alter table addで可能
    • テーブル追加時に、カラム名、データ型、NULL許可、デフォルト値、コメントを指定可能
    • 複数カラムを一括指定できる
    • 追加済みカラムの変更は、alter table change、modify、renameでおこなう
    • 古いMySQLでMyISAMテーブルを使っている場合、カラム追加で1時間以上の待ちが発生するケースあり


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

    この記事をシェア

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









    ABOUT US

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

    READ MORE