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 子句。 这意味着，如果您想要重新定位表中的现有列，您首先必须将其删除，然后将其添加到新位置。

更改列定义或名称

要更改列的定义，请使用 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 文件上创建和删除 INDEX。 我们将在下一章中看到此功能。