6.2 ALTER 修改表结构

6.2 ALTER 修改表结构

本节摘要：ALTER 改已有表的结构——加列、删列、改类型、加约束。本节讲常见 ALTER 操作和改结构的风险。

学习目标

阅读完本节，你应当能够：

1. 用 ALTER 加列删列
2. 用 ALTER 改列类型
3. 用 ALTER 加约束
4. 理解改结构的风险和注意事项

概念脉络

一、ALTER 能做什么

建完表后需求会变，要改结构，就用 ALTER。常见操作：

• 加列
• 删列
• 改列名/类型
• 加/删约束
• 改表名

图 6-2 ALTER 操作

二、加列

ALTER TABLE Customers ADD Email VARCHAR(100);
-- 加带默认值的列
ALTER TABLE Customers ADD Status VARCHAR(20) DEFAULT 'active' NOT NULL;
​

三、删列

ALTER TABLE Customers DROP COLUMN Email;
​

删列会丢该列数据，且不可恢复（除非有备份），慎用。

四、改列类型

语法因 DBMS 而异：

-- MySQL
ALTER TABLE Customers MODIFY Age BIGINT;
-- PostgreSQL
ALTER TABLE Customers ALTER COLUMN Age TYPE BIGINT;
​

改类型可能要转换数据，转换失败会报错（如把字符串列改成 INT 但有非数字值）。

五、加约束

-- 加非空
ALTER TABLE Customers MODIFY FirstName VARCHAR(50) NOT NULL;
-- 加唯一
ALTER TABLE Customers ADD CONSTRAINT uk_email UNIQUE (Email);
-- 加外键
ALTER TABLE Orders ADD CONSTRAINT fk_customer
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);
​

加约束时如果已有数据违反约束会失败——比如加 UNIQUE 但已有重复值。

六、改结构的风险

• 锁表：大表加列/改类型可能长时间锁表，阻塞业务。
• 数据丢失：删列丢数据，改类型转换失败可能丢值。
• 约束失败：加约束时已有脏数据会失败。

生产改结构的安全做法：

1. 先在测试环境验证
2. 备份数据
3. 低峰执行
4. 用支持在线 DDL 的方式（如 MySQL 的 ALGORITHM=INPLACE）
5. 大表分批改

⚠️ 常见坑：生产环境直接对大表 ALTER，锁表几小时业务瘫痪。改结构前先测试、备份、低峰执行，能用在线 DDL 的用在线 DDL。

💡 关键直觉：ALTER 改的是结构，影响所有数据。大表改结构要考虑锁表和数据风险，生产环境务必先测试、备份、低峰执行。

要点串联

• ADD：加列、加约束。
• DROP：删列、删约束（丢数据，慎用）。
• MODIFY/ALTER：改列类型，语法因 DBMS 而异，转换失败可能丢值。
• 加约束：已有脏数据会失败，先清理再加。
• 风险：锁表、数据丢失、约束失败。生产先测试、备份、低峰、用在线 DDL。

下一节讲 DROP——删表删库，最危险的操作。

常见疑问

Q1：ALTER 加列时能指定位置吗？

能（MySQL 支持 FIRST 或 AFTER 列名），但位置只影响展示顺序，逻辑无影响。建议不纠结位置，需要控制顺序时显式写列名即可。PostgreSQL 不支持指定位置，列追加到末尾。

Q2：给已有数据的表加 NOT NULL 列会怎样？

会失败：已有行的新列没有值。解决：先加 NULL 列、填充默认值、再 ALTER 为 NOT NULL；或加列时直接带 DEFAULT（部分数据库支持 ADD COLUMN ... NOT NULL DEFAULT ...，MySQL 8 有优化）。顺序是：加列到填数据到收紧约束。

Q3：ALTER 改列类型有哪些风险？

1. 类型收缩导致数据截断或失败（INT 改 SMALLINT 溢出）；2) 大表锁表时间长；3) 索引可能需要重建。改类型前先查"该类型下已有数据的最大/最小/特殊值"，确认兼容再动手。

Q4：改列名会破坏什么？

所有引用该列的查询、ORM 映射、程序代码、存储过程、视图。生产环境改列名是高危操作，必须全库搜索引用、评估影响、按发布流程走。所以建表时列名要想清楚。

Q5：ALTER 能一次做多个操作吗？

MySQL 支持 ALTER TABLE ... ADD 列, MODIFY 列, DROP 列 一次多条（逗号分隔），减少锁表次数。建议把相关变更合并成一条 ALTER。

Q6：删除列后数据能找回吗？

不能。删列即删数据，且部分数据库不可逆。删列前务必确认该列无业务使用，必要时先备份整表。宁可不删，也别误删。

动手做一做

修改表结构的风险，建议用一次"完整演练"来体会。

准备一张练习表，插入若干行数据。

第一个练习：给表加一列，观察新列在已有数据上显示什么值，体会加列对存量数据的影响。

第二个练习：给刚才的列填上值，再把它改成不允许为空，观察这次修改能否成功，理解"先填数据再收紧约束"的顺序。

第三个练习：把某一列的类型改大，再把类型改小，观察改小发生在有超范围数据时会怎样，体会类型收缩的风险。

第四个练习：给表加一个唯一约束，观察如果表里已有重复值，加约束会有什么结果，理解"约束会检查存量数据"。

第五个练习：删掉一列，对比删除前后的表结构，记住删除列的不可逆性。

这五个练习覆盖了加列、改类型、加约束、删列四类典型操作，每一步都让你亲眼看到修改结构对已有数据的影响。生产环境改结构是高危操作，现在在测试库把风险都踩一遍，将来才懂得敬畏。

一句话记忆

改表结构操作的是整张表的骨架。加列要先想存量数据，改类型要防数据损坏，加约束会检查存量数据，删列不可逆。生产改结构前先测试、备份、低峰执行，这三条缺一不可。