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，则可以使用以下示例所示的 DROP 子句和 ALTER 命令。

mysql> ALTER TABLE testalter_tbl  DROP i;

DROP 子句只有当列是表中唯一剩下的列时才能生效。

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

mysql> ALTER TABLE testalter_tbl ADD i INT;

在发布这个声明之后，testalter将包含与您第一次创建表时相同的两列，但不具有相同的结构。这是因为新的列是以默认方式添加到表的末尾。所以即使 i 最初是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之后。

尝试以下 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;

首先和之后的修饰词只能与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和Default Value属性的影响 - 当您修改或更改列时，您还可以指定列是否可以包含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 更改为 MYISAM 表类型。

要查找表的当前类型，请使用 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命令。我们将在下一章节详细讨论这个命令。