javascript - 如何重复执行nodejs mysql查询直到得到期望的结果?
问题描述
conn.connect(function (err) {
if (err) throw err;
var prizeCode = generatePrizeCode();// this method return a 5-digit code
var query = "SELECT * FROM user WHERE code = ? ";
var values = [[prizeCode]];
conn.query(query, [values], function (err, result) {
if (err) throw err;
// Here I want to re-execute the above query if the result is not empty
});
});
在上面的代码中,只要结果包含数据,我就想执行查询。但我不能像下面的伪代码那样使用循环,
// if the result is empty, the generated code does not exist in database. So it can be used.
while(result.length != 0){
var result = conn.query("SELECT * FROM user WHERE code = abc");
}
我怎样才能做到这一点?
解决方案
您必须使用回调或承诺/异步函数。以下是如何使用回调编写它:
const mysql = require('mysql');
const retryLimit = 50;
const connection = mysql.createConnection({
database: 'test',
host: 'localhost',
password: 'hunter2',
user: 'dave',
});
function getData(attempts, cb) {
if (attempts < retryLimit) { // we haven't exausted our attempts yet
const code = generatePrizeCode();
connection.query('SELECT * FROM user WHERE code = ?', code, (err, result) => {
if (err) {
return cb(err);
}
if (result.length > 0) { // code already exists
getData(attempts + 1, cb); // recurse
} else { // this is a new code
cb(null, code); // return the new code via the callback function
}
});
} else { // we have exausted our attempts
return cb(new Error('retry limit exceeded'));
}
}
getData(0, (err, code) => {
// do what you want here, e.g., console.log(code)
connection.end();
});
请注意,如果您递归太多次并超过最大调用堆栈,这可能会导致节点崩溃。一种解决方法是调用 setImmediate 或 setTimeout 而不是直接递归。而不是线
getData(attempts + 1, cb); // recurse
利用
setImmediate(() => {
getData(attempts + 1, cb); // recurse
});
或者
// recurse, using setImmediate every 1000th time
if (attempts % 1000 === 0) {
setImmediate(() => {
getData(attempts + 1, cb); // recurse
});
} else {
getData(attempts + 1, cb); // recurse
}
使用 promises 和 async/await 风格会清理很多,并且可能看起来更像你习惯的(注意这使用了promise-mysql
库):
const mysql = require('promise-mysql');
async function getData(connection) {
const retryLimit = 50;
for (let i = 0; i < retryLimit; i++) {
const code = generatePrizeCode();
const result = await connection.query('SELECT * FROM user WHERE code = ?', code);
if (result.length === 0) { // this code doesn't exist yet
return code;
}
}
throw new Error('retry limit exceeded');
}
(async () => {
try {
const connection = await mysql.createConnection({
database: 'test',
host: 'localhost',
password: 'hunter2',
user: 'dave',
});
try {
const code = await getData(connection);
// do what you want here, e.g., console.log(code)
} finally {
connection.end();
}
} catch (err) {
console.log(err);
}
})();