DATABASE.md 11 KB
数据库文档
本文档说明教育游戏系统的数据库结构
1. 数据库基本信息
| 配置项 | 值 |
|---|---|
| 数据库名称 | edu_game |
| 字符集 | utf8mb4 |
| 排序规则 | utf8mb4_unicode_ci |
| 数据库引擎 | InnoDB |
2. 表结构说明
2.1 users - 用户表
存储游戏玩家的基本信息
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | 用户ID |
| username | VARCHAR(64) | NOT NULL, UNIQUE | 用户名 |
| password_hash | VARCHAR(255) | NOT NULL | 密码哈希 |
| VARCHAR(128) | NOT NULL, UNIQUE | 邮箱 | |
| study_ability | INT | NOT NULL, DEFAULT 1 | 学习力(1-100) |
| current_level | INT | NOT NULL, DEFAULT 1 | 当前等级 |
| experience | INT | NOT NULL, DEFAULT 0 | 经验值 |
| status | VARCHAR(20) | NOT NULL, DEFAULT 'NORMAL' | 状态(NORMAL/DISABLED) |
| created_at | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 创建时间 |
| updated_at | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | 更新时间 |
**索引：
uk_users_username- 用户名唯一索引uk_users_email- 邮箱唯一索引idx_users_status_level_ability- 状态、等级、学习力复合索引idx_users_created_at- 创建时间索引
2.2 admin_users - 管理员表
存储后台管理系统的管理员信息
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | 管理员ID |
| username | VARCHAR(64) | NOT NULL, UNIQUE | 用户名 |
| password_hash | VARCHAR(255) | NOT NULL | 密码哈希 |
| role | VARCHAR(32) | NOT NULL, DEFAULT 'ADMIN' | 角色(ADMIN) |
| created_at | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 创建时间 |
**索引：
uk_admin_users_username- 用户名唯一索引
2.3 difficulty_config - 难度配置表
配置难度等级与学习力的对应关系
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | ID |
| difficulty_level | INT | NOT NULL, UNIQUE | 难度等级(1-3) |
| study_ability_min | INT | NOT NULL | 学习力下限 |
| study_ability_max | INT | NOT NULL | 学习力上限 |
| description | VARCHAR(64) | NOT NULL | 描述 |
| created_at | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 创建时间 |
| updated_at | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | 更新时间 |
**索引：
uk_difficulty_config_level- 难度等级唯一索引
2.4 questions - 题库表
存储所有的数学题目
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | 题目ID |
| question_text | VARCHAR(255) | NOT NULL | 题目文本 |
| correct_answer | INT | NOT NULL | 正确答案 |
| wrong_answers_json | VARCHAR(255) | NOT NULL | 错误答案列表(JSON) |
| difficulty | INT | NOT NULL | 难度(1-3) |
| enabled | BOOLEAN | NOT NULL, DEFAULT TRUE | 是否启用 |
| deleted | BOOLEAN | NOT NULL, DEFAULT FALSE | 是否删除 |
| created_at | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 创建时间 |
| updated_at | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | 更新时间 |
**索引：
idx_questions_difficulty_enabled- 难度、启用状态复合索引idx_questions_deleted_updated- 删除状态、更新时间复合索引
2.5 game_results - 游戏结果表
存储每次游戏的结果数据
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | 记录ID |
| user_id | BIGINT | NOT NULL, FOREIGN KEY | 用户ID |
| play_time | DECIMAL(10,2) | NOT NULL | 游戏时长(秒) |
| rooms_completed | INT | NOT NULL | 完成房间数 |
| correct_answers | INT | NOT NULL | 正确答题数 |
| wrong_answers | INT | NOT NULL | 错误答题数 |
| difficulty | INT | NOT NULL | 游戏难度 |
| is_completed | BOOLEAN | NOT NULL | 是否通关 |
| gold_collected | INT | NOT NULL | 收集金币数 |
| created_at | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 创建时间 |
**索引：
idx_game_results_user_created_at- 用户ID、创建时间复合索引idx_game_results_difficulty_created_at- 难度、创建时间复合索引idx_game_results_created_at- 创建时间索引idx_game_results_completed_created_at- 通关状态、创建时间复合索引
2.6 game_sessions - 游戏会话表
存储游戏会话记录
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | 会话ID |
| user_id | BIGINT | NOT NULL, FOREIGN KEY | 用户ID |
| play_time | DECIMAL(10,2) | NOT NULL | 游戏时长(秒) |
| progress | INT | NOT NULL | 进度 |
| difficulty | INT | NOT NULL | 难度 |
| session_date | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 会话时间 |
**索引：
idx_game_sessions_user_session_date- 用户ID、会话时间复合索引
2.7 review_sessions - 复习会话表
存储复习模式的会话记录
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | 会话ID |
| user_id | BIGINT | NOT NULL, FOREIGN KEY | 用户ID |
| difficulty | INT | NOT NULL | 难度 |
| status | VARCHAR(20) | NOT NULL, DEFAULT 'ACTIVE' | 状态(ACTIVE/COMPLETED) |
| created_at | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 创建时间 |
| updated_at | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | 更新时间 |
**索引：
idx_review_sessions_user_created_at- 用户ID、创建时间复合索引idx_review_sessions_difficulty_created_at- 难度、创建时间复合索引idx_review_sessions_status_created_at- 状态、创建时间复合索引
2.8 user_question_attempts - 用户答题记录表
记录用户每道题的答题情况
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | 记录ID |
| user_id | BIGINT | NOT NULL, FOREIGN KEY | 用户ID |
| question_id | BIGINT | NOT NULL, FOREIGN KEY | 题目ID |
| is_correct | BOOLEAN | NOT NULL | 是否答对 |
| difficulty | INT | NOT NULL | 难度 |
| source_type | VARCHAR(20) | NOT NULL | 来源(GAME/REVIEW) |
| game_result_id | BIGINT | NULL, FOREIGN KEY | 关联的游戏结果ID |
| review_session_id | BIGINT | NULL, FOREIGN KEY | 关联的复习会话ID |
| answered_at | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP | 答题时间 |
**索引：
idx_user_question_attempts_user_question- 用户ID、题目ID复合索引idx_user_question_attempts_review_session- 复习会话ID索引idx_user_question_attempts_game_result- 游戏结果ID索引idx_user_question_attempts_difficulty- 难度索引idx_user_question_attempts_user_answered_at- 用户ID、答题时间复合索引idx_user_question_attempts_question_answered_at- 题目ID、答题时间复合索引idx_user_question_attempts_source_difficulty_answered_at- 来源、难度、答题时间复合索引
2.9 user_achievements - 用户成就表
存储用户的成就进度
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | 记录ID |
| user_id | BIGINT | NOT NULL, FOREIGN KEY | 用户ID |
| achievement_id | VARCHAR(128) | NOT NULL | 成就ID |
| current_value | INT | NOT NULL, DEFAULT 0 | 当前进度值 |
| is_unlocked | BOOLEAN | NOT NULL, DEFAULT FALSE | 是否已解锁 |
| unlocked_at | DATETIME | NULL | 解锁时间 |
| updated_at | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | 更新时间 |
**索引：
uk_user_achievements_user_achievement- 用户ID、成就ID唯一索引idx_user_achievements_unlocked- 成就ID、解锁状态复合索引idx_user_achievements_user_updated- 用户ID、更新时间复合索引
2.10 question_stats - 题目统计表
统计每道题的答题数据
| 字段名 | 类型 | 约束 | 说明 |
|---|---|---|---|
| id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | 记录ID |
| question_id | BIGINT | NOT NULL, FOREIGN KEY, UNIQUE | 题目ID |
| difficulty | INT | NOT NULL | 难度 |
| total_attempts | INT | NOT NULL, DEFAULT 0 | 总答题次数 |
| correct_attempts | INT | NOT NULL, DEFAULT 0 | 正确答题次数 |
| updated_at | DATETIME | NOT NULL, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | 更新时间 |
**索引：
uk_question_stats_question- 题目ID唯一索引idx_question_stats_difficulty- 难度索引
3. 表关系图
users (用户表)
├───< game_results (游戏结果)
├───< game_sessions (游戏会话)
├───< review_sessions (复习会话)
├───< user_question_attempts (用户答题记录)
└───< user_achievements (用户成就)
questions (题库表)
├───< user_question_attempts (用户答题记录)
└───< question_stats (题目统计)
game_results (游戏结果)
└───< user_question_attempts (用户答题记录)
review_sessions (复习会话)
└───< user_question_attempts (用户答题记录)
difficulty_config (难度配置表) - 独立表
admin_users (管理员表) - 独立表
4. 初始化数据
4.1 难度配置数据
系统初始化时会插入以下难度配置：
| difficulty_level | study_ability_min | study_ability_max | description |
|---|---|---|---|
| 1 | 1 | 33 | 简单难度 |
| 2 | 34 | 66 | 中等难度 |
| 3 | 67 | 100 | 困难难度 |
4.2 管理员账号
| username | password | role |
|---|---|---|
| admin | admin123 | ADMIN |
4.3 示例题目
初始化脚本会生成大量示例题目，涵盖三个难度级别，主要包括：
- 难度1：加法运算（如 1+1、3+4等）
- 难度2：乘法运算（如 6×7、81÷9等）
- 难度3：复合运算（如 (20×16)-16等）
5. 常见查询示例
5.1 查询用户信息
SELECT id, username, email, study_ability, current_level, experience
FROM users
WHERE id = ?;
5.2 获取指定难度的题目
SELECT id, question_text, correct_answer, wrong_answers_json, difficulty
FROM questions
WHERE difficulty = ?
AND enabled = TRUE
AND deleted = FALSE
ORDER BY RAND()
LIMIT 100;
5.3 查询用户的游戏历史
SELECT id, play_time, rooms_completed, correct_answers, wrong_answers, difficulty, is_completed, created_at
FROM game_results
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 50;
5.4 查询用户的成就进度
SELECT achievement_id, current_value, is_unlocked, unlocked_at
FROM user_achievements
WHERE user_id = ?;
6. 维护建议
6.1 定期备份
建议定期备份数据库，可使用以下命令：
mysqldump -u root -p edu_game > backup_$(date +%Y%m%d).sql
6.2 索引维护
随着数据量增长，定期分析表性能，必要时优化索引
6.3 数据清理
对于历史数据，可根据需要进行归档或清理