Discord Bot等级系统问题：module.exports提前返回值

你的判断完全正确：getUserGuildXPRank函数里的connection.query是异步回调操作，函数会直接跳过它执行return userRank，此时userRank还是初始值0，导致返回结果错误。下面是具体的修复方案：

1. 修复getUserGuildXPRank.js #

核心思路是把回调式的MySQL查询封装成Promise，用await等待异步操作完成后再返回结果，同时优化SQL查询逻辑提升性能：

module.exports = async (userId, guildId) => {
    if(!userId || !guildId) {
        console.log(`Error while performing function getUserGuildXPRank, userId or guildId missing...`);
        return 0;
    }

    try {
        // 用Promise封装MySQL查询，支持await等待结果
        const rows = await new Promise((resolve, reject) => {
            // 优化SQL：直接通过子查询计算用户排名，无需遍历所有数据
            const query = `
                SELECT COUNT(*) + 1 AS rank
                FROM \`${guildId}\`
                WHERE user_xp_total > (SELECT user_xp_total FROM \`${guildId}\` WHERE user_id = ? AND user_xp_total > 0)
            `;
            connection.query(query, [userId], (err, rows) => {
                if (err) reject(err);
                else resolve(rows);
            });
        });

        if (!rows || rows.length === 0) {
            console.log(`[MYSQL  ] Function getUserGuildXPRank returned no result.`);
            return 0;
        }

        const userRank = rows[0].rank || 0;
        console.log(`DEBUG: userRank: ${userRank}`);
        return userRank;
    } catch (error) {
        console.log(`[XP    ] Couldn't perform function getUserGuildXPRank: ${error}`);
        return 0;
    }
}

修复说明： #

• 用Promise包装connection.query，让异步操作可以被await阻塞，确保拿到查询结果后再返回userRank
• 优化SQL语句：直接统计比当前用户XP高的人数，加1即为排名，避免查询全表数据再循环匹配的低效操作
• 增加边界情况处理，比如用户无有效XP数据时返回0

2. 优化命令处理文件 #

同样把命令中的回调式查询改成Promise封装，保持代码风格一致，同时避免SQL注入风险：

const getUserGuildXPRank = require('../../utils/getUserGuildXPRank');
module.exports = {
    // some more code here
    callback: async (bot, interaction) => {
        const mentionUserId = interaction.options.get('target-user')?.value;
        const targetUserId = mentionUserId || interaction.member.id;
        const targetUserObject = await interaction.guild.members.fetch(targetUserId);

        const userRank = await getUserGuildXPRank(targetUserId, interaction.guild.id);

        try {
            const rows = await new Promise((resolve, reject) => {
                // 使用参数化查询替代字符串拼接，防止SQL注入
                const query = `SELECT \`user_xp\`, \`user_level\` FROM \`${interaction.guild.id}\` WHERE \`user_id\` = ? LIMIT 1`;
                connection.query(query, [targetUserId], (err, rows) => {
                    if (err) reject(err);
                    else resolve(rows);
                });
            });

            if (!rows || rows.length === 0) {
                return interaction.editReply(`I could not find any information at the moment, please try again later.`);
            }

            console.log(`DEBUG LEVEL userRank: ${userRank}`);
            // some more code here

        } catch (err) {
            console.log(`[MYSQL  ] Error while receiving user information in command level for user ${targetUserId} in guild ${interaction.guild.id}: ${err}`);
        }
        return;
    }
}

优化说明： #

• 用Promise封装查询，避免回调嵌套，代码逻辑更清晰
• 使用?占位符的参数化查询，代替直接拼接用户ID，彻底避免SQL注入风险

内容的提问来源于stack exchange，提问作者Phil-Absynth