使用 MySQL DROP COLUMN 语句从表中删除列

有时候，您可能因为以下原因需要从现有的表中删除一个或多个列：

• 此列是多余的。
• 此列数据类型已经发生变化，需要先删除列后再重新创建列并导入数据。
• 此列已经被其他的列代替了。

假设，您有一个用户表，它有用户名，邮件，密码等信息。但是为了安全性，您需要将密码列迁移到另外的一个表中，然后删除掉用户表中的密码列。

MySQL 允许您使用 ALTER TABLE 语句来修改一个现有的表。要从一个表中删除一列或多列，请使用 ALTER TABLE ... DROP COLUMN 语句。

删除列注意事项

删除列是一个很危险的动作。像使用 DROP TABLE 删除表一样，删除列后，您很难再恢复其中的数据。

删除列之前一定要确定此动作的必要性。

另外，删除列还可能带来一些隐藏的问题：

• 如果要删除的列被外键引用，您需要同步删除外键后才能进行。这可能会破坏数据的完整性。
• 删除列后，您需要同步修改应用程序中依赖此列的代码。这包括独立的应用程序和触发器、视图、存储过程和函数中的引用。
• 对于一个大表来说，删除列是一个很耗时的过程。

重要：删除之前请一定要备份表和表中的数据。

MySQL DROP COLUMN 语法

以下是 MySQL ALTER TABLE ... DROP COLUMN 语句的语法：

ALTER TABLE table_name
DROP [COLUMN] column_name
[, DROP [COLUMN] column_name];

在这个语法中：

• 位于 ALTER TABLE 关键字之后的 table_name 是从中删除列的表名。
• 位于 DROP COLUMN 关键字之后的 column_name 是要删除的列名。
• 您可以省略 DROP COLUMN 关键字中的 COLUMN 关键字。它是可选的。
• 如果您需要在一个语句中删除多个列，请使用多个使用逗号分隔的 DROP COLUMN 子句。

确定列是否存在

在从表中删除一个列之前，您可以需要首先确定此表中是否存在此列。

要查看一个表中的所有列的信息，您可以使用 DESC 或者 SHOW COLUMNS 两个语句中的一个。这两个语句都可以显示一个表中的所有的列，但是 SHOW COLUMNS 语句更灵活和方便，因为它可以根据您的要求过滤结果集。

DESC 语句的语法如下：

DESC table_name;

SHOW COLUMNS 语句的语法如下：

SHOW [FULL] COLUMNS FROM table_name [LIKE pattern]

其中 LIKE 子句用来指定过滤模式。

MySQL DROP COLUMN 实例

这个实例演示了如何使用 ALTER TABLE ... DROP COLUMN 语句从表中删除一个或两个列。

假设，我们有一个用户表，其中有 ID，用户名，年龄，邮件和电话号码五列。

我们使用以下语句在 testdb 数据库中 创建一个表 user 用以存储用户信息：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int NOT NULL DEFAULT '20',
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

如果您没有 testdb 数据库，请先使用如下语句创建数据库并选择数据库：

CREATE DATABASE testdb;
use testdb;

创建表后，您可以使用 DESC 语句查看此表中的所有列：

DESC user;

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
| age   | int          | NO   |     | 20      |                |
| email | varchar(255) | NO   |     | NULL    |                |
| phone | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

向用户表中插入一行数据，如下；

INSERT INTO user (name, age, email, phone)
values ('Tim', 20, '[email protected]', '8769232');

使用 MySQL DROP COLUMN 从表中删除一列

要从 user 表中删除 age 列，请使用下面的语句：

ALTER TABLE user
DROP COLUMN age;

注意，删除此列后，此列中的数据也随之删除。

让我们通过 DESC 语句查看 user 表中的所有列：

DESC user;

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
| email | varchar(255) | NO   |     | NULL    |                |
| phone | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

我们也可以通过以下 SELECT 语句查询表中的数据以验证：

SELECT * FROM user;

+----+------+---------------+---------+
| id | name | email         | phone   |
+----+------+---------------+---------+
|  1 | Tim  | [email protected] | 8769232 |
+----+------+---------------+---------+

使用 MySQL DROP COLUMN 从表中删除二列

要从 user 表中删除 email 列和 phone 列，请使用下面的语句：

ALTER TABLE user
DROP COLUMN email,
DROP COLUMN phone;

让我们检查 user 表的行：

SELECT * FROM user;

+----+------+
| id | name |
+----+------+
|  1 | Tim  |
+----+------+

结论

MySQL 提供了 ALTER TABLE ... DROP COLUMN 语句从表中删除一个或多个列。 通过 ALTER TABLE 语句，您可以重命名表、重命名列、添加列、删除列、修改列的属性等。