如何将Mongoose代码转为MySQL代码并实现API数据入库（React+Node）

一、先创建MySQL表结构 #

首先在MySQL数据库中创建匹配业务需求的数据表：

CREATE TABLE IF NOT EXISTS stats (
    id INT PRIMARY KEY, -- 玩家ID作为唯一主键，避免重复数据
    position INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    alliance VARCHAR(255),
    score INT NOT NULL,
    status VARCHAR(50),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

二、Mongoose代码转MySQL版本（自动拉取API数据入库） #

使用mysql2/promise库（支持async/await，比回调更易维护），替换原Mongoose逻辑：

import fetch from 'node-fetch';
import mysql from 'mysql2/promise';

// MySQL数据库连接配置
const dbConfig = {
  host: 'localhost',
  user: '你的MySQL用户名',
  password: '你的MySQL密码',
  database: 'highscore' // 对应你创建的数据库名
};

async function fetchAndSavePlayers() {
  try {
    // 1. 建立MySQL连接
    const connection = await mysql.createConnection(dbConfig);
    
    // 2. 拉取两个API的数据
    const rankRes = await fetch("http://localhost:3008/api/highscore/players");
    const rankData = await rankRes.json();
    const playerRes = await fetch("http://localhost:3008/api/players");
    const playerData = await playerRes.json();

    // 3. 合并玩家基础信息与排名数据
    const mergedPlayers = playerData.players.map(player => {
      const rankInfo = rankData.players.find(r => r.id === player.id);
      return {
        id: player.id,
        name: player.name,
        status: player.status,
        alliance: player.alliance,
        position: rankInfo?.position || 0,
        score: rankInfo?.score || 0
      };
    });

    // 4. 批量插入/更新数据（存在则更新，避免重复）
    const insertQuery = `
      INSERT INTO stats (id, position, name, alliance, score, status)
      VALUES ?
      ON DUPLICATE KEY UPDATE
        position = VALUES(position),
        score = VALUES(score),
        alliance = VALUES(alliance),
        status = VALUES(status),
        updated_at = CURRENT_TIMESTAMP
    `;
    // 转换为MySQL批量插入所需的二维数组格式
    const values = mergedPlayers.map(p => [p.id, p.position, p.name, p.alliance, p.score, p.status]);
    await connection.query(insertQuery, [values]);

    console.log('数据同步完成');
    await connection.end();
  } catch (err) {
    console.error('同步失败:', err);
  }
}

// 立即执行一次测试
fetchAndSavePlayers();

三、后端接口整合与定时任务 #

1. 完善Express后端（含定时任务） #

把自动同步逻辑和前端查询接口整合，同时添加每日定时任务：

const express = require('express');
const mysql = require('mysql2/promise');
const schedule = require('node-schedule');
const app = express();
app.use(express.json());

const dbConfig = { /* 同上面的数据库配置 */ };

// 前端获取玩家数据的接口
app.get('/api/players', async (req, res) => {
  try {
    const connection = await mysql.createConnection(dbConfig);
    const [rows] = await connection.query('SELECT * FROM stats ORDER BY position ASC');
    await connection.end();
    res.json({ players: rows });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// 手动触发同步的接口（可选）
app.post('/api/sync-players', async (req, res) => {
  try {
    await fetchAndSavePlayers();
    res.send('同步成功');
  } catch (err) {
    res.status(500).send('同步失败');
  }
});

// 每日凌晨2点自动同步数据
schedule.scheduleJob('0 0 2 * * *', () => {
  console.log('开始每日数据同步...');
  fetchAndSavePlayers();
});

app.listen(3000, () => console.log('后端服务运行在3000端口'));

需先安装依赖：npm install express mysql2 node-schedule node-fetch

2. 替换原有手动插入接口 #

原/create接口可被上述批量同步逻辑替代，若需保留手动插入功能，可调整为单条数据的插入/更新：

app.post('/create', async (req, res) => {
  const { position, name, alliance, points, id, status } = req.body;
  try {
    const connection = await mysql.createConnection(dbConfig);
    await connection.query(
      `INSERT INTO stats (id, position, name, alliance, score, status)
       VALUES (?, ?, ?, ?, ?, ?)
       ON DUPLICATE KEY UPDATE
         position = VALUES(position),
         score = VALUES(score),
         alliance = VALUES(alliance),
         status = VALUES(status)`,
      [id, position, name, alliance, points, status]
    );
    await connection.end();
    res.send('数据已保存');
  } catch (err) {
    console.log(err);
    res.status(500).send('保存失败');
  }
});

四、前端代码优化 #

调整为调用自己后端的接口，简化前端逻辑：

import { useState, useEffect } from 'react';
import axios from 'axios';

const Table = () => {
  const [players, setPlayers] = useState([]);
  const [perPage, setPerPage] = useState(10);
  const [currentPage, setCurrentPage] = useState(1);

  const fetchData = async () => {
    try {
      const res = await axios.get('http://localhost:3000/api/players');
      setPlayers(res.data.players);
    } catch (err) {
      console.error('获取数据失败:', err);
    }
  };

  useEffect(() => {
    fetchData();
    // 可选：每5分钟自动刷新前端数据
    const interval = setInterval(fetchData, 5 * 60 * 1000);
    return () => clearInterval(interval);
  }, []);

  // 分页逻辑
  const startIndex = (currentPage - 1) * perPage;
  const currentPlayers = players.slice(startIndex, startIndex + perPage);

  return (
    <div>
      {/* 你的表格渲染代码，使用currentPlayers即可 */}
    </div>
  );
};

export default Table;

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