我有一個條件，我需要檢查天氣值是否存在于 mysql 中，如果值存在，則我們需要更新，如果不存在，則我們需要插入。在這里我正在使用這個條件，但由于異步任務 nodejs 不等待回應，當回應到來時它不檢查它只是插入資料，請看一下
db_connection.js
check_by_column(tablename, selectedKeys, values, selectedValues, callback) {
const show_key = [];
for (const [key, value] of Object.entries(values)) {
show_key.push(key " = " "'" value "'");
}
const column = show_key.join(" ");
var sql = "SELECT " selectedKeys " FROM " tablename " WHERE " column;
console.log(sql)
con.query(sql, function (err, result) {
if (err) {
console.log("database Error")
callback(err);
// throw err
} else {
if (result && result.length == 0 ) {
callback(5);
} else {
callback(3);
}
}
return;
})
}
insert_value(tablename, values, callback) {
const show_key = [];
const show_Values = [];
for (const [key, value] of Object.entries(values)) {
show_key.push(key);
show_Values.push(value);
}
const nKey = show_key.join(", ");
const nValue = show_Values.join("', '");
let sql = "INSERT INTO " tablename " (" nKey ") VALUES ('" nValue "')";
con.query(sql, function (err, result) {
if (err) {
console.log("database Error")
console.log(err);
throw err;
}
// console.log("1 record inserted");
callback(result);
});
}
index.js
var dat = {"IMEI": 80, "rule": 2, "id": 1, "operation": 4, "address": 48, "qty": 6, "delay": 10, "data" : "3231,3039,3339", "error" : 0, "Timestamp":"2021-10-12 11:41:22"};
const topicData = JSON.stringify(dat);
var query = JSON.parse(topicData);
const currentDateTime = moment().format("YYYY-MM-DD HH:mm:ss");
const groups = [1, 2];
for (let i = 0; i < groups.length; i ) {
console.log(i)
db.check_by_column('ml', 'number', { "number": query.data }, {"topic":"80", "IMEI":query.IMEI, "id":query.id, "number": query.data, "start_date":currentDateTime, "end_date": currentDateTime}, function (response) {
if (response === 5) {
db.insert_value('ml', {"topic":"80", "IMEI":query.IMEI, "id":query.id, "number": query.data, "start_date":currentDateTime, "end_date": currentDateTime}, function(response) {
console.log("insert")
});
} else {
db.update_by_column('ml', {"topic":"80", "IMEI":query.IMEI, "id":query.id, "number": query.data, "start_date":currentDateTime, "end_date": currentDateTime}, {"number":query.data}, function(response) {
console.log("update")
});
}
});
}
回復
0
SELECT number FROM ml WHERE number = '3231,3039,3339'
1
SELECT number FROM ml WHERE number = '3231,3039,3339'
insert
insert
uj5u.com熱心網友回復：
您的資料庫呼叫是異步和非阻塞的。這意味著它們在呼叫它們后立即回傳，并且它們之后的代碼繼續執行（例如for回圈的其余部分），然后在整個for回圈完成后的某個不確定時間后呼叫完成回呼。
因此，諸如此類在for回圈內的異步操作最終會并行運行，而不是按順序運行。按順序運行它們的最簡單方法（使for回圈在推進for回圈之前等待異步操作完成）是使用async和await。然后，這需要修改您的異步操作以回傳一個可解決或拒絕而不是使用回呼的承諾。讓我們分步來看。首先，我們修改check_by_column()以回傳這樣一個承諾：
check_by_column(tablename, selectedKeys, values, selectedValues) {
return new Promise((resolve, reject) => {
const show_key = [];
for (const [key, value] of Object.entries(values)) {
show_key.push(key " = " "'" value "'");
}
const column = show_key.join(" ");
var sql = "SELECT " selectedKeys " FROM " tablename " WHERE " column;
console.log(sql)
con.query(sql, function(err, result) {
if (err) {
console.log("database Error")
reject(err);
} else {
if (result && result.length == 0) {
resolve(5);
} else {
resolve(3);
}
}
})
});
}
這是通過手動將異步操作包裝在承諾中并捕獲它可以完成的所有方式（錯誤和結果）并將它們掛鉤到承諾來完成的。注意：如果您切換到庫的 msql2 版本，它具有對承諾的內置支持，并且不需要此手動包裝器，因為您可以使用內置的承諾支持。但是，出于解釋的目的，我將展示您如何手動構建承諾。
的，你做類似的事情insert_value()：
insert_value(tablename, values, callback) {
return new Promise((resolve, reject) => {
const show_key = [];
const show_Values = [];
for (const [key, value] of Object.entries(values)) {
show_key.push(key);
show_Values.push(value);
}
const nKey = show_key.join(", ");
const nValue = show_Values.join("', '");
let sql = "INSERT INTO " tablename " (" nKey ") VALUES ('" nValue "')";
con.query(sql, function(err, result) {
if (err) {
console.log("database Error")
console.log(err);
reject(err);
return;
}
// console.log("1 record inserted");
resolve(result);
});
});
}
現在，這兩個函式都使用 promise 表示，然后我們可以使用async和await重構for回圈，以便它實際上暫停for每個資料庫操作的回圈執行，從而按順序而不是并行運行資料庫操作：
async function checkAndUpdate() {
var dat = {"IMEI": 80, "rule": 2, "id": 1, "operation": 4, "address": 48, "qty": 6, "delay": 10, "data" : "3231,3039,3339", "error" : 0, "Timestamp":"2021-10-12 11:41:22"};
const topicData = JSON.stringify(dat);
var query = JSON.parse(topicData);
const currentDateTime = moment().format("YYYY-MM-DD HH:mm:ss");
const groups = [1, 2];
for (let i = 0; i < groups.length; i ) {
console.log(i)
let response = await db.check_by_column('ml', 'number', { "number": query.data }, {"topic":"80", "IMEI":query.IMEI, "id":query.id, "number": query.data, "start_date":currentDateTime, "end_date": currentDateTime});
if (response === 5) {
let insertResult = await db.insert_value('ml', {"topic":"80", "IMEI":query.IMEI, "id":query.id, "number": query.data, "start_date":currentDateTime, "end_date": currentDateTime});
console.log("inserted")
} else {
let insertResult = await db.update_by_column('ml', {"topic":"80", "IMEI":query.IMEI, "id":query.id, "number": query.data, "start_date":currentDateTime, "end_date": currentDateTime}, {"number":query.data});
console.log("updated")
}
}
}
checkAndUpdate().then(result => {
console.log("all done");
}).catch(err => {
console.log(err);
});
One other comment, this code looks like it could be subject to race conditions because if some other code or even another request running this same code is also trying to insert this value, it may conflict with this code because you're checking for a value and then changing your behavior based on whether it already exists or not. I don't know your database well, but usually there is a single atomic operation that will insert or update depending upon whether the value already exists and you can let the database do that atomically.
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/313510.html