mysql中的ALTER TABLE功能

MySQL 使用 ALTER TABLE 语句修改表。修改表指的是修改数据库中已经存在的数据表的结构。常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。

常用的语法格式如下：
ALTER TABLE <表名> [修改选项]

修改选项的语法格式如下：

{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名> }

添加字段

添加字段的语法格式如下：
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名]；

【例 1】使用 ALTER TABLE 修改表 temp_table 的结构，在表的第一列添加一个 int 类型的字段 newcol，输入的 SQL 语句和运行结果如下所示。

mysql> ALTER TABLE temp_table
-> ADD COLUMN newcol INT FIRST;

显示结果：

+--------+-------+------+-----+---------+----------------+
| Field  | Type  | Null | Key | Default | Extra          |
+--------+-------+------+-----+---------+----------------+
| newcol | int   | YES  |     | NULL    |                |
| id     | int   | NO   | PRI | NULL    | auto_increment |
| x      | float | YES  |     | NULL    |                |
| y      | float | YES  |     | NULL    |                |
| z      | float | YES  |     | NULL    |                |
| T      | float | YES  |     | NULL    |                |
| V      | float | YES  |     | NULL    |                |
+--------+-------+------+-----+---------+----------------+

提示1：ADD COLUMN newcol INT FIRST;  这里的COLUMN 可以省略。可写成：ADD newcol INT FIRST;

提示2：“FIRST 或 AFTER 已存在的字段名”用于指定新增字段在表中的位置，如果 SQL 语句中没有这两个参数，则默认将新添加的字段设置为数据表的最后列。

【例 2】使用 ALTER TABLE 修改表 temp_table 的结构，在一列 newcol后添加一个 float 类型的字段 newcol2，输入的 SQL 语句和运行结果如下所示。

mysql> ALTER TABLE temp_tabel
-> ADD newcol2 float AFTER newcol;

显示结果：

+---------+-------+------+-----+---------+----------------+
| Field   | Type  | Null | Key | Default | Extra          |
+---------+-------+------+-----+---------+----------------+
| newcol  | int   | YES  |     | NULL    |                |
| newcol2 | float | YES  |     | NULL    |                |
| id      | int   | NO   | PRI | NULL    | auto_increment |
| x       | float | YES  |     | NULL    |                |
| y       | float | YES  |     | NULL    |                |
| z       | float | YES  |     | NULL    |                |
| T       | float | YES  |     | NULL    |                |
| V       | float | YES  |     | NULL    |                |
+---------+-------+------+-----+---------+----------------+

修改字段数据类型

修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 MySQL 中修改字段数据类型的语法规则如下：
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
其中，表名指要修改数据类型的字段所在表的名称，字段名指需要修改的字段，数据类型指修改后字段的新数据类型。

【例 3】使用 ALTER TABLE 修改表 temp_table 的结构，将 newcol2 字段的数据类型由 float 修改成 int，输入的 SQL 语句和运行结果如下所示。

mysql> ALTER TABLE temp_table
-> MODIFY newcol2 int;

显示结果：

+---------+-------+------+-----+---------+----------------+
| Field   | Type  | Null | Key | Default | Extra          |
+---------+-------+------+-----+---------+----------------+
| newcol  | int   | YES  |     | NULL    |                |
| newcol2 | int   | YES  |     | NULL    |                |
| id      | int   | NO   | PRI | NULL    | auto_increment |
| x       | float | YES  |     | NULL    |                |
| y       | float | YES  |     | NULL    |                |
| z       | float | YES  |     | NULL    |                |
| T       | float | YES  |     | NULL    |                |
| V       | float | YES  |     | NULL    |                |
+---------+-------+------+-----+---------+----------------+

删除字段

ALTER TABLE <表名> DROP <字段名>；

【例 4】使用 ALTER TABLE 修改表 temp_table 的结构，删除 newcol2 字段，输入的 SQL 语句和运行结果如下所示。

mysql> ALTER TABLE temp_table
-> DROP newcol2;

显示结果：

+--------+-------+------+-----+---------+----------------+
| Field  | Type  | Null | Key | Default | Extra          |
+--------+-------+------+-----+---------+----------------+
| newcol | int   | YES  |     | NULL    |                |
| id     | int   | NO   | PRI | NULL    | auto_increment |
| x      | float | YES  |     | NULL    |                |
| y      | float | YES  |     | NULL    |                |
| z      | float | YES  |     | NULL    |                |
| T      | float | YES  |     | NULL    |                |
| V      | float | YES  |     | NULL    |                |
+--------+-------+------+-----+---------+----------------+

修改字段名称

MySQL 中修改表字段名的语法规则如下：
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

其中，旧字段名指修改前的字段名；新字段名指修改后的字段名；新数据类型指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。

【例 5】使用 ALTER TABLE 修改表 temp_table 的结构，将 newcol2 字段名称改为 col2，同时将数据类型变为 float，输入的 SQL 语句和运行结果如下所示。

mysql> ALTER TABLE temp_table
-> CHANGE newcol2 col2 float;

显示结果：

+--------+-------+------+-----+---------+----------------+
| Field  | Type  | Null | Key | Default | Extra          |
+--------+-------+------+-----+---------+----------------+
| newcol | int   | YES  |     | NULL    |                |
| col2   | float | YES  |     | NULL    |                |
| id     | int   | NO   | PRI | NULL    | auto_increment |
| x      | float | YES  |     | NULL    |                |
| y      | float | YES  |     | NULL    |                |
| z      | float | YES  |     | NULL    |                |
| T      | float | YES  |     | NULL    |                |
| V      | float | YES  |     | NULL    |                |
+--------+-------+------+-----+---------+----------------+