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

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

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

MySQLのカラム追加はALTER TABLE

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

alter table 
	テーブル名 
add 
	カラム名
	データ型
	NULL許可
	DEFAULT デフォルト値
	COMMENT コメント
	キー指定
first/after カラム位置の指定

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

create table employees_copy
like employees

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

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

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

alter table 
	employees_copy 
add 
	dept_no char(4)
	NOT NULL

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

alter table 
	employees_copy 
add 
	dept_name varchar(40)
	NOT NULL

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

show columns from employees_copy

結果はこうなります。

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

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

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

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

alter table 
	employees_copy 
add 
	dept_no char(4)
	NOT NULL
after	emp_no

結果はこうなります。

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

alter table 
	employees_copy 
add 
	dept_no char(4)
	NOT NULL
first

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

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

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

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

alter table 
	employees_copy 
add 	dept_no char(4) NOT NULL after emp_no, 
add 	dept_name varchar(40) NOT NULL

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

alter table 
	employees_copy 
drop column dept_no,
drop column dept_name,
add 	dept_no char(4) NOT NULL after emp_no, 
add 	dept_name varchar(40) NOT NULL

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

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

alter table 
	employees_copy 
add 
	dept_no char(4)
	NOT NULL
	DEFAULT 0
after	emp_no

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

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

alter table 
	employees_copy 
add 
	dept_no char(4)
	NOT NULL
	DEFAULT 0
	COMMENT '部署番号'
after	emp_no

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

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

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

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

alter table 
	employees_copy 
change dept_no
	dept_no char(4)
	NOT NULL
	DEFAULT 0
	COMMENT '部署番号'
after	emp_no

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

alter table 
	employees_copy 
modify dept_no
	char(5)

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

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

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

create table employees_copy2
like employees;
insert into employees_copy2
select * from employees;

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

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

alter table 	employees_copy2 
add col_char4 char(4) not NULL, 
add col_int int not NULL, 
add col_date date not NULL;

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

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

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

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

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

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

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

まとめ

ポテパンダの一言メモ

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