自分用のメモとして作成中。内容は随時更新していく予定です。
1. テーブルへの列追加
1.1 最後尾に追加
alter table テーブル名 add column カラム名 カラム定義
mysql> desc t_sample;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> alter table t_sample add column num000 tinyint default 0;
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_sample;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| num000 | tinyint(4) | YES | | 0 | |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
1.2 最後尾以外の特定カラムの後に追加
alter table テーブル名 add column カラム名 カラム定義 after 対象カラム名
mysql> desc t_sample;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| num000 | tinyint(4) | YES | | 0 | |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> alter table t_sample add column num001 smallint default 100 after id;
Query OK, 0 rows affected (1.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_sample;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| num001 | smallint(6) | YES | | 100 | |
| num000 | tinyint(4) | YES | | 0 | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
1.3 先頭に追加
alter table テーブル名 add column カラム名 カラム定義 first
mysql> desc t_sample;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| num000 | tinyint(4) | YES | | 0 | |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table t_sample add column num002 int first;
Query OK, 0 rows affected (0.59 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_sample;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| num002 | int(11) | YES | | NULL | |
| id | char(10) | YES | | NULL | |
| num000 | tinyint(4) | YES | | 0 | |
+--------+------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
2. テーブルの列定義変更
alter table テーブル名 modify column 対象カラム名 カラム定義
mysql> desc t_sample;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| num001 | smallint(6) | YES | | 100 | |
| num000 | tinyint(4) | YES | | 0 | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
mysql> alter table t_sample modify column num001 middleint default 200;
Query OK, 0 rows affected (1.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_sample;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| num001 | mediumint(9) | YES | | 200 | |
| num000 | tinyint(4) | YES | | 0 | |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.05 sec)
3. テーブルからの列削除
alter table テーブル名 drop column 対象カラム名
mysql> desc t_sample;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| num001 | mediumint(9) | YES | | 200 | |
| num000 | tinyint(4) | YES | | 0 | |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table t_sample drop column num001;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_sample;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| num000 | tinyint(4) | YES | | 0 | |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.05 sec)
4. 既存の列の列名変更
alter table テーブル名 change column 対象カラム名 新カラム名 新カラム定義
※単純に名前だけを変えたい場合でも、カラム定義を指定する必要がある点に注意。
mysql> desc t_sample;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| num000 | tinyint(4) | YES | | 0 | |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
-- 新カラム名だけを指定しても、カラム名の変更はできない。
mysql> alter table t_sample change column num000 num100;
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 '' at line 1
mysql>
mysql> alter table t_sample change column num000 num100 tinyint default 0;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_sample;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| num100 | tinyint(4) | YES | | 0 | |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
5. 既存の列のデフォルト値変更
alter table テーブル名 alter 対象カラム名 set default デフォルト値
mysql> desc t_sample;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| num000 | tinyint(4) | YES | | 0 | |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> alter table t_sample alter num000 set default 127;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_sample;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| num000 | tinyint(4) | YES | | 127 | |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql>
備考
- いずれの変更においても、"column"は省略可能。
- 最後尾への追加であれば、add [column]の後を"(カラム名1 カラム定義1, カラム名2 カラム定義2[, ...])"とすることで、複数の列を同時に追加することが可能。
- 最後尾以外への列追加、列定義の変更、列削除については、2カラム以上の同時実施は不可。