04 · 数据库设计与迁移实践

举报
yd_280040719 发表于 2026/11/17 12:07:13 2026/11/17
【摘要】 04 · 数据库设计与迁移实践系列第 4 篇。讲解 PostgreSQL 表结构设计思路、SQL 迁移管理方式,以及"内存先行、SQL 兜底"的开发流程。 1. 核心实体围绕玩法,数据库需要支撑四类数据:实体内容关键约束users账号:用户名/密码哈希、openid、昵称用户名与 openid 均唯一progress学习闯关进度(学科、关卡星级)每用户每关卡唯一daily_circle每日...

04 · 数据库设计与迁移实践

系列第 4 篇。讲解 PostgreSQL 表结构设计思路、SQL 迁移管理方式,以及"内存先行、SQL 兜底"的开发流程。

1. 核心实体

围绕玩法,数据库需要支撑四类数据:

实体 内容 关键约束
users 账号:用户名/密码哈希、openid、昵称 用户名与 openid 均唯一
progress 学习闯关进度(学科、关卡星级) 每用户每关卡唯一
daily_circle 每日小龙圈(签到 / 每日挑战完成态) 每用户每日一条
rooms 擂台房间:房主、对手、状态、牌局种子 房间码唯一,状态机流转

2. 迁移管理

server/migrations/ 下按序号排列 SQL 文件,启动时按顺序执行未应用的脚本,并在迁移表记录水位:

CREATE TABLE IF NOT EXISTS schema_migrations (
    version     INT PRIMARY KEY,
    applied_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

实践要点:

  1. 迁移只前进不回退:写错了就补一个新版本修正,避免在生产做"回滚幻想";
  2. 每个迁移文件幂等友好(CREATE TABLE IF NOT EXISTS 等),重复执行不炸;
  3. 破坏性变更(删列)分两步:先停写 → 下个版本再删。

3. 典型 DDL 示例

CREATE TABLE IF NOT EXISTS users (
    id            TEXT PRIMARY KEY,
    username      TEXT UNIQUE,
    password_hash TEXT,
    wx_openid     TEXT UNIQUE,
    nickname      TEXT NOT NULL DEFAULT '',
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS progress (
    user_id    TEXT NOT NULL REFERENCES users(id),
    subject    TEXT NOT NULL,   -- math | chinese | english
    level      INT  NOT NULL,
    stars      INT  NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, subject, level)
);

复合主键天然防重复写入,配合 ON CONFLICT DO UPDATE 实现"打到更好成绩才覆盖":

INSERT INTO progress (user_id, subject, level, stars)
VALUES ($1, $2, $3, $4)
ON CONFLICT (user_id, subject, level)
DO UPDATE SET stars = GREATEST(progress.stars, EXCLUDED.stars),
              updated_at = now();

4. 时间与时区

  • 所有时间列使用 TIMESTAMPTZ,"每日"逻辑按 Asia/Shanghai 计算日期边界;
  • 每日小龙圈的"今天"由服务端统一判定,绝不信任客户端时间。

5. 开发流程

  1. 日常 STORE=memory 联调,改接口先改 Go 结构体;
  2. 涉及持久化字段时,先写迁移 SQL,再改 postgres store 实现;
  3. 提交前跑一遍 Makefile 的迁移 + 集成目标,保证 SQL 与代码同步。

6. 索引与容量

  • 查询热路径:按 user_id 查进度 / 签到 → 主键已覆盖,无需额外索引;
  • rooms 房间码建唯一索引,房间带 expires_at,定期清理过期房间。

7. 小结

  • 四张核心表 + 复合主键,模型小而清晰;
  • 序号迁移 + 水位表,只前进不回退;
  • 时间统一 TIMESTAMPTZ,"每日"边界由服务端裁决。

下一篇:05 · 微信登录与 JWT 鉴权

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。