mysql - Node JS mySQL 三重查询和信息丢失

Question

在我的应用程序中，我有两个不同的表，通过第一个表的 ID 相互关联(一对多关系)。它应该首先以 JSON 格式从前端收集数据，如下所示:

cancellation = {
name: someting
id: someting
rule = 
[
 {someting}, {something}, {something}
] 
}

一张表用于取消，第二张表用于规则。如果我想按此顺序放置这些信息，我需要首先插入一条记录以进行取消。然后查询该记录在数据库中的ID是什么，然后使用该ID作为外键插入所有规则。但由于 Node JS 是异步的，所以在我获取记录程序星号的 ID 信息以执行其余代码之前，并认为该变量未定义。

app.post('/databaseSend/cancellation', function(req,res){
var cancellationReceived = req.body;
var cancellationID;
var rules = [];
var cancellation = [];
cancellation[0] = 
[
    cancellationReceived.name,
    cancellationReceived.id
]
// inserting data into cancellation table
connection.query("INSERT INTO cancellations (name, User_ID) VALUES ?", [cancellation],
    function(err,results){
        if(err){console.log(err)}

    }
)
//fetching ID of the current record
connection.query("SELECT id FROM cancellations WHERE User_ID = ? AND name = ?", [cancellationReceived.id, cancellationReceived.name],
    function(err, results){
        var cancellationID = results[0].id;
    });


//assigning ID to use it as a foreign key
for(var i = 0; i < cancellationReceived.rule.length; i++)
{
    rules[i] = 
    [
        cancellationReceived.rule[i].daysBefore,
        cancellationReceived.rule[i].fee,
        cancellationReceived.rule[i].type,
        cancellationID
    ]
}

for(var i = 0; i < rules.length; i++)
{
    console.log(rules[i]); // ID is undefined
}

});

如何解决这个问题？我尝试使用 setTimeout 来暂停我的代码，但它没有改变任何东西。

并且我将这个 Node 模块用于mysql -> https://github.com/mysqljs/mysql

Answer 1

解决这个问题的最好方法是RTFM。

connection.query('INSERT INTO cancellations (name, user_id) values ?', [cancellation], function(err, results) {
    if (err)
       return console.error(err);

    // See https://github.com/mysqljs/mysql#getting-the-id-of-an-inserted-row
    var cancellation_id = results.insertId;

    // Generate sql for rules, join them by ; and execute as one query
    // See https://github.com/mysqljs/mysql#multiple-statement-queries 
    connection.query(sqls, function(err) {
        if (err)
             return console.error(err);

        // Send response here
    });
})