如何在NodeJS中制作查询MySQL的函数

Question

我做了这个：

const mysql = require('mysql2/promise')
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: '',
database: 'nodejs',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
})
async function query(query) {
const result = await pool.query(query)
return result[0]
}
console.log(query('SELECT * FROM `users`'))

我得到了

Promise { <pending> }

如何像PHP一样从查询数据库中获取结果
在PHP中，我从未做过像async/await和promise这样的事情

我还尝试过使用mysql:

const mysql = require('mysql')
const db = mysql.createConnection({
host     : 'localhost',
user     : 'root',
password : '',
database : 'nodejs'
})
function query(query) {
db.query(query, (err, result) => {
if (err) throw err
return result
})
}
console.log(query('SELECT * FROM `users`'))

但我得到了一个未定义的结果

Answer 1

我对MySQL和您正在使用的库不是很熟悉。

但是，您得到的Promise { <pending> }响应是因为您没有等待查询执行。

由于该函数被标记为async，并且也在执行异步操作，因此它返回一个需要等待解析的Promise。

下面的代码应该可以工作：

const mysql = require('mysql2/promise')
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: '',
database: 'nodejs',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
})
async function query(query) {
const result = await pool.query(query)
return result[0]
}
(async () => {
const queryResult = await query('SELECT * FROM `users`');
console.log(queryResult);
} )();

要了解异步等待是如何工作的，请考虑以下代码：

console.log('I will get printed first');
const asyncFunction = async () => {
await setTimeout(()=> {}, 1000)
console.log('I will get printed third');
return 'hello'
}
(async () => {
const result = await asyncFunction();
console.log(`I will get printed last with result: ${result}`);
})();
console.log('I will get printed second');

console.log语句I will get printed last with result将等待asyncFunction完成执行后再执行。

Answer 2

试试这个：

var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "yourusername",
password: "yourpassword",
database: "mydb"
});
// function definition
function runQuery (con, sqlQuery) {
return new Promise((resolve, reject) => {
console.log("START");
if(con){
con.connect(function (err) {
if (err) throw err;
});
if (sqlQuery) {
con.query(sqlQuery, function (error, result, fields) {
connection.end(); // end connection
if (error) {
throw error;
} else {
return resolve(result);
}
});
} else {
connection.end(); // end connection
// code:  handle the case 
}
} else {
// code: handle the case
}
});
}
var sqlQuery = 'SELECT * FROM tableName';
// function call and pass the connection and sql query you want to execute
var p  = runQuery(con, sqlQuery); 
p.then((data)=>{ // promise and callback function
console.log('data :', data); // result
console.log("END");
});

Answer 3

试试这个：

var mysql = require('mysql');
var con = mysql.createConnection({
host: "localhost",
user: "yourusername",
password: "yourpassword",
database: "mydb"
});
con.connect(function(err) {
if (err) throw err;
con.query("SELECT * FROM customers", function (err, result, fields) {
if (err) throw err;
console.log(result);
});
});