Skip to content

用户认证 Schema

简单 约 20 分钟

你将构建什么

SQL Schema for 用户认证系统使用用户表, 唯一 email constraint, 索引 on email for 快 lookups, and roles 列.

学习目标

  • 创建用户表 使用认证字段
  • Enforce 唯一 email 使用 constraint
  • 创建索引 for 快 email lookups
  • 添加 CHECK constraint for 有效 roles

前置要求

  • CREATE TABLE and constraints
  • 理解的 索引
  • 基础认证概念

步骤

  1. 1

    创建用户表

    创建 用户 表使用 id, email, password_hash, role, and timestamps. Email 必须是 唯一 and 不空.

    代码
    CREATE TABLE users (
      id            SERIAL PRIMARY KEY,
      email         TEXT UNIQUE NOT NULL,
      password_hash TEXT NOT NULL,
      role          TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
      created_at    TIMESTAMP DEFAULT NOW(),
      updated_at    TIMESTAMP DEFAULT NOW()
    );

    预期结果

    用户表 是创建使用唯一 email and CHECK on role.

  2. 2

    创建索引 on email

    创建索引 on email 列到 速度上 登录查询, 哪个 look 上用户通过 email.

    代码
    CREATE INDEX idx_users_email ON users(email);

    预期结果

    索引是 创建, 制作 email lookups 快.

  3. 3

    Insert 用户

    Insert regular 用户 and admin. Use placeholder 密码 hashes (in 真实 apps, use bcrypt or argon2).

    代码
    INSERT INTO users (email, password_hash, role) VALUES
      ('[email protected]', 'hash_ada_123', 'user'),
      ('[email protected]', 'hash_admin_456', 'admin');

    预期结果

    Two 用户是 inserted — one regular and one admin.

  4. 4

    查询 for 登录

    编写查询那个会 是 used at 登录时间 — 查找用户通过 email. 索引制作这个快.

    代码
    SELECT id, email, password_hash, role
    FROM users
    WHERE email = '[email protected]';

    预期结果

    查询返回 Ada's 用户记录 instantly using 索引.

  5. 5

    添加索引优化查询

    为频繁查询的列创建索引,提升查询性能。索引是数据库性能优化的首要手段,但需注意写操作的开销。

    代码
    -- Create indexes on frequently filtered columns
    CREATE INDEX idx_users_email ON users(email);
    CREATE INDEX idx_orders_user_id ON orders(user_id);
    CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
    
    -- Verify the index is used
    EXPLAIN SELECT * FROM orders WHERE user_id = 42;

    预期结果

    EXPLAIN 输出显示使用 idx_orders_user_id 索引扫描而非全表扫描。

自己动手试试

你学到了什么

你构建 practical 认证 Schema 使用唯一 constraints, CHECK constraint for roles, and 索引 for 快 lookups — 相同模式 used in 真实 web applications.

相关资源

相关课程

相关术语

相关术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误

← 返回主题