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

在本文中,您将了解如何使用 MySQL ALTER TABLE ... 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 语句,您可以重命名表、重命名列、添加列、删除列、修改列的属性等。