数据库结构设计

1. SQL Server

CREATE TABLE [user] (
    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [username] VARCHAR(255) NOT NULL,
    [auth_key] VARCHAR(32) NOT NULL,
    [password_hash] VARCHAR(255) NOT NULL,
    [password_reset_token] VARCHAR(255) NULL,
    [email] VARCHAR(255) NOT NULL,
    [status] SMALLINT NOT NULL DEFAULT 10,
    [created_at] INT NOT NULL,
    [updated_at] INT NOT NULL
);
CREATE UNIQUE INDEX UK_username ON [user]([username]);

2. MySQL

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `auth_key` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password_hash` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password_reset_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` smallint(6) NOT NULL DEFAULT '10',
  `created_at` int(11) NOT NULL,
  `updated_at` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. SQLite

CREATE TABLE user (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    auth_key TEXT NOT NULL,
    password_hash TEXT NOT NULL,
    password_reset_token TEXT NULL,
    email TEXT NOT NULL,
    status INTEGER NOT NULL DEFAULT 10,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL
);
CREATE UNIQUE INDEX idx_username ON user(username);

4. PostgreSQL

CREATE TABLE "user" (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    auth_key VARCHAR(32) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    password_reset_token VARCHAR(255),
    email VARCHAR(255) NOT NULL,
    status SMALLINT NOT NULL DEFAULT 10,
    created_at BIGINT NOT NULL,
    updated_at BIGINT NOT NULL
);
CREATE UNIQUE INDEX idx_username ON "user"(username);

5. Oracle

CREATE TABLE "USER" (
    ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    USERNAME VARCHAR2(255) NOT NULL,
    AUTH_KEY VARCHAR2(32) NOT NULL,
    PASSWORD_HASH VARCHAR2(255) NOT NULL,
    PASSWORD_RESET_TOKEN VARCHAR2(255),
    EMAIL VARCHAR2(255) NOT NULL,
    STATUS NUMBER(6) DEFAULT 10 NOT NULL,
    CREATED_AT NUMBER NOT NULL,
    UPDATED_AT NUMBER NOT NULL
);
CREATE UNIQUE INDEX UK_USERNAME ON "USER"(USERNAME);

6. Access

CREATE TABLE user (
    id AUTOINCREMENT PRIMARY KEY,
    username TEXT NOT NULL,
    auth_key TEXT NOT NULL,
    password_hash TEXT NOT NULL,
    password_reset_token TEXT,
    email TEXT NOT NULL,
    status SMALLINT NOT NULL DEFAULT 10,
    created_at LONG NOT NULL,
    updated_at LONG NOT NULL
);
CREATE UNIQUE INDEX idx_username ON user(username);

7. MongoDB 文档示例

{
  "_id": ObjectId("600000000000000000000000"),
  "username": "",
  "auth_key": "",
  "password_hash": "",
  "password_reset_token": null,
  "email": "",
  "status": 10,
  "created_at": 1740000000,
  "updated_at": 1740000000
}

8. Redis Hash 存储示例

# 用户Hash存储
HSET user:1 username test auth_key abc123 password_hash xxx email test@xx.com status 10 created_at 1740000000 updated_at 1740000000
# 字段列表：username,auth_key,password_hash,password_reset_token,email,status,created_at,updated_at