如何设计用户表

用户表是几乎所有产品的核心表。无论你做的是 AI SaaS、电商平台还是内容社区，第一张要建的表大概率就是 users。但很多开发者在建表时随手写几个字段就往前推进，等产品上线半年后才发现：邮箱字段不够用、密码存储方式要改、软删除和硬删除混在一起理不清、用户状态多到布尔值撑不住。改表的代价远高于从一开始就把关键字段想清楚。

本文从字段设计、认证方案、状态管理、软删除策略和关联表五个维度，系统讲清楚用户表该怎么设计。代码示例使用 Prisma Schema，同时提供对应的 SQL 参考，适配 PostgreSQL 和 MySQL。

用户表的核心字段

一张用户表需要承载四类信息：基本信息、认证信息、状态信息和时间戳。把这四类区分清楚，字段归属就不会乱。

基本信息

基本信息是用户的身份标识和画像数据。

id 的选型值得单独讨论。自增整数（AUTO_INCREMENT / SERIAL）虽然简单，但存在两个问题：一是对外暴露了用户注册顺序和总量，竞争对手可以通过注册两个账号、拿到两个 ID 的差值来估算你的用户规模；二是在分布式数据库和多集群场景下容易产生冲突。cuid() 生成的 ID 是有序字符串，兼顾了排序性能和不可猜测性，是 SaaS 产品推荐的折中选择。如果安全要求更高，可以用 UUID v4，代价是索引体积更大、写入性能略低。

认证信息

认证信息用于验证用户身份，具体设计在下一节展开。

状态信息

状态信息描述用户当前在系统中的生命周期阶段。

为什么用枚举而不用布尔值？因为用户状态几乎一定会扩展。最初你可能只需要「正常」和「禁用」两种状态，但很快就会冒出「待验证」「已暂停」「已注销」的需求。枚举类型让你在不改表结构的前提下支持这些状态。

时间戳

时间戳用于审计追踪和数据生命周期管理。

createdAt 和 updatedAt 是每张表都应该有的基础字段，不是用户表的专利。lastLoginAt 的更新频率需要控制——如果每次请求都刷新，会带来不必要的写放大。推荐的做法是只在登录时更新，或者使用异步批量更新。

完整的用户表基础结构如下：

model User {
  id            String     @id @default(cuid())
  email         String     @unique
  name          String?
  image         String?
  passwordHash  String?
  locale        String     @default("en")
  timezone      String     @default("UTC")
  status        UserStatus @default(PENDING)
  role          Role       @default(USER)
  lastLoginAt   DateTime?
  createdAt     DateTime   @default(now())
  updatedAt     DateTime   @updatedAt
  deletedAt     DateTime?
 
  @@index([email])
  @@index([status])
  @@map("users")
}
 
enum UserStatus {
  PENDING    // 待验证
  ACTIVE     // 正常
  SUSPENDED  // 已暂停
  DEACTIVATED // 已停用
}
 
enum Role {
  USER
  ADMIN
}

认证相关字段设计

认证字段的设计直接影响系统安全性。根据认证方式的不同，字段设计有明显的差异。

邮箱 + 密码认证

密码在数据库中绝对不能存储明文，甚至不能存储可逆加密后的结果。正确做法是存储密码哈希值。

model User {
  // ...
  passwordHash  String?   // bcrypt / argon2 哈希值
  emailVerified DateTime? // 邮箱验证时间，null 表示未验证
}

关于哈希算法的选择：

三个关键原则：第一，每次哈希使用不同的随机 salt，防止彩虹表攻击；第二，选择带 work factor 的算法（bcrypt 的 rounds、argon2 的 memory cost），让哈希计算「故意变慢」，抵抗 GPU 暴力破解；第三，在数据库中记录算法标识，方便未来迁移到更强的算法时做渐进式升级。

Prisma 层面的对应实现：

model User {
  // ...
  passwordHash      String?   // 存储格式：$argon2id$v=19$m=65536,t=3,p=4$...
  passwordAlgorithm String?   // "argon2id" | "bcrypt"，便于算法迁移
  emailVerified     DateTime?
  verifyToken       String?   // 邮箱验证令牌
  verifyTokenExpiry DateTime? // 验证令牌过期时间
  resetToken        String?   // 密码重置令牌
  resetTokenExpiry  DateTime? // 重置令牌过期时间
}

passwordAlgorithm 字段看似多余，实则实用。当你的系统从 bcrypt 迁移到 argon2 时，老用户的 passwordHash 仍然是 bcrypt 格式。用户下次登录时，系统验证旧哈希成功后，用新算法重新哈希并更新 passwordHash 和 passwordAlgorithm。这种渐进式迁移方案不需要强制所有用户同时重置密码。

OAuth 社交登录

出海产品通常需要支持 Google、GitHub 等第三方登录。OAuth 的绑定信息不应该塞进 users 表，而应该拆到独立的关联表。

model Account {
  id                  String @id @default(cuid())
  userId              String
  type                String // "oauth2" | "oidc"
  provider            String // "google" | "github" | "apple"
  providerAccountId   String
  refreshToken        String?
  accessToken         String?
  expiresAt           Int?
  tokenType           String?
  scope               String?
  idToken             String?
 
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
 
  @@unique([provider, providerAccountId])
  @@index([userId])
  @@map("accounts")
}

这种设计的好处：一个用户可以绑定多个第三方账号（Google + GitHub），新增 OAuth 提供商不需要修改 users 表结构，provider + providerAccountId 的联合唯一索引防止重复绑定。

这个 Account 表的结构与 Auth.js（NextAuth）的标准 Schema 完全兼容。如果你的项目使用了 Auth.js，可以直接套用。

Session 管理

Session 表管理用户的登录会话，同样独立于 users 表：

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  userAgent    String?
  ipAddress    String?
 
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
 
  @@index([userId])
  @@map("sessions")
}

userAgent 和 ipAddress 用于安全审计场景，比如让用户在设置页看到「当前登录设备」列表。出海产品需要注意 GDPR 合规要求——IP 地址在欧盟法律下属于个人数据，存储和使用时需要明确告知用户并提供相应的数据保护措施。

关于 Token 过期的管理策略：

对于大多数出海 AI SaaS 产品，推荐 JWT + Refresh Token 的组合方案。Access Token 短生命周期（15 分钟），Refresh Token 长生命周期（7-30 天）并存储在数据库中，这样可以实现 Token 轮换和主动撤销。

用户状态管理

用户状态管理不只是「启用」和「禁用」两个值那么简单。随着产品迭代，你会遇到越来越多的状态需求：注册后待验证、邮箱未确认、欠费暂停、用户主动注销等。

状态机设计

推荐使用枚举类型定义用户状态，并明确状态之间的流转规则：

PENDING → ACTIVE（邮箱验证通过）
ACTIVE → SUSPENDED（违规 / 欠费）
SUSPENDED → ACTIVE（恢复正常）
ACTIVE → DEACTIVATED（用户主动注销）
DEACTIVATED → ACTIVE（30 天内撤回注销）

在代码层面，状态流转应该有明确的校验逻辑：

// 允许的状态转换映射
const ALLOWED_TRANSITIONS: Record<UserStatus, UserStatus[]> = {
  PENDING: ['ACTIVE'],
  ACTIVE: ['SUSPENDED', 'DEACTIVATED'],
  SUSPENDED: ['ACTIVE', 'DEACTIVATED'],
  DEACTIVATED: ['ACTIVE'],
}
 
function canTransition(from: UserStatus, to: UserStatus): boolean {
  return ALLOWED_TRANSITIONS[from]?.includes(to) ?? false
}

各状态的业务含义

SUSPENDED 状态是否允许登录是一个产品决策。如果允许登录但限制功能，用户可以在登录后看到暂停原因并自行解决（比如补缴费用）。如果不允许登录，管理更严格但用户自助恢复的难度更高。出海产品通常采用前者，因为 GDPR 要求用户有权访问自己的数据，即使是暂停状态。

软删除设计

用户注销账号时，你有两个选择：硬删除（DELETE FROM users WHERE id = ?）和软删除（设置标记但保留数据）。对于大多数 SaaS 产品，软删除是更安全的选择。

为什么不用硬删除

硬删除会带来一系列连锁问题：

第一，数据关联断裂。用户的订单、支付记录、AI 调用记录都通过 userId 外键关联。如果直接删除用户记录，这些关联数据要么被级联删除（丢失业务数据），要么变成孤儿记录（外键约束报错）。

第二，审计追踪失效。金融类、支付类产品有合规要求，交易数据需要保留一定年限。如果用户记录被物理删除，审计链路就断了。

第三，无法恢复。用户可能因为误操作或冲动注销账号，硬删除后数据无法找回。软删除提供了一个「后悔窗口」。

软删除的实现方案

最常见的方案是添加 deletedAt 时间戳字段：

model User {
  // ...
  deletedAt DateTime? // null 表示未删除，有值表示已删除的时间
}

对应的 SQL 操作：

-- 软删除
UPDATE users SET deleted_at = NOW() WHERE id = 'xxx';
 
-- 查询活跃用户（排除已删除）
SELECT id, email FROM users WHERE deleted_at IS NULL;
 
-- 恢复已删除用户
UPDATE users SET deleted_at = NULL WHERE id = 'xxx';
 
-- 物理清除过期数据（定期清理任务）
DELETE FROM users WHERE deleted_at < NOW() - INTERVAL '90 days';

使用 deletedAt 而不是布尔值 isDeleted 的原因：时间戳包含了删除时间信息，可以用于过期清理策略，也方便排查「这个用户是什么时候被删除的」。

软删除的注意事项

软删除有几个容易踩的坑：

唯一约束冲突。用户 A 用 [email protected] 注册后注销（软删除），用户 B 再次用同一个邮箱注册。如果 email 字段有唯一约束，就会冲突。解决方案是将唯一约束改为 (email, deletedAt) 的联合约束，或者在注销时清空 email 字段（替换为 [email protected]）。

查询遗漏过滤条件。每一处查询都需要加上 WHERE deleted_at IS NULL，一旦遗漏就会查出已删除的用户。在 Prisma 中可以通过全局中间件自动注入过滤条件：

// Prisma 中间件：自动过滤软删除记录
prisma.$use(async (params, next) => {
  if (params.model === 'User') {
    if (['findMany', 'findFirst', 'count'].includes(params.action)) {
      params.args.where = {
        ...params.args.where,
        deletedAt: null,
      }
    }
  }
  return next(params)
})

表膨胀。软删除的记录持续积累，表体积会不断增长。需要定期运行清理任务，将超过保留期限的软删除记录物理删除或归档到冷存储。

软删除 vs 硬删除对比

表结构设计对比

对于出海 SaaS 产品，推荐 deletedAt 时间戳方案。它在信息完整性、实现复杂度和查询便利性之间取得了最好的平衡。

关联表设计

用户表不是孤立存在的。一个完整的用户系统至少涉及用户-角色、用户-组织两组关联关系。

用户-角色关联

简单场景下，角色可以直接作为 users 表的枚举字段。当角色体系变复杂（一个用户有多个角色、角色可以继承）时，需要拆成独立的关联表。

经典的 RBAC（基于角色的访问控制）设计：

// 角色表
model Role {
  id          String   @id @default(cuid())
  name        String   @unique // "admin" | "editor" | "viewer"
  description String?
  createdAt   DateTime @default(now())
 
  users UserRole[]
 
  @@map("roles")
}
 
// 用户-角色关联表
model UserRole {
  id     String @id @default(cuid())
  userId String
  roleId String
 
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
  role Role @relation(fields: [roleId], references: [id], onDelete: Cascade)
 
  @@unique([userId, roleId])
  @@index([userId])
  @@index([roleId])
  @@map("user_roles")
}

@@unique([userId, roleId]) 防止同一用户被重复分配同一角色。onDelete: Cascade 确保用户被删除时关联的角色记录自动清除。

用户-组织关联

出海 SaaS 产品如果做 B2B 或团队协作功能，需要支持「一个用户属于多个组织」的关系。

model Organization {
  id        String   @id @default(cuid())
  name      String
  slug      String   @unique
  createdAt DateTime @default(now())
 
  members OrganizationMember[]
 
  @@map("organizations")
}
 
model OrganizationMember {
  id             String       @id @default(cuid())
  userId         String
  organizationId String
  orgRole        OrgRole      @default(MEMBER)
  joinedAt       DateTime     @default(now())
 
  user         User         @relation(fields: [userId], references: [id], onDelete: Cascade)
  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
 
  @@unique([userId, organizationId])
  @@index([userId])
  @@index([organizationId])
  @@map("organization_members")
}
 
enum OrgRole {
  OWNER   // 所有者，可删除组织
  ADMIN   // 管理员，可管理成员
  MEMBER  // 普通成员
}

用户表完整关联关系

用 Mermaid ER 图展示用户表与关联表的关系：

实战案例

案例一：AI 写作工具的早期用户表设计

一个做 AI 写作工具的出海团队，最初的用户表只有 5 个字段：

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

三个月后遇到了问题。Google 登录接入后，password 字段变成了可选，但没有提前设置 NULL 约束，导致 OAuth 用户注册失败。加上 locale 字段时发现表结构没有预留扩展空间，每次加字段都要做线上 DDL。更严重的是，一个用户要求删除账号，团队发现直接 DELETE 会导致订单数据和发票记录丢失。

重构后的设计：

model User {
  id            String     @id @default(cuid())
  email         String     @unique
  name          String?
  image         String?
  passwordHash  String?    // OAuth 用户可以为 null
  emailVerified DateTime?
  locale        String     @default("en")
  timezone      String     @default("UTC")
  status        UserStatus @default(ACTIVE)
  lastLoginAt   DateTime?
  createdAt     DateTime   @default(now())
  updatedAt     DateTime   @updatedAt
  deletedAt     DateTime?
 
  accounts Account[]
  sessions Session[]
 
  @@index([email])
  @@index([status])
  @@map("users")
}

核心变化：passwordHash 改为可选（兼容 OAuth 用户），增加了 status、locale、timezone、deletedAt 等扩展字段，认证信息拆到独立的 Account 和 Session 表。

案例二：多租户 SaaS 平台的用户-组织设计

一个面向企业的多租户 AI 平台，需要支持「一个用户加入多个团队」和「团队内不同角色」的需求。

初始设计把 organizationId 直接放在 users 表里，用户只能属于一个组织。业务扩展后不得不拆分成独立的关联表，并且要写数据迁移脚本把已有用户迁移到新结构。

最终方案采用了 users + organizations + organization_members 三表设计（如前文 Prisma Schema 所示），关键设计决策：

• OrganizationMember 表有自己的 orgRole 字段，独立于用户的全局 role。用户在某个组织里是 MEMBER，在另一个组织里可能是 OWNER
• @@unique([userId, organizationId]) 确保同一用户在同一组织不会出现两条记录
• onDelete: Cascade 确保组织被删除时成员关系自动清除

不同产品规模的设计建议

出海产品的特殊考虑

出海产品的用户表设计有几个区别于国内产品的要点：

多语言和多时区。locale 和 timezone 不是可选的装饰字段，它们直接影响邮件发送时间、日期格式展示、界面语言等核心体验。一个在东京的用户和一个在纽约的用户，看到的「今日免费额度重置」应该发生在各自时区的零点，而不是 UTC 零点。

GDPR 合规。欧盟用户的个人数据（包括邮箱、IP 地址、登录记录）受到 GDPR 保护。用户有权要求删除个人数据（Right to Erasure），这要求你的软删除方案能在用户明确要求时执行真正的物理删除，而不只是标记为已删除。

数据驻留。部分国家和地区要求用户数据存储在本地。如果产品同时服务欧洲和亚太用户，可能需要考虑多区域数据库部署，用户表的设计需要在不同区域保持一致的 Schema。

检查清单

在设计或审查用户表时，逐项确认以下要点：

1. 主键选型：是否使用了不可猜测的 ID（cuid / UUID），而非自增整数？
2. 邮箱约束：email 字段是否设置了唯一约束和索引？
3. 密码存储：是否使用 bcrypt / argon2 等安全哈希算法，而非 MD5 / SHA256？
4. OAuth 拆分：第三方登录信息是否拆到独立的 accounts 表，而非塞进 users？
5. 状态枚举：用户状态是否使用枚举类型，而非布尔值？
6. 软删除：是否使用 deletedAt 时间戳实现软删除，并处理了唯一约束冲突？
7. 时区和语言：是否包含 locale 和 timezone 字段？
8. 时间戳完整性：createdAt、updatedAt 是否在每张表上都存在？
9. 外键级联：关联表的 onDelete 策略是否明确设置（Cascade / SetNull / Restrict）？
10. 查询过滤：是否有全局中间件自动注入 deletedAt IS NULL 过滤条件？
11. GDPR 合规：是否支持用户请求物理删除个人数据？
12. 索引覆盖：高频查询字段（email、status、userId 外键）是否有索引？
13. Token 过期：邮箱验证令牌和密码重置令牌是否设置了过期时间？
14. 敏感字段保护：密码哈希、Token 等敏感字段是否在 API 层做了脱敏处理？

小结

用户表的设计质量直接影响产品的可扩展性和维护成本。核心原则是：基本信息保持精简，认证信息拆到独立表，状态管理用枚举而非布尔值，软删除用时间戳而非标志位，关联关系通过中间表维护。出海产品还需要额外考虑多语言、多时区和 GDPR 合规的需求。

好的用户表设计不是一次性完成的，但它可以在早期就奠定清晰的结构基础，避免后期频繁做破坏性的表结构变更。本文提供的 Prisma Schema 和 SQL 示例可以直接作为项目模板使用，根据你的业务需求做增减即可。

参考资料

1. Auth.js Database Schema - Auth.js 官方数据库适配器文档，提供标准的 User / Account / Session 表结构
2. Redgate: Best Practices for Designing a User Authentication Module - 用户认证模块设计最佳实践
3. OneUptime: How to Design a Schema for Soft Deletes in MySQL - MySQL 软删除 Schema 设计指南
4. 掘金：登录注册用户表结构详细设计及实现 - 用户表结构设计实战，涵盖垂直分表和 OAuth 拆分
5. 知乎：PolarDB-X 最佳实践——如何设计一张用户表 - 分布式场景下的用户表设计与分区键选择
6. 腾讯云：用户表的设计——角色和权限管理数据表设计 - RBAC 权限模型的表结构设计
7. Brent Ozar: What Are Soft Deletes, and How Are They Implemented? - 软删除实现方案与注意事项
8. OWASP: Password Storage Cheat Sheet - OWASP 密码存储安全指南