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 文件上创建和删除索引。我们将在下一章中介绍此功能。

打印页面