26.1 用户系统：账号、权限、登录状态设计

用户系统几乎是每个应用的起点，也是数据库设计中最容易“一步错、步步错”的模块。账号怎么建表、密码怎么存、权限怎么管、登录状态怎么维护——这些决策一旦在早期做错，后面改起来成本极高。本节从数据库层面出发，给出一个经过大量项目验证的实用设计方案。

账号表设计：一张表解决用户核心信息

用户账号表（users）是数据中最重要的表之一，设计要点是字段精简、索引准确、字符集统一。下面是一个典型的 InnoDB 表结构：

CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    status TINYINT NOT NULL DEFAULT 1 COMMENT '1正常 0禁用',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    KEY idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

设计决策说明：

主键用自增 BIGINT：比 INT 空间大一倍，但在用户量级上亿时不会很快耗尽，而且作为聚簇索引，自增有序插入对性能友好。如果你预期未来需要分库分表，可以用雪花算法生成全局唯一 ID，但初期自增足矣。
用户名和邮箱设置唯一索引：在应用层做重复校验的同时，数据库层的唯一约束是最后一道防线，能杜绝并发注册导致的重复账号。注意 email 在实际使用中可能多账号共享，但多数系统将其作为登录凭证，唯一性是必要的。
密码存储用 CHAR(60)：这里存的是 bcrypt 哈希后的结果，bcrypt 输出固定 60 个字符。绝不存储明文密码或简单 MD5。在应用层使用 bcrypt、argon2 等算法加盐哈希，再入库。即使数据库泄露，攻击者也无法反推出原始密码。
phone 字段可空、建有普通索引：不是所有用户都会绑定手机，所以允许 NULL。建索引是为了支持“手机号登录”或“找回密码”时的快速查找。
字符集统一 utf8mb4：MySQL 的 utf8 最多支持 3 字节，无法存储 emoji 等 4 字节字符。utf8mb4 才是真正的 UTF-8，能避免因昵称包含特殊字符引发的插入失败。
status 实现软禁：删除用户用状态标记而不是物理删除，数据更安全，恢复也简单。

权限设计：RBAC 模型的简洁落地

权限管理推荐采用经典模型：用户-角色-权限。角色是权限的集合，用户通过与角色关联获得权限。这种模型比直接给用户赋权更灵活，尤其适合后台管理场景。

三张核心表：

-- 角色表
CREATE TABLE roles (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    code VARCHAR(30) NOT NULL COMMENT '角色标识，如 admin, editor',
    description VARCHAR(200) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 权限表（可对应 URL 或操作码）
CREATE TABLE permissions (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    code VARCHAR(80) NOT NULL COMMENT '权限标识，如 user:create, post:delete',
    PRIMARY KEY (id),
    UNIQUE KEY uk_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 用户角色关联表
CREATE TABLE user_roles (
    user_id BIGINT UNSIGNED NOT NULL,
    role_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, role_id),
    KEY idx_role_id (role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 角色权限关联表
CREATE TABLE role_permissions (
    role_id INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

几点实用建议：

实际项目中，多数操作都可以转化为权限码校验，在网关或中间件层拦截。你需要查询的通常就是：根据 user_id 找出其所有角色，再找出所有权限码，放进缓存。这可以通过一次 SQL 联表完成，或直接在应用层缓存用户权限列表，避免每次请求都查库。
ON DELETE CASCADE 保证角色或用户删除时关联记录自动清理，防止孤儿数据。但这要求外键是 InnoDB 的特性，如果分库分表则无法使用，需要应用层处理。
如果你的系统权限比较简单（如只有普通用户和管理员），用 users 表里一个 role 枚举字段就够，不必上全套 RBAC。设计永远以当前需要为准，不要过度设计。

登录状态设计：集中式会话表与 Token 策略

用户登录后需要维持状态，常见方案有两种：分布式 Session（如 Spring Session + Redis）适合有状态服务；JWT 无状态令牌适合无状态服务。但无论哪种，数据库都可以承担“登录凭证”的角色。这里给出的是一个简化的 user_sessions 表设计，适合中小规模项目，也便于排查登录态问题。

CREATE TABLE user_sessions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    token VARCHAR(64) NOT NULL COMMENT '登录令牌',
    refresh_token VARCHAR(64) DEFAULT NULL COMMENT '刷新令牌',
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(500) DEFAULT NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_token (token),
    KEY idx_user_id (user_id),
    KEY idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

使用流程：

用户登录时，服务端验证通过后生成一个随机安全的 token（例如 64 字节 hex 字符串），以及可选的长时效 refresh_token（用于自动续期）。
将 token 及过期时间写入 user_sessions 表，返回给客户端。
后续请求携带 token，服务端查表验证 token 是否有效，同时可校验 IP 和 User-Agent 的变化，防止 token 被盗用。
用户主动登出时，删除该条记录。对于超时的 token，可以通过定时任务清理 expires_at < NOW() 的记录。

在 MySQL 实现集中式会话时，有几个优化点：

唯一索引 uk_token 保证 token 不重复，实际上 token 碰撞概率极低，但索引能让查询走哈希查找，性能极高。
定期清理：写一个定时任务 DELETE FROM user_sessions WHERE expires_at < NOW() 每天或每小时执行，防止表无限膨胀。
高并发场景不建议用此方案：对于数百万日活的系统，频繁查表会成为瓶颈，此时应当迁移到 Redis 等内存存储，但即使如此，也可以将 session 落库到 MySQL 做持久化备份，用于离线分析或异常排查。
如果采用 JWT 无状态方案，则不需要此表，但你需要额外维护一个“黑名单”或“失效列表”，存放到数据库中（例如用于用户修改密码后强制所有前登录失效）。

串联事务：让你的用户操作安全可靠

用户系统中有几个操作涉及多表写入，必须放在事务中：

注册：INSERT INTO users，同时可能需要初始化用户默认角色（INSERT INTO user_roles）。任何一步失败都要整体回滚。
注销/封禁：更新 users.status，同时删除该用户所有 user_sessions 使其立即生效。
修改敏感字段：如换绑手机或邮箱，需要同时更新 users 表和发送验证邮件/短信。此处的数据库操作是单表，但应用层可以配合事务和最终一致性来处理外部通知。

事务的粒度要尽量小，不要放入远程调用或耗时操作。数据库事务解决的是“数据库内部”的一致性问题，外部交互应使用补偿、重试等策略。

避坑指南

不要用邮箱/手机号做主键。这些信息可能变更，主键变动代价巨大。主键应当是纯粹无业务含义的 ID。
密码哈希必须在服务端进行，绝不让数据库参与密码加密。这样即使数据库被拖库，攻击者拿到的也是 bcrypt 哈希，暴力破解成本极高。
唯一索引不是万能的，并发下可能触发死锁。不过用户注册时顺序插入大概率不会同时插同一条，风险很小。如果发生，应用层应捕获死锁异常并重试。
注意字符集和校验规则。utf8mb4_unicode_ci 是大小写不敏感的通用排序，但有时需要精确区分大小写，例如用户名“Alex”和“alex”是否为同一用户。可根据业务需要选择 utf8mb4_bin（按二进制比较，区分大小写）。
不要轻易物理删除用户。使用 status 字段标记状态，同时保留审计日志。用户数据关联着大量业务数据，物理删除可能破坏引用完整性。

至此，一个健壮可扩展的用户系统基础设计就完成了。数据库的设计永远服务于业务，随着系统演进可能需要拆表、引入 Redis 缓存、增加 MFA 等多因素认证，但上述的扎实根基会让你少走很多弯路。