MySQLのテーブルレイアウト変更方法

自分用のメモとして作成中。内容は随時更新していく予定です。

##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カラム以上の同時実施は不可。

##参考サイト

- https://dev.mysql.com/doc/refman/5.6/ja/alter-table.html