OAuth 2.0用户认证数据库表结构设计咨询

阿华AIGC实验室

2026-4-7

嘿，这个问题我之前帮好几个开发者梳理过，其实核心是看你当前的系统规模和未来的扩展计划——完全可以用单表，也可以分表，没有绝对的对错，只有适合你场景的选择，下面给你拆解两种方案的优缺点和实际例子：

单表方案（适合中小型、逻辑简单的系统）

如果你的系统目前只需要本地用户名密码+Google登录，且短期内不会加太多第三方认证方式，单表是最省心的选择：查询不用关联表，维护成本低，逻辑也简单。

表结构示例（SQL）

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  full_name VARCHAR(255),
  avatar_url VARCHAR(255),
  -- 本地登录专属字段
  password_hash VARCHAR(255), -- 只有本地登录的用户才有值，OAuth用户设为NULL
  -- 认证方式区分字段
  auth_provider VARCHAR(50) NOT NULL CHECK (auth_provider IN ('local', 'google')),
  auth_provider_id VARCHAR(255), -- Google用户的唯一ID（即Google返回的sub字段），本地用户可设为NULL或与id一致
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

核心逻辑

本地登录：筛选auth_provider = 'local'且email = 输入邮箱的用户，验证password_hash和输入密码的匹配度（用bcrypt这类工具即可）。
Google登录：筛选auth_provider = 'google'且auth_provider_id = Google返回的用户ID的用户；如果不存在，就用Google返回的邮箱、姓名创建新用户，填好对应的auth_provider和auth_provider_id。

分表方案（适合复杂系统/多第三方登录场景）

如果以后打算加更多第三方登录（比如Facebook、GitHub），或者需要支持用户绑定多种认证方式（比如先本地注册，后来又绑定Google登录），分表会更灵活，能把用户基础信息和认证凭证解耦。

表结构示例（SQL）

1. 用户基础信息表（users）

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  full_name VARCHAR(255),
  avatar_url VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 认证凭证表（auth_credentials）

一个用户可以对应多条凭证（比如本地+Google各一条）：

CREATE TABLE auth_credentials (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider VARCHAR(50) NOT NULL CHECK (provider IN ('local', 'google', 'github')),
  provider_id VARCHAR(255), -- 第三方平台的用户唯一ID，本地登录可设为NULL
  password_hash VARCHAR(255), -- 仅本地登录的凭证有值
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

核心逻辑

本地登录：通过provider = 'local'关联到users表，找到匹配邮箱的用户，验证密码哈希。
Google登录：通过provider = 'google'和provider_id = Google用户ID找到凭证，关联到对应的用户；如果没有凭证，先创建用户，再插入新的认证凭证。
多方式绑定：比如用户已经有本地账号，后来要绑定Google，直接给该用户新增一条provider='google'的凭证即可，完全不用修改用户基础信息。

Node.js 实战伪代码示例

单表下的Google登录处理

const bcrypt = require('bcrypt');
const { db } = require('./你的数据库连接');

async function handleGoogleOAuthCallback(googleProfile) {
  const { id: googleUserId, emails, displayName, photos } = googleProfile;
  const userEmail = emails[0].value;
  const avatarUrl = photos?.[0]?.value;

  // 先检查是否已有该Google账号对应的用户
  let existingUser = await db.query(
    'SELECT * FROM users WHERE auth_provider = $1 AND auth_provider_id = $2',
    ['google', googleUserId]
  );

  if (!existingUser.rows.length) {
    // 再检查是否有相同邮箱的本地用户（可选：支持邮箱合并，这里示例直接创建新用户）
    existingUser = await db.query(
      'SELECT * FROM users WHERE email = $1 AND auth_provider = $2',
      [userEmail, 'local']
    );

    if (existingUser.rows.length) {
      console.log('该邮箱已存在本地账号，可引导用户绑定');
    } else {
      // 创建新的Google用户
      const newUser = await db.query(
        `INSERT INTO users (email, full_name, avatar_url, auth_provider, auth_provider_id)
         VALUES ($1, $2, $3, $4, $5) RETURNING *`,
        [userEmail, displayName, avatarUrl, 'google', googleUserId]
      );
      existingUser = newUser;
    }
  }

  // 生成JWT返回给前端
  const token = generateJwtToken(existingUser.rows[0]);
  return { user: existingUser.rows[0], token };
}

单表下的本地登录处理

async function handleLocalLogin(email, password) {
  const userResult = await db.query(
    'SELECT * FROM users WHERE auth_provider = $1 AND email = $2',
    ['local', email]
  );

  if (!userResult.rows.length) {
    throw new Error('用户不存在或密码错误');
  }

  const user = userResult.rows[0];
  const isPasswordValid = await bcrypt.compare(password, user.password_hash);

  if (!isPasswordValid) {
    throw new Error('用户不存在或密码错误');
  }

  const token = generateJwtToken(user);
  return { user, token };
}