使用 MySQL ALTER TABLE 语句向表在添加列

在本文中,您将了解如何使用 MySQL ALTER TABLE ... ADD 语句向表中添加一个或多个列。

有时候,您需要向一个已有的表中增加额外的列以保存一些必要的信息。

假设,您有一个用户表,它用来存储用户名,邮件,密码等信息。但是随着系统的需求的变动,您需要在用户表中保存手机号码。要做到这些,您不需要重新创建一个表,只需要在现有的表上添加一个列即可。

MySQL 允许您使用 ALTER TABLE 语句来修改一个现有的表。要向一个表中添加一列或多列,请使用 ALTER TABLE ... ADD COLUMN 语句。

MySQL ADD COLUMN 语法

以下是 MySQL ALTER TABLE ... ADD COLUMN 语句的语法:

ALTER TABLE table_name
ADD [COLUMN] column_definition [FIRST|AFTER existing_column]
[, ADD [COLUMN] column_definition [FIRST|AFTER existing_column]];

在这个语法中:

  • 位于 ALTER TABLE 关键字之后的 table_name 是向其中添加列的表名。
  • ADD [COLUMN] 关键字之后是列的定义。您可以省略 COLUMN 关键字。
  • 您需要在列定义 column_definition 中指定列名,列的类型等信息。
  • 默认情况下,新列将被添加为表的最后一列。您可以通过使用 FIRST 关键字将列添加为表的第一列,或者使用 AFTER existing_column 将新列添加到现有列 existing_column 的后面。
  • 如果您需要在一个语句中添加多个列,请使用多个使用逗号分隔的 ADD COLUMN 子句。

确定列是否存在

在向表中添加一个列之前,您可以需要首先确定此表中是否存在同名的列。

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

DESC 语句的语法如下:

DESC table_name;

SHOW COLUMNS 语句的语法如下:

SHOW [FULL] COLUMNS FROM table_name [LIKE pattern]

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

MySQL ADD COLUMN 实例

这个实例演示了如何使用 ALTER TABLE ... ADD COLUMN 语句向表中添加一个或两个列。

假设,我们有一个用户表,其中有 ID 和 用户名 两列。

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

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

如果您没有 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    |                |
+-------+--------------+------+-----+---------+----------------+

向用户表中插入一行数据,如下;

INSERT INTO user (name) values ('Tim');

使用 MySQL ADD COLUMN 向表中添加一列

要想在 user 表保存用户的年龄,你需要使用以下语句向 user 表中添加 age 列,

ALTER TABLE user
ADD COLUMN age INT NOT NULL DEFAULT 20;

这里,我们添加了一个 age 列,它的类型是 INT,并且是非空列,默认值是 20

注意,如果您没有通过 DEFAULT 关键字指定默认值,那么 MySQL 会为 INT 类型的非空列使用 0 作为默认值。

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

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

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

SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Tim  |  20 |
+----+------+-----+

使用 MySQL ADD COLUMN 向表中添加二列

随着系统的发展,您可能需要在用户表中保存用户的 email 和手机号列信息。这几乎已经是用户的必备信息。

要向 user 表中添加 email 和手机号列,请使用以下语句:

ALTER TABLE user
ADD COLUMN email VARCHAR(255) NOT NULL,
ADD COLUMN phone VARCHAR(255) NOT NULL;

这里,我们向 user 表中添加了两个列: email 用于电子邮件地址 和 phone 用于手机号码。

让我们检查 user 表的行:

SELECT * FROM user;
+----+------+-----+-------+-------+
| id | name | age | email | phone |
+----+------+-----+-------+-------+
|  1 | Tim  |  20 |       |       |
+----+------+-----+-------+-------+

我们发现, MySQL 为非空的 VARCHAR 列使用空字符串作为默认值。

结论

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