MySQL 修改表结构

Begindev 编辑部 March 30, 2026

数据库的表结构并不是一成不变的。随着业务的发展，你经常需要对现有的表进行演进，以适应新的数据需求或优化现有的设计。MySQL 提供了强大的命令来修改表模式（Schema），允许你在建表之后添加、修改或删除列。

这些操作都是通过 ALTER TABLE 语句来完成的，它是一个非常核心的数据定义语言 (DDL) 命令。

1. 添加数据列

当一张表需要存储额外的属性时，添加新列是最常见的需求。你可以使用 ALTER TABLE ... ADD COLUMN 语句来实现。在添加列时，你需要指定列名、数据类型以及任何约束条件，这与你最初创建表时的做法非常相似。

1.1 添加列的语法

ALTER TABLE table_name
ADD COLUMN column_name DATATYPE [column_constraint];

table_name：需要修改的表名。
column_name：要添加的新列的名称。
DATATYPE：新列的数据类型（例如 VARCHAR(255), INT, DECIMAL(10,2)）。正如此前关于数据类型的章节所强调的，选择正确的数据类型对数据完整性和性能至关重要。
column_constraint：可选的约束条件，例如 NOT NULL、DEFAULT 默认值、UNIQUE 唯一约束，或者使用 AFTER 现有列名 / FIRST 来指定新列插入的位置。

1.2 添加列的代码示例

假设我们有一个 Customers 表，目前只存储了 customer_id、first_name 和 last_name。

-- 初始的 Customers 表结构
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

基础添加列
假设我们需要为每位客户存储电子邮箱地址。

ALTER TABLE Customers
ADD COLUMN email VARCHAR(100) UNIQUE;

这条命令添加了一个类型为 VARCHAR(100) 的 email 列。UNIQUE 约束确保没有两个客户可以拥有相同的邮箱。由于没有指定 NOT NULL，表中现有的数据行在这个新列上将被填充为 NULL（空值），新插入的数据如果没有提供邮箱也会默认为 NULL。

添加带有默认值的列
现在，我们想追踪客户账号的创建时间。

ALTER TABLE Customers
ADD COLUMN registration_date DATE DEFAULT (CURRENT_DATE);

这添加了一个 DATE 类型的 registration_date 列。DEFAULT (CURRENT_DATE) 意味着如果在 INSERT 插入操作中没有提供该值，MySQL 将自动用当前日期填充它。重点注意：对于表中已经存在的历史数据，这个新列会被初始化为执行 ALTER TABLE 操作当天的日期，而不是他们真实的注册日期。

添加 NOT NULL 列
如果新列必须有值（例如状态字段），并且你想立即强制执行此规则：

ALTER TABLE Customers
ADD COLUMN customer_status VARCHAR(20) NOT NULL DEFAULT 'Active';

这里，customer_status 作为 VARCHAR(20) 被添加，并且是 NOT NULL（非空）。为了处理表中已经存在的旧数据，我们必须提供一个 DEFAULT 'Active'。如果没有这个默认值，由于现有行无法满足 NOT NULL 的要求，这条 ALTER TABLE 语句将会报错失败。

指定列的插入位置
你可以使用 FIRST（第一列）或 AFTER existing_column（在某列之后）来控制新列的位置。

ALTER TABLE Customers
ADD COLUMN phone_number VARCHAR(20) AFTER last_name;

这会将 phone_number 紧紧添加到 last_name 列的后面。

ALTER TABLE Customers
ADD COLUMN customer_priority INT DEFAULT 0 FIRST;

这会将 customer_priority 作为表的第一列添加。虽然列的顺序在 MySQL 中通常不影响功能或查询性能，但在图形化工具（如 MySQL Workbench）中可以提高可读性。

2. 修改现有列

列创建后，其属性可能需要调整。你可以更改列的数据类型、约束（如 NOT NULL 或 DEFAULT），甚至更改它的名字。这需要用到 ALTER TABLE ... MODIFY COLUMN 或 ALTER TABLE ... CHANGE COLUMN 语句。

2.1 修改列的语法

MODIFY COLUMN：用于更改列的定义（数据类型、约束），但不改变列名。

ALTER TABLE table_name
MODIFY COLUMN column_name DATATYPE [column_constraint];

CHANGE COLUMN：用于更改列的定义以及它的名字。这要求你同时写出旧列名和新列名。

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name DATATYPE [column_constraint];

2.2 修改列的代码示例

继续使用刚才扩展后的 Customers 表。

更改数据类型和约束（使用 MODIFY）
假设 email 列最初是 VARCHAR(100) UNIQUE，但需求变了，邮箱地址偶尔可能会超过 100 个字符，并且我们现在要求它必须为 NOT NULL。

ALTER TABLE Customers
MODIFY COLUMN email VARCHAR(255) UNIQUE NOT NULL;

这条命令将 email 的长度增加到 255，并附加了 NOT NULL 约束。关键提醒：如果在修改之前，表中已经存在 email 为 NULL 的数据，这条语句会执行失败。你必须先通过 UPDATE 语句把那些空值填上数据，然后再执行修改。

更改列名（使用 CHANGE）
为了表意更清晰，我们要把 customer_status 改名为 account_status。

ALTER TABLE Customers
CHANGE COLUMN customer_status account_status VARCHAR(20) NOT NULL DEFAULT 'Active';

这里将列名进行了更改。请注意，即使你只是想改个名字，你也必须完整地重新写一遍它的数据类型和所有约束。如果不写，该列将丢失以前的定义。

修改默认值 (DEFAULT)
如果想把默认优先级从 0 改为 1：

ALTER TABLE Customers
ALTER COLUMN customer_priority SET DEFAULT 1;

这只影响未来插入的新数据，现有数据的值保持不变。

删除默认值
如果某个列不再需要默认值：

ALTER TABLE Customers
ALTER COLUMN registration_date DROP DEFAULT;

删除了默认值后，未来如果再执行 INSERT 且没有提供日期，要么会因为 NOT NULL 报错，要么就直接存入 NULL。

2.3 修改列的注意事项

数据丢失风险：将数据类型改为更宽松的类型（如 INT 到 BIGINT，VARCHAR(10) 到 VARCHAR(100)）通常是安全的。但是，改为更严格的类型（如 VARCHAR(100) 改为 VARCHAR(10)）可能会导致数据被截断或报错。操作前务必备份数据。
性能影响：在超大表上执行 ALTER TABLE 非常耗时，甚至会锁表，导致应用在此期间无法响应。现代 MySQL 版本 (8.0+) 在很多场景下支持在线 DDL (Online DDL)，但在生产环境依然需要谨慎计划。
依赖关系：如果一列被用作索引、外键或视图的一部分，修改它可能会影响这些关联对象。MySQL 通常会自动处理，或者在可能破坏结构时抛出错误。

3. 删除数据列

当确认不再需要某一列时，可以使用 ALTER TABLE ... DROP COLUMN 将其移除。这是一个破坏性操作，会永久删除该列及其内部的所有数据。

3.1 删除列的语法

ALTER TABLE table_name
DROP COLUMN column_name;

3.2 删除列的代码示例

基础删除
如果不再需要电话号码列：

ALTER TABLE Customers
DROP COLUMN phone_number;

删除带有依赖关系的列
如果该列被外键约束引用，通常需要先删除外键。如果该列有索引，MySQL 一般会自动跟着删除，但在某些复杂场景下可能需要你手动先干掉索引：

-- 假设 'email' 上有一个名为 'idx_email' 的索引
-- 如果系统没有自动处理，你可能需要先执行：
-- ALTER TABLE Customers DROP INDEX idx_email;

ALTER TABLE Customers
DROP COLUMN email;

3.3 删除列的注意事项

不可逆的数据丢失：列被删除后，里面的数据就永远消失了。
对应用的影响：后端代码如果还在通过 SQL 查询或写入这个被删掉的列，应用就会崩溃。上线前务必和开发团队对齐。

4. 综合实战演练

让我们通过一个 Products（产品）表的完整场景来巩固知识。

初始建表与数据

CREATE TABLE Products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0
);

INSERT INTO Products (product_name, price, stock_quantity) VALUES
('Laptop', 1200.00, 50),
('Mouse', 25.50, 200),
('Keyboard', 75.00, 100);

SELECT * FROM Products;

输出：

+------------+--------------+---------+----------------+
| product_id | product_name | price   | stock_quantity |
+------------+--------------+---------+----------------+
|          1 | Laptop       | 1200.00 |             50 |
|          2 | Mouse        |   25.50 |            200 |
|          3 | Keyboard     |   75.00 |            100 |
+------------+--------------+---------+----------------+

场景 1：添加描述列 (允许为空)

ALTER TABLE Products
ADD COLUMN description TEXT;

SELECT product_id, product_name, description FROM Products;

输出（注意旧数据的 description 是 NULL）：

+------------+--------------+-------------+
| product_id | product_name | description |
+------------+--------------+-------------+
|          1 | Laptop       | NULL        |
|          2 | Mouse        | NULL        |
|          3 | Keyboard     | NULL        |
+------------+--------------+-------------+

场景 2：添加非空且带有默认值的列
添加布尔值 is_available（是否上架）。

ALTER TABLE Products
ADD COLUMN is_available BOOLEAN NOT NULL DEFAULT TRUE;

SELECT product_id, product_name, is_available FROM Products;

输出（旧数据自动获得 TRUE(1)）：

+------------+--------------+--------------+
| product_id | product_name | is_available |
+------------+--------------+--------------+
|          1 | Laptop       |            1 |
|          2 | Mouse        |            1 |
|          3 | Keyboard     |            1 |
+------------+--------------+--------------+

(注：MySQL 中 BOOLEAN 底层是 TINYINT(1)，1 代表 TRUE，0 代表 FALSE。)

场景 3：修改列的数据类型 (MODIFY)
产品名字段太短了，扩大到 255。

ALTER TABLE Products
MODIFY COLUMN product_name VARCHAR(255) NOT NULL;

(数据本身不会变，但你可以用 DESCRIBE Products; 看到结构的改变。)

场景 4：重命名列 (CHANGE)
把 stock_quantity 换个更准确的名字 current_stock。

ALTER TABLE Products
CHANGE COLUMN stock_quantity current_stock INT NOT NULL DEFAULT 0;`

场景 5：删除列 (DROP)
觉得 description 没用了。

ALTER TABLE Products
DROP COLUMN description;