MySQL修改資料表（ALTER TABLE語句）

為實現資料庫中表規範化設計的目的，有時候需要對之前已經建立的表進行結構修改或者調整。

在 MySQL 中可以使用 ALTER TABLE 語句來改變原有表的結構，例如增加或刪減列、建立或取消索引、更改原有列型別、重新命名列或表等。

基本語法

修改表指的是修改資料庫中已經存在的資料表的結構。MySQL 使用 ALTER TABLE 語句修改表。常用的修改表的操作有修改表名、修改欄位資料型別或欄位名、增加和刪除欄位、修改欄位的排列位置、更改表的儲存引擎、刪除表的外來鍵約束等。

常用的語法格式如下：

ALTER TABLE <表名> [修改選項]

修改選項的語法格式如下：

{ ADD COLUMN <列名> <型別>
| CHANGE COLUMN <舊列名> <新列名> <新列型別>
| ALTER COLUMN <列名> { SET DEFAULT <預設值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <型別>
| DROP COLUMN <列名>
| RENAME TO <新表名> }

新增欄位

隨著業務的變化，可能需要在已經存在的表中新增新的欄位，一個完整的欄位包括欄位名、資料型別、完整性約束。新增欄位的語法格式如下：

ALTER TABLE <表名> ADD <新欄位名> <資料型別> [約束條件] [FIRST|AFTER 已存在的欄位名]；

新欄位名為需要新增的欄位的名稱；FIRST 為可選引數，其作用是將新新增的欄位設定為表的第一個欄位；AFTER 為可選引數，其作用是將新新增的欄位新增到指定的已存在的欄位名的後面。

【範例 1】使用 ALTER TABLE 修改表 tb_emp1 的結構，在表的第一列新增一個 int 型別的欄位 col1，輸入的 SQL 語句和執行結果如下所示。

mysql> ALTER TABLE tb_emp1
    -> ADD COLUMN col1 INT FIRST;
Query OK, 0 rows affected (0.94 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col1   | int(11)     | YES  |     | NULL    |       |
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

提示：“FIRST 或 AFTER 已存在的欄位名”用於指定新增欄位在表中的位置，如果 SQL 語句中沒有這兩個引數，則預設將新新增的欄位設定為資料表的最後列。

【範例 2】使用 ALTER TABLE 修改表 tb_emp1 的結構，在一列 name 後新增一個 int 型別的欄位 col2，輸入的 SQL 語句和執行結果如下所示。

mysql> ALTER TABLE tb_emp1
    -> ADD COLUMN col2 INT AFTER name;
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col1   | int(11)     | YES  |     | NULL    |       |
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| col2   | int(11)     | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

可以看到，表 tb_emp1 中增加了一個名稱為 col2 的欄位，其位置在指定的 name 欄位後面，新增欄位成功。

修改欄位資料型別

修改欄位的資料型別就是把欄位的資料型別轉換成另一種資料型別。在 MySQL 中修改欄位資料型別的語法規則如下：

ALTER TABLE <表名> MODIFY <欄位名> <資料型別>

其中，表名指要修改資料型別的欄位所在表的名稱，欄位名指需要修改的欄位，資料型別指修改後欄位的新資料型別。

【範例 3】使用 ALTER TABLE 修改表 tb_emp1 的結構，將 name 欄位的資料型別由 VARCHAR(22) 修改成 VARCHAR(30)，輸入的 SQL 語句和執行結果如下所示。

mysql> ALTER TABLE tb_emp1
    -> MODIFY name VARCHAR(30);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col1   | int(11)     | YES  |     | NULL    |       |
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| col2   | int(11)     | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

語句執行後，發現表 tb_emp1 中 name 欄位的資料型別已經修改成 VARCHAR(30)，修改成功。

刪除欄位

刪除欄位是將資料表中的某個欄位從表中移除，語法格式如下：

ALTER TABLE <表名> DROP <欄位名>；

其中，欄位名指需要從表中刪除的欄位的名稱。

【範例 4】使用 ALTER TABLE 修改表 tb_emp1 的結構，刪除 col2 欄位，輸入的 SQL 語句和執行結果如下所示。

mysql> ALTER TABLE tb_emp1
    -> DROP col2;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col1   | int(11)     | YES  |     | NULL    |       |
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改欄位名稱

MySQL 中修改表欄位名的語法規則如下：

ALTER TABLE <表名> CHANGE <舊欄位名> <新欄位名> <新資料型別>；

其中，舊欄位名指修改前的欄位名；新欄位名指修改後的欄位名；新資料型別指修改後的資料型別，如果不需要修改欄位的資料型別，可以將新資料型別設定成與原來一樣，但資料型別不能為空。

【範例 5】使用 ALTER TABLE 修改表 tb_emp1 的結構，將 col1 欄位名稱改為 col3，同時將資料型別變為 CHAR(30)，輸入的 SQL 語句和執行結果如下所示。

mysql> ALTER TABLE tb_emp1
    -> CHANGE col1 col3 CHAR(30);
Query OK, 0 rows affected (0.76 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col3   | char(30)    | YES  |     | NULL    |       |
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

CHANGE 也可以只修改資料型別，實現和 MODIFY 同樣的效果，方法是將 SQL 語句中的“新欄位名”和“舊欄位名”設定為相同的名稱，只改變“資料型別”。

提示：由於不同型別的資料在機器中的儲存方式及長度並不相同，修改資料型別可能會影響資料表中已有的資料記錄，因此，當資料表中已經有資料時，不要輕易修改資料型別。

修改表名

MySQL 通過 ALTER TABLE 語句來實現表名的修改，語法規則如下：

ALTER TABLE <舊表名> RENAME [TO] <新表名>；

其中，TO 為可選引數，使用與否均不影響結果。

【範例 6】使用 ALTER TABLE 將資料表 tb_emp1 改名為 tb_emp2，輸入的 SQL 語句和執行結果如下所示。

mysql> ALTER TABLE tb_emp1
    -> RENAME TO tb_emp2;
mysql> SHOW TABLES;
+--------------------+
| Tables_in_test_db  |
+--------------------+
| tb_emp2            |
+--------------------+
1 rows in set (0.00 sec)

提示：使用者可以在修改表名稱時使用 DESC 命令檢視修改後兩個表的結構，修改表名並不修改表的結構，因此修改名稱後的表和修改名稱前的表的結構是相同的。