MySQLi - ALTER 命令

MySQL ALTER 命令在您想更改表名、任何表字段，或者想在表中新增或刪除現有列時非常有用。

讓我們從建立一個名為 tutorials_alter 的表開始。

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> create table tutorials_alter
   → (
   → i INT,
   → c CHAR(1)
   → );
Query OK, 0 rows affected (0.27 sec)

mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)

刪除、新增或重新定位列

假設您想從上面的 MySQL 表中刪除現有列 i，那麼您將使用 DROP 子句以及 ALTER 命令，如下所示：

mysql> ALTER TABLE tutorials_alter  DROP i;

如果列是表中僅剩的列，則 DROP 將不起作用。

要新增列，請使用 ADD 並指定列定義。以下語句將 i 列恢復到 tutorials_alter：

mysql> ALTER TABLE tutorials_alter ADD i INT;

發出此語句後，testalter 將包含與您第一次建立表時相同的兩列，但結構不會完全相同。這是因為新列預設新增到表的末尾。因此，即使 i 最初是 mytbl 的第一列，現在它是最後一列。

mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

要指示您希望在表中的特定位置放置列，請使用 FIRST 將其設定為第一列，或使用 AFTER col_name 指示新列應放在 col_name 之後。嘗試以下 ALTER TABLE 語句，並在每個語句之後使用 SHOW COLUMNS 來檢視每個語句的效果：

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

FIRST 和 AFTER 說明符僅適用於 ADD 子句。這意味著，如果您想在表中重新定位現有列，則必須首先將其 DROP，然後在新的位置 ADD 它。

更改列定義或名稱

要更改列的定義，請使用 MODIFY 或 CHANGE 子句以及 ALTER 命令。例如，要將列 c 從 CHAR(1) 更改為 CHAR(10)，請執行以下操作：

mysql> ALTER TABLE tutorials_alter MODIFY c CHAR(10);

使用 CHANGE，語法略有不同。在 CHANGE 關鍵字之後，您命名要更改的列，然後指定新的定義，其中包括新名稱。試試下面的例子

mysql> ALTER TABLE tutorials_alter CHANGE i j BIGINT;

如果您現在使用 CHANGE 將 j 從 BIGINT 轉換回 INT 而無需更改列名，則語句將按預期執行：

mysql> ALTER TABLE tutorials_alter CHANGE j j INT;

ALTER TABLE 對 NULL 和預設值屬性的影響

當您修改或更改列時，您還可以指定列是否可以包含 NULL 值以及其預設值是什麼。事實上，如果您不這樣做，MySQL 會自動為這些屬性賦值。

這是一個示例，其中 NOT NULL 列的預設值為 100。

mysql> ALTER TABLE tutorials_alter 
   → MODIFY j BIGINT NOT NULL DEFAULT 100;

如果您不使用上述命令，則 MySQL 將在所有列中填充 NULL 值。

更改列的預設值

您可以使用 ALTER 命令更改任何列的預設值。試試下面的例子。

mysql> ALTER TABLE tutorials_alter ALTER j SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | char(10)   | YES  |     | NULL    |       |
| j     | bigint(20) | NO   |     | 1000    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

您可以使用 DROP 子句以及 ALTER 命令從任何列中刪除預設約束。

mysql> ALTER TABLE tutorials_alter ALTER j DROP DEFAULT;
mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | char(10)   | YES  |     | NULL    |       |
| j     | bigint(20) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

更改表型別

您可以使用 TYPE 子句以及 ALTER 命令使用表型別。

要找出表的當前型別，請使用 SHOW TABLE STATUS 語句。

mysql>  SHOW TABLE STATUS LIKE 'tutorials_alter'\G
*************************** 1. row ***************************
           Name: tutorials_alter
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-02-17 11:30:29
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

重命名錶

要重命名錶，請使用 ALTER TABLE 語句的 RENAME 選項。嘗試以下示例將 tutorials_alter 重新命名為 tutorials_bks。

mysql> ALTER TABLE tutorials_alter RENAME TO tutorials_bks;

您可以使用 ALTER 命令在 MySQL 檔案上建立和刪除索引。我們將在下一章中介紹此功能。

列印頁面