既存のテーブルのカラムに”not null”を追加・削除する。

NOT NULLを削除する

mysql> show fields from site;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id     | int(11)      | NO   | MUL | NULL    |                |
| url         | varchar(500) | NO   | MUL | NULL    |                |
| name        | varchar(500) | NO   | MUL | NULL    |                |
| category_id | int(11)      | NO   | MUL | NULL    |                |
| description | text         | NO   |     | NULL    |                |
| published   | int(11)      | NO   |     | 1       |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> alter table site modify column user_id int;
Query OK, 16 rows affected (0.07 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> show fields from site;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id     | int(11)      | YES  | MUL | NULL    |                |
| url         | varchar(500) | NO   | MUL | NULL    |                |
| name        | varchar(500) | NO   | MUL | NULL    |                |
| category_id | int(11)      | NO   | MUL | NULL    |                |
| description | text         | NO   |     | NULL    |                |
| published   | int(11)      | NO   |     | 1       |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

NOT NULLを設定する

mysql> alter table site modify column user_id int not null;
Query OK, 16 rows affected (0.06 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> show fields from site;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id     | int(11)      | NO   | MUL | NULL    |                |
| url         | varchar(500) | NO   | MUL | NULL    |                |
| name        | varchar(500) | NO   | MUL | NULL    |                |
| category_id | int(11)      | NO   | MUL | NULL    |                |
| description | text         | NO   |     | NULL    |                |
| published   | int(11)      | NO   |     | 1       |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

既存のテーブルにあるカラムにdefault設定を追加・削除する

MySQLのデフォルト設定の追加・削除は以下のように行います。

デフォルト設定の追加

mysql> show fields from site;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id     | int(11)      | NO   | MUL | NULL    |                |
| url         | varchar(500) | NO   | MUL | NULL    |                |
| name        | varchar(500) | NO   | MUL | NULL    |                |
| link_url    | varchar(500) | NO   |     | NULL    |                |
| category_id | int(11)      | NO   | MUL | NULL    |                |
| description | text         | NO   |     | NULL    |                |
| published   | int(11)      | NO   |     | 0       |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
10 rows in set (0.02 sec)

mysql> alter table site alter link_url set default 'http://example.com/';
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show fields from site;
+-------------+--------------+------+-----+---------------------+----------------+
| Field       | Type         | Null | Key | Default             | Extra          |
+-------------+--------------+------+-----+---------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL                | auto_increment |
| user_id     | int(11)      | NO   | MUL | NULL                |                |
| url         | varchar(500) | NO   | MUL | NULL                |                |
| name        | varchar(500) | NO   | MUL | NULL                |                |
| link_url    | varchar(500) | NO   |     | http://example.com/ |                |
| category_id | int(11)      | NO   | MUL | NULL                |                |
| description | text         | NO   |     | NULL                |                |
| published   | int(11)      | NO   |     | 0                   |                |
| created_at  | datetime     | NO   |     | NULL                |                |
| updated_at  | datetime     | NO   |     | NULL                |                |
+-------------+--------------+------+-----+---------------------+----------------+
10 rows in set (0.00 sec)

デフォルト設定を削除

mysql> alter table site alter link_url drop default;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show fields from site;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id     | int(11)      | NO   | MUL | NULL    |                |
| url         | varchar(500) | NO   | MUL | NULL    |                |
| name        | varchar(500) | NO   | MUL | NULL    |                |
| link_url    | varchar(500) | NO   |     | NULL    |                |
| category_id | int(11)      | NO   | MUL | NULL    |                |
| description | text         | NO   |     | NULL    |                |
| published   | int(11)      | NO   |     | 0       |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
10 rows in set (0.04 sec)

既存のカラムを削除する

既存のカラムを削除するには、”ALter”句を使用します。

以下のような感じで使用します。

mysql> ALTER TABLE  DROP 

以下使用例です。

mysql> show fields from tmp;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(100) | YES  |     | NULL    |                |
| age    | int(11)      | YES  |     | NULL    |                |
| tall   | int(11)      | YES  |     | NULL    |                |
| weight | int(11)      | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

mysql> ALTER TABLE tmp DROP weight;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show fields from tmp;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
| tall  | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

テーブルの名前を変更する

テーブル名の変更はRENAMEを使用します。

mysql> ALTER TABLE  RENAME ;
mysql> show tables;
+---------------+
| Tables_in_slt |
+---------------+
| member        |
| slt           |
+---------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE member RENAME user;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_slt |
+---------------+
| slt           |
| user          |
+---------------+
2 rows in set (0.00 sec)