08｜修改已有表：字段、索引与 Expand–Migrate–Contract

创建空表通常容易；修改已经有数据、正在被程序读写的表才是真正高风险的工作。本章以 admin_user 为例，建立一套可复用流程。

1. 先区分“数据库变更”和“代码变更”

假设要给管理员增加所属部门：

ALTER TABLE admin_user
ADD COLUMN department_id BIGINT UNSIGNED NULL;

执行 SQL 后，Sequelize Model 不会自动更新；修改 Model 后，MySQL 表也不会自动更新。一次完整需求至少涉及：

Migration → 真实数据库结构 → Sequelize Model → Service 查询/写入 → DTO/API

其中任何两层不一致，都可能产生“类型检查通过，但运行时报 Unknown column”一类问题。

2. 每次改表前的固定步骤

2.1 确认当前结构和数据量

SHOW CREATE TABLE admin_user;
SHOW INDEX FROM admin_user;
SELECT COUNT(*) FROM admin_user;

不要只看仓库中的旧 SQL 文件；运行中的数据库才告诉你环境实际状态。

2.2 搜索代码中的读写位置

增加、重命名或删除字段前，搜索 Model、Service、DTO、脚本、报表：

rg "department_id|旧字段名" .

还要检查不在主 Web 服务中的定时任务、CLI 脚本和旧版本实例。

2.3 评估兼容窗口

部署时经常会短暂同时运行旧代码和新代码。Migration 应尽量满足：

• 旧代码面对新结构仍可运行；
• 新代码在所有数据库升级前不会读取不存在的列；
• 回滚应用代码时，旧字段暂时还存在。

3. Expand–Migrate–Contract

这是修改线上结构最值得养成的习惯：

Expand：增加兼容的新结构
Migrate：发布代码并回填/迁移数据
Contract：确认旧依赖消失后清理旧结构

3.1 示例：增加非空 department_id

直接写下面这条通常不够安全：

ALTER TABLE admin_user
ADD COLUMN department_id BIGINT UNSIGNED NOT NULL;

已有行没有部门值；旧版本代码也不会写入该列。更稳妥的多阶段流程如下。

阶段 A：Expand

Migration 202609070002_add_admin_department.up.sql：

ALTER TABLE admin_user
ADD COLUMN department_id BIGINT UNSIGNED NULL;

CREATE INDEX idx_admin_user_department_id
ON admin_user (department_id);

同时发布能读写 department_id、但仍接受 NULL 的代码：

declare department_id: string | null;

阶段 B：Migrate

先统计待回填量：

SELECT COUNT(*)
FROM admin_user
WHERE department_id IS NULL;

小表可一次回填；大表应按主键分批，避免超长事务：

UPDATE admin_user
SET department_id = 1
WHERE department_id IS NULL
  AND id > 0
  AND id <= 10000;

每批提交，记录最后一个 ID。回填程序应可重复执行，即“已经正确的行再次执行不会被破坏”。

验证：

SELECT COUNT(*) AS missing_count
FROM admin_user
WHERE department_id IS NULL;

阶段 C：Contract

确认所有应用版本都会写入部门，且 missing_count = 0 后，再用新 Migration 收紧约束：

ALTER TABLE admin_user
MODIFY COLUMN department_id BIGINT UNSIGNED NOT NULL;

如果有 department 表，再增加外键。不要把所有步骤强塞进一次不可观察的发布。

4. 常见结构变更怎么做

4.1 增加允许 NULL 的字段

通常最容易保持向后兼容：

ALTER TABLE admin_user
ADD COLUMN avatar_url VARCHAR(2048) NULL;

Model 同步：

declare avatar_url: string | null;

注意：URL 长度和内容合法性仍应在请求/业务边界校验，数据库长度只是最后防线。

4.2 增加带默认值的字段

ALTER TABLE admin_user
ADD COLUMN must_change_password TINYINT(1) UNSIGNED NOT NULL DEFAULT 0;

Model 应同步 allowNull: false 和 defaultValue: false（或项目选择的数字类型）。必须明确默认值对历史行和未来行分别表示什么，不能为了绕过 NOT NULL 随便填 0。

4.3 修改字段类型

例如缩短 VARCHAR(100) 到 VARCHAR(50) 前先找出不兼容数据：

SELECT id, CHAR_LENGTH(display_name) AS name_length
FROM admin_user
WHERE CHAR_LENGTH(display_name) > 50;

先清洗或决定截断策略，再 ALTER TABLE。隐式截断可能丢数据，严格 SQL 模式下可能直接失败。

4.4 重命名字段

“一步重命名”会让旧代码立刻报错。线上更适合双字段过渡：

1. 增加新字段；
2. 新代码双写旧字段和新字段；
3. 回填新字段；
4. 读路径切到新字段；
5. 停止写旧字段；
6. 等待回滚窗口结束后删除旧字段。

双写需要定义失败语义，最好在同一数据库事务内写入，或把新值由数据库/单一 Service 维护，避免两列漂移。

4.5 删除字段

正确顺序通常是“代码先停止依赖，字段后删除”：

停止读字段 → 停止写字段 → 观察一个回滚窗口 → 备份/归档 → DROP COLUMN

ALTER TABLE admin_user
DROP COLUMN obsolete_column;

危险提示：DROP COLUMN 是数据删除，不应仅因为 Model 已经不用它就直接执行。执行前需要备份、依赖搜索和明确回滚方案。

5. 增加、修改和删除索引

5.1 增加普通索引

假设经常查询临时锁定中的管理员：

SELECT id
FROM admin_user
WHERE locked_until > CURRENT_TIMESTAMP(3);

可以评估：

CREATE INDEX idx_admin_user_locked_until
ON admin_user (locked_until);

先用 EXPLAIN 对比，并考虑字段选择性和写入成本。索引不是越多越好；每次 INSERT、UPDATE 都要维护它。

5.2 增加唯一索引

创建前先检查历史重复：

SELECT email, COUNT(*) AS duplicate_count
FROM admin_user
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

清理重复后再执行：

CREATE UNIQUE INDEX uk_admin_user_email
ON admin_user (email);

MySQL 唯一索引通常允许多个 NULL。这与 email 可选的设计匹配，但不要误以为 NULL 彼此相等。

业务层“先查是否存在”只能改善提示，不能替代唯一索引：两个并发请求可能同时查到不存在，再同时插入。

5.3 删除索引

DROP INDEX idx_admin_user_locked_until ON admin_user;

删除前确认：

• 是否被高频查询使用；
• 是否支撑外键；
• 是否存在功能等价的复合索引；
• EXPLAIN 在真实数据规模下是否恶化。

不要只因为“看起来重复”就删索引；复合索引的最左前缀规则会影响它能否替代单列索引。

6. 增加外键的安全流程

给审计表增加外键前先找孤儿数据：

SELECT a.id, a.admin_user_id
FROM admin_login_audit AS a
LEFT JOIN admin_user AS u ON u.id = a.admin_user_id
WHERE a.admin_user_id IS NOT NULL
  AND u.id IS NULL;

确认结果为空后：

ALTER TABLE admin_login_audit
ADD CONSTRAINT fk_admin_login_audit_admin_user
FOREIGN KEY (admin_user_id)
REFERENCES admin_user (id)
ON DELETE SET NULL;

外键会提供数据库级一致性，但也会影响删除顺序、并发锁和导入流程。ON DELETE 行为必须来自业务决定。

7. Sequelize Migration 的表达方式

如果未来采用 sequelize-cli 或 Umzug，可以用 queryInterface：

import type { QueryInterface } from "sequelize";
import { DataTypes } from "sequelize";

export async function up(query_interface: QueryInterface): Promise<void> {
  await query_interface.addColumn("admin_user", "department_id", {
    type: DataTypes.BIGINT.UNSIGNED,
    allowNull: true,
  });

  await query_interface.addIndex("admin_user", ["department_id"], {
    name: "idx_admin_user_department_id",
  });
}

但不要把两个 DDL 都想当然地包进“绝对原子”的事务。MySQL 8 的许多 DDL 会隐式提交或使用原子 DDL 机制，其事务语义与普通 DML 不同。应查对应 MySQL 版本文档，并设计可恢复、可重复检查的步骤。

SQL-first 也可继续写原生 SQL，重点仍是版本、执行记录和验证。

8. 大表与锁表风险

ALTER TABLE 的影响取决于 MySQL 版本、存储引擎、操作类型和表规模。不要仅凭语句短就认为执行快。

发布前至少确认：

SELECT TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'admin_user';

需要进一步评估：

• 是否支持 ALGORITHM=INSTANT / INPLACE；
• 是否会获取 metadata lock；
• 是否有长事务阻挡 DDL；
• 是否有执行时长和磁盘空间预算；
• 失败后能否安全重试。

小练习项目可以直接观察，但要养成“先在等量级测试数据上演练”的习惯。

9. Migration 文件的纪律

已经在共享环境执行的 Migration 不应原地改写。正确做法是追加一个新 Migration：

错误：修改 202609070002 的内容，假装历史没发生
正确：新增 202609070003_fix_department_index

否则新环境执行的是新文件，旧环境保留旧结果，两者会永久漂移。

每个 Migration 至少记录：

• 目的；
• 前置数据检查；
• up；
• 验证 SQL；
• 回滚或前滚修复策略；
• 可能的锁和执行时间风险。

10. 初学者常犯的错误

1. 新增 NOT NULL 列却没有考虑历史行和旧代码。
2. Model 先加字段，但数据库尚未部署。
3. 直接重命名/删除正在被旧实例读取的列。
4. 创建唯一索引前不检查重复数据。
5. 一次性回填全表，制造长事务和大量锁。
6. 把 DDL 当成与普通 DML 完全相同的可回滚事务。
7. 修改已执行 Migration，导致环境漂移。
8. 认为增加索引一定提升所有查询，忽略写成本和索引选择性。

11. 练习

1. 设计给 admin_user 增加可选 phone_number 的两阶段 Migration；说明唯一索引创建前如何查重。
2. 把 display_name 从 100 缩到 60，写出数据兼容性检查 SQL。
3. 假设要把 ip_address 改名为 client_ip_address，设计双写和回填发布顺序。
4. 为 admin_login_audit 删除一个索引前，用两条典型查询做 EXPLAIN 对比。
5. 故意开一个未提交事务，再执行相关 DDL，观察 metadata lock，并在本地测试库安全结束实验。

12. 复盘清单

• [ ] 是否先检查了真实结构、数据量和历史脏数据？
• [ ] 是否考虑旧代码与新代码同时运行？
• [ ] 非空、唯一、外键约束是否分阶段增加？
• [ ] 回填是否分批、幂等、可观察？
• [ ] 是否追加新 Migration，而非篡改已执行历史？
• [ ] 是否评估索引、锁表和磁盘开销？
• [ ] Model、TypeScript 类型和 DTO 是否同步？
• [ ] 是否有验证 SQL、备份和回滚/前滚方案？

下一章进入 DML 事务：当多个读写操作必须作为一个整体完成时，怎样正确使用 Sequelize Transaction API。