zrd-nb666/campus_health_app/backend/database/README.md

数据库文档

概述

本目录包含数智健调系统体重管理模块的数据库架构定义和相关脚本。

文件说明

文件名	说明	用途
schema.sql	数据库架构定义	创建所有表结构、索引、约束
init.sql	数据库初始化脚本	创建数据库和基本配置
seed.sql	测试数据种子脚本	插入测试数据用于开发
README.md	本文档	使用说明和参考

快速开始

前提条件

• MySQL 8.0+ 或 MariaDB 10.5+
• 数据库管理员权限

安装步骤

1. 创建数据库

mysql -u root -p < init.sql

这将创建名为 campus_health 的数据库。

2. 创建表结构

mysql -u root -p campus_health < schema.sql

这将创建以下表：

• users - 用户表
• weight_records - 体重记录表 ⭐
• weight_goals - 体重目标表 ⭐
• tags - 标签表
• weight_record_tags - 体重记录-标签关联表
• anomaly_logs - 异常日志表

3. 插入测试数据（可选）

mysql -u root -p campus_health < seed.sql

这将插入：

• 3个测试用户
• 90天的体重记录数据
• 1个活跃的体重目标
• 系统预设标签
• 示例异常日志

数据库架构

核心表

users（用户表）

存储用户基本信息。

关键字段：

• id: 用户唯一标识（UUID）
• phone: 手机号（唯一）
• nickname: 昵称
• school_id: 学校ID
• is_under_14: 是否未满14岁

weight_records（体重记录表）⭐

存储用户的体重测量记录。

关键字段：

• id: 记录唯一标识
• user_id: 用户ID（外键）
• record_date: 记录日期
• weight: 体重（30-200kg）
• body_fat: 体脂率（5-60%）
• muscle_mass: 肌肉含量（10-100kg）
• measurement_condition: 测量条件（空腹/餐后）

索引：

• idx_user_date: 复合索引（user_id, record_date DESC）- 高频查询优化
• idx_user_created: 复合索引（user_id, created_at DESC）- 按创建时间查询

weight_goals（体重目标表）⭐

存储用户的体重目标设置。

关键字段：

• target_weight: 目标体重
• target_body_fat: 目标体脂率
• target_date: 目标日期
• start_weight: 起始体重
• weekly_target: 每周目标减重量
• status: 目标状态（active/completed/abandoned）

tags（标签表）

存储系统预设和用户自定义标签。

系统预设标签：

• 开始运动
• 目标调整
• 饮食改变
• 生病
• 假期
• 压力期

weight_record_tags（关联表）

多对多关系表，关联体重记录和标签。

anomaly_logs（异常日志表）

记录系统检测到的异常情况。

异常类型：

• rapid_change: 快速体重变化
• body_fat_anomaly: 体脂率异常
• missing_data: 缺失数据
• extreme_value: 极端值

数据关系

users (1) -----> (N) weight_records
users (1) -----> (N) weight_goals
users (1) -----> (N) tags (自定义标签)
users (1) -----> (N) anomaly_logs

weight_records (N) <-----> (N) tags (通过 weight_record_tags)

性能优化

索引策略

1. 主键索引：所有表都有UUID主键
2. 唯一索引：users.phone, tags.uk_user_tag
3. 复合索引：
   • weight_records.idx_user_date - 按用户和日期查询
   • weight_goals.idx_user_status - 按用户和状态查询
4. 单列索引：外键字段、软删除字段

查询优化建议

-- ✅ 好的查询：使用索引
SELECT * FROM weight_records 
WHERE user_id = 'xxx' 
  AND record_date >= '2025-01-01'
  AND deleted_at IS NULL
ORDER BY record_date DESC;

-- ❌ 避免：全表扫描
SELECT * FROM weight_records 
WHERE weight > 70
  AND deleted_at IS NULL;

-- ✅ 好的查询：使用复合索引
SELECT * FROM weight_goals
WHERE user_id = 'xxx'
  AND status = 'active'
  AND deleted_at IS NULL;

数据约束

Check 约束

• weight: 30.00 - 200.00 kg
• body_fat: 5.00 - 60.00 %
• muscle_mass: 10.00 - 100.00 kg
• target_date: 必须大于 start_date

外键约束

所有外键都设置了 ON DELETE CASCADE，确保数据一致性。

备份与恢复

备份数据库

# 完整备份
mysqldump -u root -p campus_health > backup_$(date +%Y%m%d).sql

# 仅备份结构
mysqldump -u root -p --no-data campus_health > schema_backup.sql

# 仅备份数据
mysqldump -u root -p --no-create-info campus_health > data_backup.sql

恢复数据库

mysql -u root -p campus_health < backup_20251020.sql

常用查询示例

1. 获取用户最近30天体重记录

SELECT 
  record_date,
  weight,
  body_fat,
  muscle_mass
FROM weight_records
WHERE user_id = 'test-user-001'
  AND record_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND deleted_at IS NULL
ORDER BY record_date DESC;

2. 获取用户当前活跃目标

SELECT *
FROM weight_goals
WHERE user_id = 'test-user-001'
  AND status = 'active'
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 1;

3. 获取带标签的体重记录

SELECT 
  wr.record_date,
  wr.weight,
  wr.body_fat,
  GROUP_CONCAT(t.tag_name) AS tags
FROM weight_records wr
LEFT JOIN weight_record_tags wrt ON wr.id = wrt.weight_record_id
LEFT JOIN tags t ON wrt.tag_id = t.id
WHERE wr.user_id = 'test-user-001'
  AND wr.deleted_at IS NULL
GROUP BY wr.id, wr.record_date, wr.weight, wr.body_fat
ORDER BY wr.record_date DESC
LIMIT 10;

4. 计算用户体重统计

SELECT 
  COUNT(*) AS total_records,
  MIN(record_date) AS first_date,
  MAX(record_date) AS last_date,
  MIN(weight) AS min_weight,
  MAX(weight) AS max_weight,
  AVG(weight) AS avg_weight,
  (MAX(weight) - MIN(weight)) AS weight_range
FROM weight_records
WHERE user_id = 'test-user-001'
  AND deleted_at IS NULL;

5. 获取周体重变化

SELECT 
  YEARWEEK(record_date) AS week,
  AVG(weight) AS avg_weight,
  MIN(weight) AS min_weight,
  MAX(weight) AS max_weight,
  COUNT(*) AS record_count
FROM weight_records
WHERE user_id = 'test-user-001'
  AND record_date >= DATE_SUB(CURDATE(), INTERVAL 12 WEEK)
  AND deleted_at IS NULL
GROUP BY YEARWEEK(record_date)
ORDER BY week DESC;

维护建议

定期任务

1. 数据清理（建议每月执行）

   -- 清理1年前的软删除记录
   DELETE FROM weight_records 
   WHERE deleted_at IS NOT NULL 
    AND deleted_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
   

2. 索引优化（建议每季度执行）

   -- 分析表
   ANALYZE TABLE weight_records;
   ANALYZE TABLE weight_goals;
      
   -- 优化表
   OPTIMIZE TABLE weight_records;
   OPTIMIZE TABLE weight_goals;
   

3. 统计更新

   -- 更新表统计信息
   ANALYZE TABLE weight_records;
   

监控指标

• 表大小增长趋势
• 查询响应时间
• 索引使用率
• 慢查询日志

故障排查

常见问题

1. 外键约束错误

ERROR 1452: Cannot add or update a child row

解决方案：确保父表（users）中存在对应的记录。

2. Check 约束错误

ERROR 3819: Check constraint is violated

解决方案：检查数值是否在允许范围内（体重30-200kg，体脂率5-60%）。

3. 唯一键冲突

ERROR 1062: Duplicate entry

解决方案：检查是否插入了重复的手机号或用户标签组合。

版本历史

版本	日期	变更说明
1.0	2025-10-20	初始版本，包含6张核心表

联系方式

如有问题或建议，请联系开发团队。