07｜创建新表：把手工 SQL 升级为版本化 Migration

本章解决一个很实际的问题：你已经会先写建表 SQL，再通过 MySQL CLI 或 DBeaver 执行；怎样保留这个优点，同时让每个环境都能可靠复现数据库结构？

结论先说：继续 SQL-first 完全可以，但 SQL 文件应从“个人操作记录”升级为“按顺序执行、进入 Git、可验证的 Migration”。

1. 当前方式缺少的不是 SQL，而是变更历史

例如你写好 models/admin_user.sql，然后在本机 DBeaver 执行。数据库确实创建成功，但 Git 不能回答：

测试环境执行过吗？
生产环境执行的是不是同一版？
两个月后，新同事怎样从空库重建？
SQL 执行到一半失败，该从哪里继续？
某个字段究竟在哪次发布中加入？

Migration 的本质就是：给每次结构变更一个唯一且有顺序的文件，并记录它是否已执行。 它不是某个工具的专利。

2. 案例：设计 `admin_login_audit`

需求：每次管理员登录尝试产生一条审计记录，后续要支持：

按管理员和时间倒序查询；
按尝试用户名和时间查询；
管理员被删除后仍保留历史记录；
记录当时输入的用户名，但绝不记录密码和 Access Token。

本章采用以下明确假设：login_result=1 表示成功、2 表示凭据错误、3 表示账号禁用、4 表示账号锁定；IP 可能因代理配置或客户端缺失而拿不到，所以允许 NULL；所有业务时间遵循项目统一时区策略并保存毫秒。

本教程要求 MySQL 8.0.16 或更高版本，因为更早的 MySQL 8.0 会解析但忽略 CHECK 约束。可先执行 SELECT VERSION() 确认练习环境。

字段	类型	NULL	默认值	含义与选择理由
`id`	`BIGINT UNSIGNED`	否	自增	长期增长的审计记录主键；TS 中按 `string` 读取
`admin_user_id`	`BIGINT UNSIGNED`	是	`NULL`	与管理员主键类型完全一致；账号不存在或被删除后可以为空
`attempted_username`	`VARCHAR(50)`	否	无	保存本次输入快照；长度与当前管理员用户名产品边界一致
`login_result`	`TINYINT UNSIGNED`	否	无	小型状态值；必须由调用方明确提供，不用默认值掩盖漏传
`ip_address`	`VARCHAR(45)`	是	`NULL`	容纳 IPv4/IPv6 文本；缺失不使用空字符串冒充
`created_at`	`DATETIME(3)`	否	当前时间	记录尝试发生时间并保留毫秒

先从查询倒推索引，再写表：

CREATE TABLE admin_login_audit (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '审计记录主键',
    admin_user_id BIGINT UNSIGNED NULL COMMENT '管理员 ID，账号不存在或删除后为空',
    attempted_username VARCHAR(50) NOT NULL COMMENT '本次尝试输入的用户名快照',
    login_result TINYINT UNSIGNED NOT NULL COMMENT '结果：1 成功，2 凭据错误，3 账号禁用，4 账号锁定',
    ip_address VARCHAR(45) NULL COMMENT '客户端 IPv4 或 IPv6 地址',
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '尝试发生时间',
    PRIMARY KEY (id),
    KEY idx_admin_login_audit_admin_created
        (admin_user_id, created_at, id),
    KEY idx_admin_login_audit_username_created
        (attempted_username, created_at, id),
    CONSTRAINT fk_admin_login_audit_admin_user
        FOREIGN KEY (admin_user_id)
        REFERENCES admin_user (id)
        ON UPDATE RESTRICT
        ON DELETE SET NULL,
    CONSTRAINT chk_admin_login_audit_result
        CHECK (login_result IN (1, 2, 3, 4))
) ENGINE = InnoDB
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci
  COMMENT = '管理员登录审计';

设计上的几个关键点：

外键两端必须都是 BIGINT UNSIGNED，类型、符号要一致。
admin_user_id 允许 NULL，才能配合 ON DELETE SET NULL。
保留 attempted_username，即使账号不存在或后来改名，仍能审计当时发生了什么。
ip_address 用字符串是为了同时容纳 IPv4、IPv6；不要用 VARCHAR(15)。
login_result 没有默认值，因为每条审计都必须明确表明具体结果。
两个复合索引分别服务“按管理员”和“按尝试用户名”的时间线查询；id 为同毫秒记录提供稳定排序。索引来自查询条件，不是“每个字段都加一个索引”。

危险提示：审计表不要保存明文密码、password_hash、JWT、Cookie 或完整授权头。日志越持久，敏感信息泄露的影响越大。

3. 推荐的创建新表流程

第一步：写清业务规则和查询

不要一上来猜字段。先写出核心查询：

SELECT *
FROM admin_login_audit
WHERE admin_user_id = ?
ORDER BY created_at DESC, id DESC
LIMIT ?;

再决定 (admin_user_id, created_at) 索引。索引是为查询服务的。

第二步：创建不可重复命名的 Migration

可先使用最简单的目录：

migrations/
  202609070001_create_admin_login_audit.up.sql
  202609070001_create_admin_login_audit.down.sql

时间戳或严格递增编号决定执行顺序。团队中同一编号不能重复。

up.sql 放上面的 CREATE TABLE；down.sql 可以写：

DROP TABLE admin_login_audit;

但 down 只是开发和发布回滚方案的一部分，不代表生产环境可以随意删表。

危险提示：DROP TABLE 会删除数据。生产回滚通常优先回滚应用代码，是否执行破坏性 down 必须单独评估和备份。

第三步：在干净的本地测试库执行

MySQL CLI 示例：

mysql -u root -p sakila < migrations/202609070001_create_admin_login_audit.up.sql

也可以在 DBeaver 中打开同一个版本化文件执行。重点不是禁止 GUI，而是不要只把 SQL 留在 SQL Console 历史中。

执行后验证：

SHOW CREATE TABLE admin_login_audit;
SHOW INDEX FROM admin_login_audit;
SELECT *
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND TABLE_NAME = 'admin_login_audit';

CREATE TABLE 成功不等于设计正确；必须核对列、默认值、索引和外键。

第四步：同步 Sequelize Model

数据库结构和 Model 是两个需要同步维护的事实层：

export class AdminLoginAudit extends Model<
  InferAttributes<AdminLoginAudit>,
  InferCreationAttributes<AdminLoginAudit>
> {
  declare id: CreationOptional<string>;
  declare admin_user_id: string | null;
  declare attempted_username: string;
  declare login_result: number;
  declare ip_address: string | null;
  declare created_at: CreationOptional<Date>;
}

映射重点：

MySQL BIGINT 在本项目中用 string，延续 bigNumberStrings: true。
SQL NULL 对应 T | null。
自动递增和数据库默认值字段使用 CreationOptional<T>。
属性和列都遵循项目约定的 snake_case。

本章只展示方向，不要求现在创建该 Model；后面的关联章节再完整实现。

第五步：做最小读写验证

至少验证：

INSERT INTO admin_login_audit (
    admin_user_id,
    attempted_username,
    login_result
) VALUES (1, 'admin', 2);

SELECT id, admin_user_id, attempted_username, login_result, created_at
FROM admin_login_audit
WHERE admin_user_id = 1
ORDER BY created_at DESC, id DESC;

还要故意写入非法 login_result = 9，确认 CHECK 真正生效。

第六步：Migration、Model、业务代码一起评审和提交

评审者应能在一次变更中看到：

为什么加表 → 表如何约束数据 → Model 如何映射 → 代码如何使用 → 怎样验证

部署顺序通常是先执行向后兼容的 Migration，再发布依赖新表的代码。

4. 三种数据库结构管理方式如何选择

4.1 版本化 SQL Migration

优点：

最贴近你已有的 SQL-first 学习方式；
能完整使用 MySQL 的 CHECK、索引、字符集和在线 DDL 选项；
DBA 和 DBeaver 都容易审查。

代价：

需要自己提供执行器或规范；
参数化、多数据库兼容能力弱；
如果只手工执行，仍然无法自动记录状态。

当前项目最平滑的下一步是：先版本化 SQL，再写一个很小的执行流程或以后引入 Umzug。

4.2 `sequelize-cli` Migration

它可以生成 JS Migration，并用 queryInterface.createTable()、addColumn() 等执行。优点是生态成熟、有命令和执行记录；缺点是额外配置较多，TypeScript 项目常需要编译或 loader 配置，有些 MySQL 特性用 Raw SQL 更清楚。

当前项目没有安装 sequelize-cli，以下只是未来选择，不是现在能直接运行的命令：

npx sequelize-cli migration:generate --name create-admin-login-audit
npx sequelize-cli db:migrate

4.3 Umzug

Umzug 是 Sequelize 团队维护的通用 Migration 执行器。它适合在 TypeScript 项目中自己决定：

Migration 文件格式；
执行入口；
状态存在哪张表；
使用 queryInterface 还是读取 .sql。

它更灵活，也意味着需要自己写少量集成代码。当前项目同样没有安装 Umzug。

4.4 本项目当前推荐

近期：版本化 SQL + 本地测试库验证 + Git 审查
下一步：引入 Umzug 或项目级 SQL 执行器，自动记录执行状态
不建议：依靠 DBeaver Console 历史，或在生产使用 sync({ alter: true })

5. 为什么不把 `sync()` 当作正式 Migration

await db.sync();
await db.sync({ alter: true });
await db.sync({ force: true });

sync() 适合临时示例或测试，尝试让表与 Model 对齐。
alter 根据差异自动改表，你很难在上线前精确审查锁表、数据转换和索引变化。
force 会先删除表，具有直接丢数据风险。

Model 也不一定完整表达触发器、复杂约束、在线 DDL 策略等 MySQL 决策。因此生产结构变更应由可评审的 Migration 驱动。

6. 初学者常犯的错误

只在本地手工执行，没有把 SQL 纳入 Git。
修改已经在其他环境执行过的旧 Migration；结果是“文件相同名，数据库结构却不同”。
先发布读取新表的代码，后建表，产生短暂 500。
外键列类型一个有符号、一个无符号。
只有 Model unique: true，数据库中没有真实唯一索引。
给每一列都建索引，却没有根据 WHERE、JOIN、ORDER BY 设计复合索引。
认为 IF NOT EXISTS 能验证旧表结构正确；它只会跳过已经存在的表。
在审计表记录密码或 Token。

7. 练习

为 admin_login_audit 增加 request_id VARCHAR(64)，说明它是否应允许 NULL，是否需要索引。
写出“按 admin_user_id 分页且同毫秒内顺序稳定”的 SQL。提示：排序加上 id DESC。
在一个空测试库按顺序执行 admin_user 与审计表 SQL，再运行 SHOW CREATE TABLE。
故意把外键列改成 INT UNSIGNED，观察 MySQL 的报错，再恢复。
比较删除管理员时 RESTRICT、CASCADE、SET NULL 对审计数据的影响。

8. 复盘清单

[ ] 业务规则和高频查询是否先于表结构设计？
[ ] Migration 是否有唯一、可排序的编号？
[ ] SQL 是否进入 Git，而不是只存在 DBeaver 历史里？
[ ] 是否在空测试库验证过？
[ ] 是否检查了 SHOW CREATE TABLE 和索引？
[ ] Sequelize Model 的类型、默认值和 NULL 是否与 DDL 一致？
[ ] 部署顺序是否允许旧代码和新结构短期共存？
[ ] 是否避免在审计数据中保存 Secret？

下一章将在这个流程上处理更危险的任务：修改已经有数据、已经被旧代码使用的表。