咨询：适合游戏数据存储的AWS服务选型（含API访问需求）

嘿，我来帮你梳理下这个游戏数据库和API的实现方案，刚好之前做过类似的游戏积分系统，给你拆解成几个核心部分一步步来：

首先先把两张表的结构定好，要兼顾唯一性约束和查询效率：

-- 表1：用户表（存储用户名和密码）
CREATE TABLE users (
    username VARCHAR(50) PRIMARY KEY, -- 用户名唯一，作为主键
    password_hash VARCHAR(255) NOT NULL -- 存加密后的密码，绝对不能明文！
);

-- 表2：用户分数表（存储用户名和分数）
CREATE TABLE user_scores (
    username VARCHAR(50) PRIMARY KEY REFERENCES users(username) ON DELETE CASCADE,
    score INT DEFAULT 0 NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 给分数加索引，方便排名查询
CREATE INDEX idx_score ON user_scores(score DESC);

这里要注意：密码必须用哈希算法（比如bcrypt）加密后存储，绝对不能直接存明文；用户分数表用用户名做主键，关联用户表的主键，确保分数和用户一一对应。

按照你的需求，设计这几个核心端点，每个端点对应一个功能：

• 注册新用户：POST /api/users

  • 请求体：{ "username": "xxx", "password": "xxx" }
  • 逻辑：检查用户名是否已存在，不存在则加密密码后插入users表，同时在user_scores初始化分数为0

• 创建/更新用户分数：PUT /api/users/{username}/score

  • 请求体：{ "score": 100 }
  • 逻辑：用UPSERT语法（比如MySQL的ON DUPLICATE KEY UPDATE，PostgreSQL的INSERT ... ON CONFLICT ... DO UPDATE），如果用户分数存在就更新，不存在就创建（不过正常注册后会初始化，这里做兜底）

• 查询指定用户的分数及排名：GET /api/users/{username}/score

  • 返回示例：{ "username": "xxx", "score": 100, "rank": 5 }
  • 逻辑：用窗口函数RANK()计算用户的排名，比如SELECT score, RANK() OVER (ORDER BY score DESC) AS rank FROM user_scores WHERE username = ?

• 获取排名前十的用户分数：GET /api/users/top-ten

  • 返回示例：[{ "username": "user1", "score": 500, "rank": 1 }, ...]
  • 逻辑：按分数倒序取前10条，同时计算排名（注意如果有并列分数，排名会相同）

• 批量查询用户名列表的分数：POST /api/users/batch-scores

  • 请求体：{ "usernames": ["user1", "user2", "user3"] }
  • 返回示例：[{ "username": "user1", "score": 500 }, { "username": "user2", "score": 300 }]
  • 逻辑：用IN子句批量查询，注意处理列表中不存在的用户名（可以过滤掉或者返回score为null）

给你写几个关键功能的代码片段，方便你参考：

1. 用户注册（密码加密） #

const bcrypt = require('bcrypt');
const mysql = require('mysql2/promise');

app.post('/api/users', async (req, res) => {
  const { username, password } = req.body;
  if (!username || !password) {
    return res.status(400).json({ error: '用户名和密码不能为空' });
  }

  try {
    const connection = await mysql.createConnection(dbConfig);
    // 检查用户名是否存在
    const [existingUsers] = await connection.execute('SELECT username FROM users WHERE username = ?', [username]);
    if (existingUsers.length > 0) {
      return res.status(409).json({ error: '用户名已存在' });
    }

    // 加密密码
    const passwordHash = await bcrypt.hash(password, 10);
    // 事务：同时插入用户表和分数表
    await connection.beginTransaction();
    await connection.execute('INSERT INTO users (username, password_hash) VALUES (?, ?)', [username, passwordHash]);
    await connection.execute('INSERT INTO user_scores (username) VALUES (?)', [username]);
    await connection.commit();

    res.status(201).json({ message: '注册成功' });
  } catch (err) {
    await connection?.rollback();
    res.status(500).json({ error: '服务器错误' });
  }
});

2. 查询用户分数及排名 #

app.get('/api/users/:username/score', async (req, res) => {
  const { username } = req.params;
  try {
    const connection = await mysql.createConnection(dbConfig);
    const [rows] = await connection.execute(`
      SELECT 
        us.score,
        (SELECT COUNT(DISTINCT score) FROM user_scores WHERE score > us.score) + 1 AS rank
      FROM user_scores us
      WHERE us.username = ?
    `, [username]);

    if (rows.length === 0) {
      return res.status(404).json({ error: '用户不存在' });
    }

    res.json({ username, score: rows[0].score, rank: rows[0].rank });
  } catch (err) {
    res.status(500).json({ error: '服务器错误' });
  }
});

3. 获取排名前十的用户 #

app.get('/api/users/top-ten', async (req, res) => {
  try {
    const connection = await mysql.createConnection(dbConfig);
    const [rows] = await connection.execute(`
      SELECT 
        username,
        score,
        RANK() OVER (ORDER BY score DESC) AS rank
      FROM user_scores
      ORDER BY score DESC
      LIMIT 10
    `);

    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: '服务器错误' });
  }
});

• 密码加密：一定要用成熟的哈希库（比如bcrypt、Argon2），不要自己写加密逻辑
• 事务处理：像注册这种涉及多张表操作的，一定要用事务保证数据一致性
• 索引优化：排名查询和分数排序一定要加索引，不然数据量大了会很慢
• 参数验证：所有传入的参数（用户名、分数、批量列表）都要做格式和合法性验证，防止SQL注入和非法数据
• 错误处理：每个API都要处理常见错误（比如用户不存在、参数错误、服务器异常），返回清晰的错误信息

内容的提问来源于stack exchange，提问作者user3648673