【Unity MySQL小白教程⑤】HTTP API中间层实战（Node.js + Express）

前言

前四篇我们在Unity里直连MySQL跑通了CRUD。但正如第1篇强调的：生产环境严禁客户端直连数据库。本篇搭建一个Node.js + Express的HTTP API中间层，Unity客户端通过HTTP请求与服务端交互，密码和SQL永远不离开服务器。

一、为什么必须加API中间层？

风险	直连MySQL ❌	HTTP API ✅
密码泄露	连接字符串可被反编译	密码只在服务器内存中
SQL注入	客户端构造SQL	服务端参数化查询
权限失控	玩家能执行任意SQL	接口白名单+鉴权
并发瓶颈	每个客户端占一个DB连接	连接池复用+限流
业务校验	无法保证	服务端统一校验
审计日志	无	全链路请求日志

二、技术栈选择

运行时:    Node.js 20 LTS
框架:      Express 4.x
数据库驱动: mysql2 (支持async/await + prepared statements)
验证:      jsonwebtoken (JWT)
密码哈希:  bcryptjs
校验:      express-validator
部署:      PM2 / Docker

💡 选Node.js是因为前后端统一JavaScript/TypeScript生态，上手最快。Go/C#/Python同样可行，架构思路完全一致。

三、项目结构

game-api/
├── package.json
├── .env                  # 环境变量(不入Git)
├── src/
│   ├── app.js            # Express入口
│   ├── config/
│   │   └── db.js         # mysql2连接池
│   ├── middleware/
│   │   └── auth.js       # JWT验证中间件
│   ├── routes/
│   │   └── player.js     # 玩家路由
│   ├── controllers/
│   │   └── playerController.js  # 业务逻辑
│   └── models/
│       └── playerModel.js       # SQL操作
└── logs/

四、核心代码实现

4.1 数据库连接池

// src/config/db.js
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: process.env.DB_HOST || '127.0.0.1',
    port: parseInt(process.env.DB_PORT || '3306'),
    user: process.env.DB_USER || 'game_app',
    password: process.env.DB_PASS,
    database: process.env.DB_NAME || 'game_db',
    charset: 'utf8mb4',
    waitForConnections: true,
    connectionLimit: 20,
    queueLimit: 0,
    enableKeepAlive: true,
});

module.exports = pool;

4.2 玩家Model层（SQL操作）

// src/models/playerModel.js
const pool = require('../config/db');

class PlayerModel {
    static async findByUsername(username) {
        const [rows] = await pool.execute(
            'SELECT * FROM players WHERE username = ? AND status != 3 LIMIT 1',
            [username]
        );
        return rows[0] || null;
    }

    static async findById(id) {
        const [rows] = await pool.execute(
            'SELECT id, username, nickname, email, level, exp, gold, diamond, avatar_id, status, created_at FROM players WHERE id = ? AND status != 3',
            [id]
        );
        return rows[0] || null;
    }

    static async create(data) {
        const [result] = await pool.execute(
            `INSERT INTO players (username, nickname, email, password_hash, level, gold, diamond)
             VALUES (?, ?, ?, ?, ?, ?, ?)`,
            [data.username, data.nickname, data.email || null, data.passwordHash, 1, 1000, 10]
        );
        return result.insertId;
    }

    static async updateGold(playerId, delta) {
        const [result] = await pool.execute(
            'UPDATE players SET gold = gold + ? WHERE id = ? AND status != 3',
            [delta, playerId]
        );
        return result.affectedRows;
    }

    static async getLeaderboard(limit = 50) {
        const [rows] = await pool.execute(
            'SELECT id, nickname, level, gold FROM players WHERE status = 1 ORDER BY gold DESC LIMIT ?',
            [limit]
        );
        return rows;
    }
}

module.exports = PlayerModel;

⚠️ 注意：pool.execute() 使用prepared statements，天然防SQL注入。永远不要用 pool.query() 拼接字符串。

4.3 Controller层（业务逻辑）

// src/controllers/playerController.js
const bcrypt = require('bcryptjs');
const jwt = require('jsonwebtoken');
const PlayerModel = require('../models/playerModel');

class PlayerController {
    // POST /api/player/register
    static async register(req, res) {
        try {
            const { username, nickname, password } = req.body;

            // 检查用户名是否已存在
            const existing = await PlayerModel.findByUsername(username);
            if (existing) return res.status(409).json({ error: '用户名已存在' });

            // 哈希密码
            const passwordHash = await bcrypt.hash(password, 12);

            // 创建玩家
            const id = await PlayerModel.create({ username, nickname, passwordHash });
            res.status(201).json({ id, message: '注册成功' });
        } catch (err) {
            res.status(500).json({ error: err.message });
        }
    }

    // POST /api/player/login
    static async login(req, res) {
        try {
            const { username, password } = req.body;
            const player = await PlayerModel.findByUsername(username);

            if (!player || !(await bcrypt.compare(password, player.password_hash))) {
                return res.status(401).json({ error: '用户名或密码错误' });
            }

            // 签发JWT
            const token = jwt.sign(
                { playerId: player.id, username: player.username },
                process.env.JWT_SECRET,
                { expiresIn: '7d' }
            );

            res.json({ token, player: { id: player.id, nickname: player.nickname, level: player.level } });
        } catch (err) {
            res.status(500).json({ error: err.message });
        }
    }

    // GET /api/player/:id
    static async getPlayer(req, res) {
        try {
            const player = await PlayerModel.findById(req.params.id);
            if (!player) return res.status(404).json({ error: '玩家不存在' });
            res.json(player);
        } catch (err) {
            res.status(500).json({ error: err.message });
        }
    }

    // GET /api/leaderboard
    static async getLeaderboard(req, res) {
        try {
            const limit = Math.min(parseInt(req.query.limit) || 50, 100);
            const list = await PlayerModel.getLeaderboard(limit);
            res.json(list);
        } catch (err) {
            res.status(500).json({ error: err.message });
        }
    }
}

module.exports = PlayerController;

4.4 JWT验证中间件

// src/middleware/auth.js
const jwt = require('jsonwebtoken');

function authMiddleware(req, res, next) {
    const header = req.headers.authorization;
    if (!header || !header.startsWith('Bearer ')) {
        return res.status(401).json({ error: '未提供Token' });
    }

    try {
        const token = header.slice(7);
        req.user = jwt.verify(token, process.env.JWT_SECRET);
        next();
    } catch {
        res.status(401).json({ error: 'Token无效或已过期' });
    }
}

module.exports = authMiddleware;

4.5 路由注册

// src/routes/player.js
const express = require('express');
const router = express.Router();
const PlayerController = require('../controllers/playerController');
const auth = require('../middleware/auth');

router.post('/register', PlayerController.register);
router.post('/login', PlayerController.login);
router.get('/leaderboard', PlayerController.getLeaderboard);
router.get('/:id', auth, PlayerController.getPlayer);  // 需要登录

module.exports = router;

4.6 Express入口

// src/app.js
require('dotenv').config();
const express = require('express');
const playerRoutes = require('./routes/player');

const app = express();
app.use(express.json());
app.use('/api/player', playerRoutes);

app.listen(process.env.PORT || 3000, () => {
    console.log(`🚀 Game API running on port ${process.env.PORT || 3000}`);
});

五、Unity客户端调用

// Assets/Scripts/Network/GameApiClient.cs
using System.Text;
using System.Threading.Tasks;
using UnityEngine;
using UnityEngine.Networking;

public static class GameApiClient
{
    private const string BASE_URL = "http://localhost:3000/api";
    private static string _token;

    public static async Task<string> LoginAsync(string username, string password)
    {
        var json = JsonUtility.ToJson(new LoginRequest { username = username, password = password });
        using var req = new UnityWebRequest($"{BASE_URL}/player/login", "POST");
        req.uploadHandler = new UploadHandlerRaw(Encoding.UTF8.GetBytes(json));
        req.downloadHandler = new DownloadHandlerBuffer();
        req.SetRequestHeader("Content-Type", "application/json");
        await req.SendWebRequest();

        if (req.result != UnityWebRequest.Result.Success)
        {
            Debug.LogError($"Login failed: {req.error}");
            return null;
        }

        var resp = JsonUtility.FromJson<LoginResponse>(req.downloadHandler.text);
        _token = resp.token;
        Debug.Log($"✅ 登录成功: {resp.player.nickname}");
        return _token;
    }

    public static async Task<string> GetPlayerAsync(long id)
    {
        using var req = UnityWebRequest.Get($"{BASE_URL}/player/{id}");
        req.SetRequestHeader("Authorization", $"Bearer {_token}");
        await req.SendWebRequest();
        return req.downloadHandler.text;
    }

    [System.Serializable] private class LoginRequest { public string username; public string password; }
    [System.Serializable] private class LoginResponse { public string token; public PlayerInfo player; }
    [System.Serializable] private class PlayerInfo { public long id; public string nickname; public int level; }
}

六、API安全Checklist

检查项	实现方式
✅ 密码BCrypt哈希	bcryptjs saltRounds=12
✅ JWT签名验证	HS256 + 环境变量SECRET
✅ Token过期机制	expiresIn: 7d
✅ 参数化查询	mysql2 execute() prepared statements
✅ 输入长度校验	express-validator (建议添加)
✅ CORS限制	cors中间件白名单域名
✅ 速率限制	express-rate-limit (建议添加)
✅ HTTPS传输	Nginx反向代理 + Let’s Encrypt
✅ 敏感配置外置	.env文件 + dotenv
✅ 错误信息脱敏	生产环境不暴露stack trace

总结

本篇完成了从”客户端直连”到”API中间层”的关键跃迁。Unity只发HTTP请求，MySQL只在服务器上被访问——这条边界一旦划清，安全性就上了一个量级。Node.js + Express只是载体，换成Go Gin / C# ASP.NET / Python FastAPI，架构思路完全相同。

下一篇是终章实战：把前面所有知识串联成一个完整的玩家管理系统Demo。

---

系列导航
← 第4篇：增删改查完整实现与DAO封装
第6篇：完整项目实战——玩家管理系统 →