04 · 数据库设计与迁移实践
【摘要】 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()
);
实践要点:
- 迁移只前进不回退:写错了就补一个新版本修正,避免在生产做"回滚幻想";
- 每个迁移文件幂等友好(
CREATE TABLE IF NOT EXISTS等),重复执行不炸; - 破坏性变更(删列)分两步:先停写 → 下个版本再删。
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. 开发流程
- 日常
STORE=memory联调,改接口先改 Go 结构体; - 涉及持久化字段时,先写迁移 SQL,再改 postgres store 实现;
- 提交前跑一遍
Makefile的迁移 + 集成目标,保证 SQL 与代码同步。
6. 索引与容量
- 查询热路径:按
user_id查进度 / 签到 → 主键已覆盖,无需额外索引; rooms房间码建唯一索引,房间带expires_at,定期清理过期房间。
7. 小结
- 四张核心表 + 复合主键,模型小而清晰;
- 序号迁移 + 水位表,只前进不回退;
- 时间统一 TIMESTAMPTZ,"每日"边界由服务端裁决。
下一篇:05 · 微信登录与 JWT 鉴权
【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)