01 管理员表设计

本章设计 admin_user 表。数据库是现成的 Sakila，但认证账号是新业务，因此需要单独建表。

本章提供可执行 DDL 骨架供你补全和审核，但不要在不理解字段与约束的情况下直接在重要数据库执行。

1. 业务假设

这是电影租赁公司的内部管理后台：

staff 是 Sakila 中办理租赁与收款的门店员工；
admin_user 是能够登录本管理后台的系统账号；
第一阶段所有管理员角色均为 manager；
管理员可以被禁用，也可能因连续失败被暂时锁定；
修改密码或“退出所有设备”后，应让旧 JWT 失效；
一个邮箱最多属于一个管理员，但邮箱允许为空；
管理员 ID 对外作为字符串处理。

不要直接用 staff.username/password 实现本练习认证。将业务员工与认证身份分开，可以避免：

误用 Sakila 示例中的弱密码字段；
登录系统被门店业务字段限制；
管理账号生命周期与员工业务记录耦合；
后续权限设计难以演进。

进阶时可以增加可空的 staff_id，把某个管理员关联到门店员工，但这不是第一阶段必做项。

2. 字段设计

字段	推荐类型	NULL	默认值	说明
`id`	`BIGINT UNSIGNED`	否	自增	主键
`username`	`VARCHAR(50)`	否	无	登录名，唯一
`password_hash`	`VARCHAR(255)`	否	无	Argon2id 摘要
`display_name`	`VARCHAR(100)`	否	无	页面显示名称
`email`	`VARCHAR(254)`	是	`NULL`	联系邮箱，非登录必需
`account_status`	`TINYINT UNSIGNED`	否	`1`	0 禁用，1 启用，2 手工锁定
`token_version`	`INT UNSIGNED`	否	`0`	撤销历史 Token
`failed_login_count`	`INT UNSIGNED`	否	`0`	连续失败次数
`locked_until`	`DATETIME(3)`	是	`NULL`	自动锁定截止时间
`last_login_at`	`DATETIME(3)`	是	`NULL`	最近成功登录时间
`password_changed_at`	`DATETIME(3)`	否	当前时间	密码最后修改时间
`created_at`	`DATETIME(3)`	否	当前时间	创建时间
`updated_at`	`DATETIME(3)`	否	当前时间并自动更新	修改时间

3. 关键字段为什么这样设计

3.1 `password_hash`

数据库只保存：

$argon2id$v=19$...

它是 Argon2id 密码摘要，不是明文，也不是可解密密文。项目已经安装 argon2，后续应使用：

const passwordHash = await argon2.hash(password, {
  type: argon2.argon2id,
});

校验时使用：

await argon2.verify(passwordHash, candidatePassword);

不要自行调用 MD5、SHA-1 或普通 SHA-256 保存密码。密码 Hash 需要专门的慢算法、随机盐和可调成本。

VARCHAR(255) 给 Argon2 编码串和未来算法升级留出余量。不要把 password_hash 放进 JWT、DTO、普通日志或错误响应。

3.2 `token_version`

JWT 签发时放入当前版本：

{
  "sub": "12",
  "role": "manager",
  "tokenVersion": 3
}

鉴权时查询管理员并比较：

JWT.tokenVersion === admin_user.token_version

修改密码或退出所有设备时执行原子递增：

UPDATE admin_user
SET token_version = token_version + 1
WHERE id = ?;

旧 JWT 中的版本不再匹配，因而失效。注意：这会让鉴权不再完全无状态，因为每次请求需要查询数据库或可靠缓存。

3.3 锁定字段

字段各自表达不同事实：

account_status       管理员主动配置的长期状态
failed_login_count   连续失败次数
locked_until         自动临时锁定截止时间

不要只用一个 Boolean locked 混合“被管理员禁用”和“失败太多临时锁定”。成功登录后通常清零 failed_login_count；临时锁定过期后如何复位，应在 Service 中制定明确规则。

登录失败计数存在并发问题。两个失败请求同时执行“读出 2，再写入 3”可能丢失一次更新。后续应练习原子递增：

failed_login_count = failed_login_count + 1

3.4 为什么使用 `DATETIME(3)`

(3) 保存毫秒，适合登录、锁定和审计时间。MySQL DATETIME 不携带时区；应用必须约定数据库连接时区和 API 输出格式。

建议：

数据库统一存 UTC，或明确整个系统固定使用的时区；
API 输出 ISO 8601 字符串；
计算 locked_until 时不要混用本地时间和 UTC；
不依赖数据库字段“看起来像北京时间”来猜时区。

3.5 `BIGINT` 与 JavaScript string 策略

MySQL BIGINT UNSIGNED 最大值可能超过 JavaScript 安全整数：

Number.MAX_SAFE_INTEGER;

因此本练习约定：

数据库：BIGINT UNSIGNED
Sequelize Model：string
DTO：adminId 为 string
JWT sub：string
URL 参数：校验后仍转换为规范的十进制 string

不要使用 Number(adminId) 后再查询，否则大 ID 可能丢失精度。

4. 索引对应的真实查询

不要因为“字段很多”就给每列建索引。索引应服务于查询。

登录查询

SELECT ...
FROM admin_user
WHERE username = ?
LIMIT 1;

对应唯一索引：

UNIQUE KEY uk_admin_user_username (username)

按邮箱查重

SELECT id
FROM admin_user
WHERE email = ?
LIMIT 1;

对应：

UNIQUE KEY uk_admin_user_email (email)

MySQL 唯一索引通常允许多行 NULL，符合“邮箱可空，但有值时唯一”的假设。空邮箱应保存 NULL，不要保存 ''。

管理员分页列表

SELECT ...
FROM admin_user
WHERE account_status = ?
ORDER BY created_at DESC, id DESC
LIMIT ? OFFSET ?;

候选联合索引：

KEY idx_admin_user_status_created_id
  (account_status, created_at, id)

是否真正需要它，要根据数据量和 EXPLAIN 决定。管理员通常数量很少，不要把“看起来合理的索引”误当成必须项。

不建议仅为 failed_login_count 建索引，因为常规业务并不按失败次数批量检索管理员。

5. 可执行 DDL 骨架

请先在练习库执行 USE sakila_training;，逐项补全 TODO，并自行审核字符集、约束支持版本和字段默认值。

CREATE TABLE admin_user (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(100) NOT NULL,
  email VARCHAR(254) NULL,

  account_status TINYINT UNSIGNED NOT NULL DEFAULT 1,
  token_version INT UNSIGNED NOT NULL DEFAULT 0,
  failed_login_count INT UNSIGNED NOT NULL DEFAULT 0,
  locked_until DATETIME(3) NULL,
  last_login_at DATETIME(3) NULL,
  password_changed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),

  PRIMARY KEY (id),
  UNIQUE KEY uk_admin_user_username (username),
  UNIQUE KEY uk_admin_user_email (email),

  /* TODO：是否加入管理员列表的联合索引？先写出对应查询和 EXPLAIN */

  CONSTRAINT chk_admin_user_account_status
    CHECK (account_status IN (0, 1, 2)),
  CONSTRAINT chk_admin_user_failed_login_count
    CHECK (failed_login_count >= 0)
) ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;

注意：

utf8mb4_0900_ai_ci 适用于 MySQL 8；其他版本需选择受支持的 collation。
CHECK 在较旧 MySQL 版本中可能被解析但不真正执行，请确认服务器版本。
当前 collation 通常会让用户名比较不区分大小写。你需要明确 Admin 与 admin 是否视为同一账号。
DDL 没有 role 字段，因为第一阶段只有一种角色；如果加入角色，必须同步定义约束和鉴权规则。

6. Sequelize 字段映射预览

Model 使用 camelCase，数据库使用 snake_case：

declare passwordHash: string;
declare tokenVersion: CreationOptional<number>;
declare lockedUntil: Date | null;

字段配置通过 field 映射：

passwordHash: {
  type: DataTypes.STRING(255),
  allowNull: false,
  field: "password_hash",
},

本练习的 DDL 已由 MySQL 负责生成和更新两个时间戳。为了让 Model 属性仍保持 camelCase，建议把它们作为普通字段显式映射，并关闭 Sequelize 的自动时间戳：

createdAt: {
  type: DataTypes.DATE(3),
  allowNull: false,
  field: "created_at",
},
updatedAt: {
  type: DataTypes.DATE(3),
  allowNull: false,
  field: "updated_at",
},

// Model options
timestamps: false,

这里的 timestamps: false 不是说表里没有时间字段，而是“不让 Sequelize 自动添加和维护时间戳属性”。不要再同时配置 createdAt: "created_at"：该 option 会重命名 Model 属性，并不是单纯设置数据库列名，容易与类上的 createdAt 冲突。

7. 数据库约束与应用校验的边界

应用层可先检查用户名格式和是否已存在，以返回友好消息；数据库唯一约束仍然必须存在，因为并发请求可能同时通过“查重”。最终 Service 还要捕获 UniqueConstraintError。

同理：

express-validator  校验 username 长度和字符
Service            判断业务状态
Sequelize Model    描述字段规则
MySQL constraint   提供最终一致性保护

这些校验有重合，但各自处于不同边界，不能只保留最外层校验。

8. 本章验收清单

[ ] 能说明为什么不复用 staff.password。
[ ] password_hash 不会通过默认查询进入 DTO。
[ ] username 和非 NULL email 有唯一约束。
[ ] 能说明 token_version 如何使旧 JWT 失效。
[ ] 临时锁定和人工禁用没有混用一个字段。
[ ] 管理员 ID 在 TypeScript 和 JWT 中使用 string。
[ ] 能解释每个索引服务于哪条查询。
[ ] 没有运行 sync({ force: true })。

9. 本章练习

补全并审核 DDL，在你自己的练习库中执行前先说明每个约束的作用。
如果用户名需要严格区分大小写，应怎样调整字段或索引？
写出“临时锁定 15 分钟”的状态判断伪代码。
两次并发登录失败怎样避免丢失计数？
为什么管理员表数据很少时，联合索引可能没有明显收益？
设计“不能禁用最后一个启用管理员”的查询与事务边界。
如果以后支持 Refresh Token，还需要保存哪些可撤销信息？
分析 DATETIME(3) 与 Node.js Date 交换时的时区风险。