MySQL 中 ALTER 命令的使用方法

当想要更改表的名称、任何表字段或想要添加或删除表中的现有列时，就会使用到 MySQL 的 ALTER 命令。

让我们从创建一个名为 testalter_tbl 的表开始。

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

mysql> use TUTORIALS;
Database changed

mysql> create table testalter_tbl
   -> (
   -> i INT,
   -> c CHAR(1)
   -> );
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   i   | int(11) | YES  |     |   NULL  |       |
|   c   | char(1) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除、添加或重新定位列

如果你想从上面的 MySQL 表中删除现有的列 i，那么可以使用 ALTER 命令和 DROP 语句，如下所示 ：

mysql> ALTER TABLE testalter_tbl  DROP i;

如果该列是表中唯一剩下的列，则 DROP 语句将不起作用。

要添加列，请使用 ADD 并指定列定义。以下语句将 i 列恢复到 testalter_tbl 表中：

mysql> ALTER TABLE testalter_tbl ADD i INT;

执行该语句后，testalter 将包含与创建表时相同的两列，但不会具有相同的结构。这是因为默认情况下会在表的末尾添加新列。所以最初它是 mytbl 中的第一列，现在它是最后一列。

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

要在表中指定位置，请使用 FIRST 使其成为第一列或使用 AFTER col_name 指定将新列放在 col_name （例如下面例子中的 C 列）之后。

可尝试以下 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 testalter_tbl MODIFY c CHAR(10);

使用 CHANGE，语法有点不同。在 CHANGE 关键字之后，为要更改的列命名，然后指定包含新名称的新定义。

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

如果现在使用 CHANGE 将 j 从 BIGINT 转换回 INT 而不更改列名，则语句将如下所示 ：

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE 对 Null 和默认值属性的影响：当修改列时，还可以指定该列是否可以包含 NULL 值及其默认值。事实上，如果你不这样做，MySQL 会自动为这些属性分配值。

以下是一个示例，其中 NOT NULL 列的默认值为 100。

mysql> ALTER TABLE testalter_tbl 
   -> MODIFY j BIGINT NOT NULL DEFAULT 100;

如果不使用上述命令，那么 MySQL 将在所有列中填充 NULL 值。

更改列的默认值

可以使用 ALTER 命令更改任何列的默认值。如下面的例子所示：

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   1000  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

可以使用 DROP 语句和 ALTER 命令从任何列中删除默认值。

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

更改表类型

可以通过将ALTER 命令和 TYPE 语句与一起使用来更改表类型。以下示例将 testalter_tbl 更改为 MISAM 表类型。

要找出表的当前类型，请使用 SHOW TABLE STATUS 语句。

mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G
*************************** 1. row ****************
           Name: testalter_tbl
           Type: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 25769803775
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-06-03 08:04:36
    Update_time: 2007-06-03 08:04:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

重命名表

要重命名表，请使用 ALTER TABLE 语句的 RENAME 选项。

尝试以下示例将 testalter_tbl 重命名为 alter_tbl。

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

还可以使用 ALTER 命令在 MySQL 上创建和删除 INDEX 命令。