MySQL ALTER TABLE 命令修改表結構完整指南與實戰範例

MySQL ALTER TABLE 命令詳細解析

在 MySQL 資料庫管理中，ALTER TABLE 是修改現有表結構的核心命令。它支援多種操作，包括新增/刪除欄位、修改資料型態、設定主鍵、建立索引、重新命名表格等。本文將透過兩個實戰範例，詳細示範如何靈活運用此命令進行表結構維護。

範例一：新增欄位、修改型態與設定主鍵

1. 建立基礎表格

首先建立一個僅包含 ID 欄位的簡單表格：

mysql> CREATE TABLE myTable(
    -> ID SMALLINT
    -> );

2. 查看初始結構

使用 DESC 命令檢視表結構：

mysql> desc myTable;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | smallint(6) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

3. 執行複合 ALTER TABLE 操作

單一命令完成三項操作：新增 Quantity 欄位、修改 ID 為無號數且非空、設定 ID 為主鍵：

mysql> ALTER TABLE myTable
    -> ADD COLUMN Quantity SMALLINT UNSIGNED NOT NULL,
    -> MODIFY ID SMALLINT UNSIGNED NOT NULL,
    -> ADD PRIMARY KEY (ID);

4. 驗證修改結果

mysql> desc myTable;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| ID       | smallint(5) unsigned | NO   | PRI |         |       |
| Quantity | smallint(5) unsigned | NO   |     |         |       |
+----------+----------------------+------+-----+---------+-------+

結果顯示：ID 已變更為 UNSIGNED NOT NULL 並成為主鍵（PRI），Quantity 成功新增且同樣設為非空無號數。

範例二：刪除欄位與主鍵

承接上例，移除 Quantity 欄位與主鍵約束：

mysql> ALTER TABLE myTable
    -> DROP COLUMN Quantity,
    -> DROP PRIMARY KEY;

驗證刪除結果

mysql> desc myTable;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| ID    | smallint(5) unsigned | NO   |     |         |       |
+-------+----------------------+------+-----+---------+-------+

表格恢復僅含 ID 欄位，但保留 UNSIGNED NOT NULL 屬性，主鍵標記消失。

核心語法總結

• 新增欄位：ADD COLUMN 欄位名 資料型態 [約束條件]
• 修改欄位：MODIFY 欄位名 新資料型態 [新約束條件]
• 設定主鍵：ADD PRIMARY KEY (欄位名)
• 刪除欄位：DROP COLUMN 欄位名
• 刪除主鍵：DROP PRIMARY KEY

最佳實踐建議

1. 生產環境操作前務必備份，結構變更具風險
2. 大表結構變更建議使用 pt-online-schema-change 等工具避免鎖表
3. 批次操作：單一 ALTER TABLE 可合併多個子句，減少鎖表次數
4. 測試驗證：先在測試環境驗證語法與效果

掌握 ALTER TABLE 的靈活運用，能讓資料庫結構隨業務演進而平滑調整，是 DBA 與後端開發者的必備技能。