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。我们将在下一章看到这个功能。