如何在Node.js中获取MySQL查询结果？Promise使用遇阻求助

补充报错信息 #

函数内加await时的错误日志： #

You have tried to call .then(), .catch(), or invoked await on the
result of query that is not a promise, which is a programming error.
Try calling con.promise().query(), or require('mysql2/promise')
instead of 'mysql2' for a promise-compatible version of the query
interface. To learn how to use async/await or Promises check out
documentation at
https://sidorares.github.io/node-mysql2/docs#using-promise-wrapper, or
the mysql2 documentation at
https://sidorares.github.io/node-mysql2/docs/documentation/promise-wrapper
/var/www/html/nodejs/node_modules/mysql2/lib/commands/query.js:43
throw new Error(err);
^

Error: You have tried to call .then(), .catch(), or invoked await on
the result of query that is not a promise, which is a programming
error. Try calling con.promise().query(), or require('mysql2/promise')
instead of 'mysql2' for a promise-compatible version of the query
interface. To learn how to use async/await or Promises check out
documentation at
https://sidorares.github.io/node-mysql2/docs#using-promise-wrapper, or
the mysql2 documentation at
https://sidorares.github.io/node-mysql2/docs/documentation/promise-wrapper
at Query.then (/var/www/html/nodejs/node_modules/mysql2/lib/commands/query.js:43:11)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

Node.js v18.17.1

函数外加await时的错误日志： #

SyntaxError: await is only valid in async functions and the top level
bodies of modules
at internalCompileFunction (node:internal/vm:73:18)
at wrapSafe (node:internal/modules/cjs/loader:1178:20)
at Module._compile (node:internal/modules/cjs/loader:1220:27)
at Module._extensions..js (node:internal/modules/cjs/loader:1310:10)
at Module.load (node:internal/modules/cjs/loader:1119:32)
at Module._load (node:internal/modules/cjs/loader:960:12)
at Function.executeUserEntryPoint [as runMain] (node:internal/process/run_main:81:12)
at node:internal/main/run_main_module:23:47

Node.js v18.17.1

1. 让mysql2支持Promise #

mysql2默认的query方法是回调式的，不返回Promise，所以无法直接用await。两种解决方式：

方式一：改用mysql2的Promise模块

修改引入和连接池创建代码：

const mysql = require('mysql2/promise'); // 引入Promise版本
var database = mysql.createPool({
    host: "localhost",
    user: "user",
    password: "password",
    database: "db"
});

方式二：给现有连接池添加Promise包装

如果不想更换模块引入，给已创建的连接池调用promise()方法：

const mysql = require('mysql2');
var database = mysql.createPool({
    host: "localhost",
    user: "user",
    password: "password",
    database: "db"
}).promise(); // 转为Promise兼容的连接池

2. 修正查询函数 #

现在可以正常使用await，注意解构获取结果行：

var query = "SELECT * FROM table;";
async function getResult(){
    const [rows] = await database.query(query); // 解构拿到查询结果
    return rows;
};

3. 正确获取全局作用域的结果 #

await不能直接在非async的全局作用域（CommonJS模块）中使用，两种处理方式：

方式一：使用立即执行的async函数

把需要使用结果的代码放在自执行async函数里：

(async () => {
    var result = await getResult();
    console.log(result);
    // 所有依赖result的后续代码都写在这里
})();

方式二：转为ES模块

在项目的package.json中添加"type": "module"，这样就能在顶层直接使用await：

// package.json需配置 "type": "module"
var result = await getResult();
console.log(result);

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