MySQL ALTER TABLE 修改表

在本文中,我们介绍了在 MySQL 服务器上如何使用 ALTER TABLE 语句修改表。

在使用表的过程中,如果您需要对表进行修改,您可以使用 ALTER TABLE 语句。通过 ALTER TABLE 语句,您可以重命名表、重命名列、添加列、删除列、修改列的属性等。

ALTER TABLE 语法

ALTER TABLE table_name
  [alter_action options], ...

其中 alter_action 是一个修改动作,包括:

  • ADD 关键字可用来添加列、索引、约束等,包括:

    • ADD [COLUMN]: 添加列
    • ADD INDEX: 添加索引
    • ADD PRIMARY KEY: 添加主键
    • ADD FOREIGN KEY: 添加外键
    • ADD UNIQUE INDEX: 添加唯一索引
    • ADD CHECK: 添加检查约束
  • DROP 关键字可用来删除列、索引、约束等,包括:

    • DROP [COLUMN] col_name: 删除列
    • ADD INDEX index_name: 删除索引
    • DROP PRIMARY KEY: 删除主键
    • DROP FOREIGN KEY fk_symbol: 删除外键
    • DROP CHECK symbol: 删除检查约束
  • MODIFY 关键字用来修改列的定义。与 CHANGE 关键字不同,它不能重命名列。例如: MODIFY [COLUMN] col_name column_definition

  • CHANGE 关键字用来修改列的定义。与 MODIFY 关键字不同,它可以重命名列。例如: CHANGE [COLUMN] old_col_name new_col_name column_definition

  • RENAME 关键字可以重命名列、索引和表。包括:

    • RENAME COLUMN old_col_name TO new_col_name: 重命名列。
    • RENAME INDEX old_index_name TO new_index_name: 重命名索引。
    • RENAME new_tbl_name: 重命名表。

ALTER TABLE 实例

ALTER TABLE 语法看出, ALTER TABLE 用法很多。为了演示 ALTER TABLE 用法,我们在 testdb 数据库中创建一个表 user

请执行以下语句:

CREATE TABLE user (id INT);

通过以下语句查看 user 表的定义。

DESC user;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

下面让我们看一些实际的例子。

添加一列

以下语句使用 ADD 关键字在 user 表中添加了 name 列。

ALTER TABLE user
ADD name VARCHAR(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意,当向包含数据的表中添加列时,新添加的列不满足表的约束定义,则会返回错误。

添加多列

以下语句使用 ADD 关键字在 user 表中添加了 ageemail 列。

ALTER TABLE user
ADD age INT,
ADD email VARCHAR(50);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

重命名列

以下语句使用 RENAME COLUMN 关键字,将 user 表的 name 列重命名为 username

ALTER TABLE user
RENAME COLUMN name TO username;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

以下是修改后的表定义:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(20) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改列定义

以下语句使用 MODIFY 关键字,将 user 表的 username 列由 varchar(20) 修改为 VARCHAR(45)

ALTER TABLE user
MODIFY username VARCHAR(45);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

以下是修改后的表定义:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(45) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| email    | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改列名和定义

以下语句使用 CHANGE 关键字,将 user 表的 username 列修改为 name VARCHAR(30)

ALTER TABLE user
CHANGE username name VARCHAR(30);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

以下是修改后的表定义:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| email | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

添加主键

以下语句使用 ADD 关键字,将 user 表的 id 列设置为主键。

ALTER TABLE user
ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意,由于主键要求列的值是唯一的并且不能为 NULL,如果该列中有重复的值或者 NULL 值,则会返回错误。同样,如果你添加唯一索引,也可能会出现相同的错误。

以下是修改后的表定义:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| email | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

删除列

下面语句使用 DROP 关键字删除了 email 列。

ALTER TABLE user
DROP COLUMN email;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意:删除列是很危险的操作,因为它会永久删除列中的数据。这是不可撤销的操作,请谨慎。

以下是删除后的表定义:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

重命名表

下面语句使用 RENAME 关键字将 user 表重命名为 users

ALTER TABLE user
RENAME users;

我们还可以是用 RENAME TABLE 语句重命名表,它的用法如下:

RENAME TABLE table_name TO new_table_name;

下面的语句同样实现将 user 表重命名为 users

RENAME TABLE user TO users;

结论

在本文中,我们介绍了在 MySQL 服务器上如何使用 ALTER TABLE 语句修改表,包括:添加列、删除列、修改列、重命名列、重命名表和设置主键等。本文要点包括:

  • ALTER TABLE 关键字后面跟要修改的表名。
  • ADD 关键字可用来添加列、索引、约束等。
  • DROP 关键字可用来删除列、索引、约束等。
  • RENAME 关键字可以重命名列、索引和表。
  • MODIFY 关键字用来修改列的定义。
  • CHANGE 关键字用来修改列的定义和列名。
  • RENAME TABLE ... TO ... 用来重命名表。