数据库查询函数返回undefined而非数组的问题排查求助

我写了一个从数据库获取数据并组装成数组的函数，调用它时返回的却是undefined，但实际上数组里是有数据的——把回调里的return punishments_data改成console.log(punishments_data)能看到正确的数组结构。

调用代码（输出undefined） #

const data = methods.getPunishmentData();
console.log(data);

目标函数代码 #

methods.getPunishmentData = function() {
    try {
        mysql.executeQuery(`SELECT * FROM log_punishments LIMIT 50`, function (err, rows, fields) {
            let punishments_data = [];

            rows.forEach(row => {
                const playerPunishment = {
                    time: row['time'].toString(),
                    admin: {
                        name: user.getRpName(user.getPlayerById(methods.parseInt(row['admin']))),
                        id: methods.parseInt(row['admin']),
                    },
                    type: row['type'],
                    player: {
                        name: user.getRpName(user.getPlayerById(methods.parseInt(row['player']))),
                        id: methods.parseInt(row['player']),
                    },
                    reason: row['reason']
                }

                punishments_data.push(playerPunishment);
            });

            return punishments_data;
        });
    }
    catch(ex) {
        methods.error('DB Get Punishments', ex);
    }
};

期望的返回结构 #

[
  {
    time: 'Thu Apr 13 2023 23:42:47 GMT+0200 (GMT+02:00)',
    admin: { name: 'Danuh Sus', id: 105 },
    type: 'test',
    player: { name: 'Danuh Sus', id: 105 },
    reason: 'test2'
  },
  {
    time: 'Thu Apr 13 2023 23:42:48 GMT+0200 (GMT+02:00)',
    admin: { name: 'Danuh Sus', id: 105 },
    type: 'test',
    player: { name: 'Danuh Sus', id: 105 },
    reason: 'test2'
  },
  {
    time: 'Thu Apr 13 2023 23:42:49 GMT+0200 (GMT+02:00)',
    admin: { name: 'Danuh Sus', id: 105 },
    type: 'test',
    player: { name: 'Danuh Sus', id: 105 },
    reason: 'test2'
  },
  {
    time: 'Fri Apr 14 2023 00:02:53 GMT+0200 (GMT+02:00)',
    admin: { name: 'Danuh Sus', id: 105 },
    type: 'test',
    player: { name: 'Danuh Sus', id: 105 },
    reason: 'test2'
  }
]

问题原因 #

核心是异步操作的回调函数返回值无法传递到外层函数：

• mysql.executeQuery是异步执行的，外层的getPunishmentData函数会先执行完异步调用的发起，然后直接结束（没有任何返回值，所以默认返回undefined）。
• 回调函数里的return punishments_data只是给executeQuery内部的调用逻辑返回值，和外层的getPunishmentData完全没关系。

解决方案 #

方案1：用Promise封装函数

把异步操作包装成Promise，这样可以通过then或者await获取结果：

methods.getPunishmentData = function() {
    return new Promise((resolve, reject) => {
        try {
            mysql.executeQuery(`SELECT * FROM log_punishments LIMIT 50`, function (err, rows, fields) {
                if (err) {
                    reject(err);
                    return;
                }
                let punishments_data = [];

                rows.forEach(row => {
                    const playerPunishment = {
                        time: row['time'].toString(),
                        admin: {
                            name: user.getRpName(user.getPlayerById(methods.parseInt(row['admin']))),
                            id: methods.parseInt(row['admin']),
                        },
                        type: row['type'],
                        player: {
                            name: user.getRpName(user.getPlayerById(methods.parseInt(row['player']))),
                            id: methods.parseInt(row['player']),
                        },
                        reason: row['reason']
                    }

                    punishments_data.push(playerPunishment);
                });

                resolve(punishments_data);
            });
        } catch(ex) {
            methods.error('DB Get Punishments', ex);
            reject(ex);
        }
    });
};

调用方式（用await，需要在async函数里）：

// 注意：要在async函数内使用await
async function fetchData() {
    const data = await methods.getPunishmentData();
    console.log(data);
}
fetchData();

或者用then：

methods.getPunishmentData().then(data => {
    console.log(data);
}).catch(err => {
    // 处理错误
});

方案2：给目标函数添加回调参数

如果不想用Promise，可以直接给getPunishmentData加一个回调参数，在异步操作完成后调用：

methods.getPunishmentData = function(callback) {
    try {
        mysql.executeQuery(`SELECT * FROM log_punishments LIMIT 50`, function (err, rows, fields) {
            if (err) {
                callback(err, null);
                return;
            }
            let punishments_data = [];

            rows.forEach(row => {
                const playerPunishment = {
                    time: row['time'].toString(),
                    admin: {
                        name: user.getRpName(user.getPlayerById(methods.parseInt(row['admin']))),
                        id: methods.parseInt(row['admin']),
                    },
                    type: row['type'],
                    player: {
                        name: user.getRpName(user.getPlayerById(methods.parseInt(row['player']))),
                        id: methods.parseInt(row['player']),
                    },
                    reason: row['reason']
                }

                punishments_data.push(playerPunishment);
            });

            callback(null, punishments_data);
        });
    } catch(ex) {
        methods.error('DB Get Punishments', ex);
        callback(ex, null);
    }
};

调用方式：

methods.getPunishmentData((err, data) => {
    if (err) {
        // 处理错误
        return;
    }
    console.log(data);
});

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