MySQLでテーブルのカラム(列)の変更を行うにはALTER TABLEを利用します。ALTER TABLEはカラム以外にもテーブルのあらゆる変更が行える万能選手ですが今回はカラムの追加,変更,削除に特化して見ていきます。
カラムを追加する
-- テーブルの末尾に追加 ALTER TABLE tablename ADD colname varchar(64); -- テーブルの先頭に追加 ALTER TABLE tablename ADD colname varchar(64) FIRST; -- 指定カラムの後ろに追加 ALTER TABLE tablename ADD colname varchar(64) AFTER id; -- idの後ろに追加
カラムを変更する
-- カラム名とデータ型を変更 ALTER TABLE tablename CHANGE old_colname new_colname integer; -- カラム名だけを変更(MySQL8.0〜) ALTER TABLE tablename RENAME COLUMN old_colname TO new_colname;
カラムを削除する
ALTER TABLE tablename DROP colname;
準備
データベースとテーブル
テスト用のデータベースとテーブル、初期データを準備します。
/*---------------------------*/ /* データベースとテーブルを準備 */ /*---------------------------*/ -- データベースを作成＆選択 CREATE DATABASE rpg; USE rpg; -- テーブルを作成 CREATE TABLE Monster( id int, name varchar(64), primary key(id) ); -- 初期データを挿入 INSERT INTO Monster(id, name) VALUES (1, 'slime'), (2, 'golem'), (3, 'dragon');
上記のSQLを実行した後に正常に作成されたか確認しておきます。
mysql> desc Monster; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from Monster; +----+--------+ | id | name | +----+--------+ | 1 | slime | | 2 | golem | | 3 | dragon | +----+--------+ 3 rows in set (0.00 sec)
カラムを追加する
テーブルの末尾に追加
テーブルの最後にhpという名前、データ型は整数型のカラムを追加してみます。
ALTER TABLE Monster ADD hp int;
テーブル定義を確認すると最後にhpが増えているのがわかります。
mysql> desc Monster; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | hp | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
データを覗くとNULLが入っていました。特にDEFAULTを指定しない場合はこのようにNULLが入ります。任意の値を各レコードに入れたい場合はこの後にUPDATE文などを実行してやります。
mysql> select * from Monster; +----+--------+------+ | id | name | hp | +----+--------+------+ | 1 | slime | NULL | | 2 | golem | NULL | | 3 | dragon | NULL | +----+--------+------+ 3 rows in set (0.00 sec)
テーブルの先頭に追加
テーブルの先頭にtown_cdという名前、文字列型のカラムを追加してみます。先ほど変わったのは最後にFIRSTが加わっただけですね。
ALTER TABLE Monster ADD town_cd varchar(3) FIRST;
実行後にテーブル構造を確認すると意図した通りにカラムが追加されているのがわかります。
mysql> desc Monster; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | town_cd | varchar(3) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | hp | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
指定カラムの後ろに追加
最後に指定したカラムの後ろに追加します。ここではnameの後ろにrarityという名前で、文字列型のカラムを追加します。
ALTER TABLE Monster ADD rarity varchar(2) AFTER name;
これも無事に指定カラムの後ろに追加できましたね。
mysql> desc Monster; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | town_cd | varchar(3) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | rarity | varchar(2) | YES | | NULL | | | hp | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
カラムを変更する
名前を変更する
MySQL5.7まで
town_cdの名前をarea_cdに変更してみます。データ型は今回はそのままとします。
ALTER TABLE Monster CHANGE town_cd area_cd varchar(3);
はい、無事に名前だけ変更ができました。
mysql> desc Monster; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | area_cd | varchar(3) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | rarity | varchar(2) | YES | | NULL | | | hp | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
なおCHANGEの部分はMODIFYとしても同様に利用できます。これはOracleとの互換性対応のようです。
MySQL8.0以降
MySQL8.0以降ではカラムの名前だけを変更したい場合には、RENAME COLUMNを利用することができます。
ALTER TABLE Monster RENAME COLUMN town_cd TO area_cd;
5.7までで実行するともちろん構文エラーになりますｗ
mysql> ALTER TABLE Monster RENAME COLUMN area_cd TO area_cd2; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLUMN area_cd TO area_cd2' at line 1
データ型や定義を変更する
area_cdのデータ型をvarchar(64)からより巨大なデータの入るtextに変更してみます。
ALTER TABLE Monster CHANGE area_cd area_cd text;
はい、無事に変更できました。カラム名変えない場合は指定しなくて良い構文があると良いのですけどね。
mysql> desc Monster; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | area_cd | text | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | rarity | varchar(2) | YES | | NULL | | | hp | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
順番を変更する
並び順を移動するだけのSQLは用意されていないので、先ほど使用したCHANGEを使います。
指定カラムの後ろに移動
SQLの最後にAFTER 移動先のカラム名を追加してやります。
ALTER TABLE Monster CHANGE area_cd area_cd text AFTER rarity;
rarityの後ろにarea_cdを移動させることができました。
mysql> desc Monster; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | rarity | varchar(2) | YES | | NULL | | | area_cd | text | YES | | NULL | | | hp | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
テーブルの最初に移動
一番最初に移動する場合は単にFIRSTを最後に付けるだけです。
ALTER TABLE Monster CHANGE area_cd area_cd text FIRST;
ご覧の通りです。area_cd(元town_cd)さん弄んですみません。
mysql> desc Monster; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | area_cd | text | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | rarity | varchar(2) | YES | | NULL | | | hp | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
データの定義を変更するわけではないのに、ちょっと面倒ですねｗ 移動するだけの構文があると良いのですが。
カラムを削除する
先ほど追加したarea_cdは気の迷いだったので削除しておきます。ADDだった部分がDROPになっただけなのでわかりやすいですね。SQLでのDROPは基本的に削除を意味します。
ALTER TABLE Monster DROP area_cd;
散々いじくり回したarea_cd(元town_cd)さんをキレイさっぱり削除することができました。来世では活躍してくれることを祈ってます。
mysql> desc Monster; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(64) | YES | | NULL | | | rarity | varchar(2) | YES | | NULL | | | hp | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
もちろんカラム内のデータは消えてなくなるので、実行する際はくれぐれもご注意を。
