MySQL: MySQL修改数据表结构完全指南
最后更新：2026-08-26
表结构不是一成不变的——业务变化时，ALTER TABLE 是你的救星。
本课系统讲解修改表结构的所有操作。
graph TB
A[ALTER TABLE 操作] --> B[ADD COLUMN<br/>添加字段]
A --> C[DROP COLUMN<br/>删除字段]
A --> D[MODIFY<br/>修改类型/约束]
A --> E[CHANGE<br/>重命名字段]
A --> F[RENAME<br/>修改表名]
A --> G[AUTO_INCREMENT<br/>自增设置]
A --> H[ADD/DROP CONSTRAINT<br/>约束管理]
1. 你将学到
- ALTER TABLE 添加/删除/修改字段
- 修改表名和字段名
- 修改字段类型和约束
- AUTO_INCREMENT 自增设置
- DEFAULT 默认值管理
2. 一个需求变更的真实故事
(1) 痛点：需求变了
产品经理说：
"用户表要加一个手机号字段，还要把用户名长度从 50 改成 100。"
如果不会 ALTER TABLE，只能删表重建——数据全丢。
(2) ALTER TABLE 的解法
SQL
-- 添加手机号字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 修改用户名长度
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;
收益： 不丢数据，秒级完成。
3. 添加字段
▶ 示例：ADD COLUMN
SQL
-- 添加单个字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 添加字段并指定位置
ALTER TABLE users ADD COLUMN avatar VARCHAR(255) AFTER username;
-- 添加到表的最前面
ALTER TABLE users ADD COLUMN uuid CHAR(36) FIRST;
-- 添加多个字段
ALTER TABLE users
ADD COLUMN address VARCHAR(200),
ADD COLUMN city VARCHAR(50),
ADD COLUMN country VARCHAR(50) DEFAULT 'China';
输出：
TEXT
📖 仅展示
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
4. 删除字段
▶ 示例：DROP COLUMN
SQL
-- 删除单个字段
ALTER TABLE users DROP COLUMN phone;
-- 删除多个字段
ALTER TABLE users DROP COLUMN address, DROP COLUMN city;
⚠️ 注意： 删除字段会永久丢失该列数据，无法恢复！
5. 修改字段
(1) MODIFY vs CHANGE
| 命令 | 可改内容 | 语法 |
|---|---|---|
| MODIFY | 类型、约束、位置 | MODIFY column new_type [constraints] |
| CHANGE | 名称、类型、约束 | CHANGE old_name new_name new_type [constraints] |
▶ 示例：MODIFY 修改类型
SQL
-- 修改字段类型
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;
-- 修改字段类型并移到指定位置
ALTER TABLE users MODIFY COLUMN email VARCHAR(150) FIRST;
-- 修改字段默认值
ALTER TABLE users MODIFY COLUMN status VARCHAR(20) DEFAULT 'active';
▶ 示例：CHANGE 重命名字段
SQL
-- 重命名字段
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(100) NOT NULL;
-- 重命名并修改类型
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30);
6. 修改表名
▶ 示例：RENAME
SQL
-- 方法1：RENAME TO
ALTER TABLE users RENAME TO customers;
-- 方法2：RENAME TABLE
RENAME TABLE users TO customers;
-- 同时重命名多个表
RENAME TABLE
old_users TO users,
old_orders TO orders;
7. AUTO_INCREMENT 设置
▶ 示例：自增配置
SQL
-- 创建带自增的表
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
);
-- 插入数据（id 自动生成）
INSERT INTO products (name) VALUES ('iPhone');
INSERT INTO products (name) VALUES ('MacBook');
-- 查看当前自增值
SHOW CREATE TABLE products\G
-- 修改自增起始值
ALTER TABLE products AUTO_INCREMENT = 1000;
-- 插入新数据（id 从 1000 开始）
INSERT INTO products (name) VALUES ('iPad');
输出：
TEXT
📖 仅展示
+----+---------+
| id | name |
+----+---------+
| 1 | iPhone |
| 2 | MacBook |
| 1000| iPad |
+----+---------+
8. DEFAULT 值管理
▶ 示例：修改默认值
SQL
-- 添加默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
-- 删除默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;
-- 修改 created_at 的默认值
ALTER TABLE orders ALTER COLUMN order_date SET DEFAULT (CURRENT_DATE);
9. 添加/删除约束
▶ 示例：约束管理
SQL
-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);
-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;
-- 添加唯一约束
ALTER TABLE users ADD UNIQUE (email);
-- 删除唯一约束
ALTER TABLE users DROP INDEX email;
-- 添加外键
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);
-- 删除外键
ALTER TABLE orders DROP FOREIGN KEY fk_customer;
-- 添加 CHECK 约束
ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18);
-- 删除 CHECK 约束
ALTER TABLE employees DROP CHECK chk_age;
10. 批量修改实战
▶ 示例：一次执行多个修改
SQL
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) AFTER email,
ADD COLUMN avatar VARCHAR(255),
MODIFY COLUMN username VARCHAR(150) NOT NULL,
DROP COLUMN temp_field,
ADD UNIQUE (phone);
❓ 常见问题
Q ALTER TABLE 会锁表吗？
A MySQL 8.0 大多数 ALTER TABLE 操作支持 Online DDL，不会锁表。但修改主键、字段类型等操作可能锁表。
Q 修改字段会丢数据吗？
A MODIFY/CHANGE 修改类型时，如果新类型无法存储原数据，会截断或报错。改名不会丢数据。
Q 可以撤销 ALTER TABLE 吗？
A 不能。ALTER TABLE 是 DDL 操作，执行后自动提交，无法 ROLLBACK。操作前建议备份。
Q 表太大了怎么修改？
A 大表（千万级）ALTER TABLE 可能很慢。推荐使用 pt-online-schema-change 或 gh-ost 工具在线改表。
📖 小节
- ADD COLUMN 添加字段，可指定位置（FIRST/AFTER）
- DROP COLUMN 删除字段，数据永久丢失
- MODIFY 修改字段类型和约束，CHANGE 可同时改名
- RENAME TABLE 修改表名
- AUTO_INCREMENT 控制自增起始值
- ALTER COLUMN SET/DROP DEFAULT 管理默认值
- ADD/DROP CONSTRAINT 管理主键、外键、唯一、CHECK 约束
📝 作业
-
基础题（难度⭐）：给
users表添加phone字段（VARCHAR(20)），放在email字段后面。 -
进阶题（难度⭐⭐）：创建
orders表后，修改amount字段从 INT 改为 DECIMAL(10,2)，添加status字段默认值为 'pending'。 -
挑战题（难度⭐⭐⭐）：设计一个表结构演进方案：从 v1 到 v2 需要添加 3 个字段、删除 1 个字段、修改 2 个字段类型，写出完整的 ALTER TABLE 语句。
