mysql - 使用Node.js写MySQL一对多关系

Question

我已经尝试过几种方法，也许 node.js 是用于此目的的错误语言，但我认为可以用它快速完成。我正在获取一个包含姓名和地址的联系人表，并将其转换为具有一对多关系的两个表，以便一个联系人可以有多个地址。想要的数据结构是这样的:

联系表联系方式名姓电话电子邮件等等……

地址表地址编号联系方式街道城市状态等等……

目前所有字段都在同一个表中。

我得到的结果是第二个查询中的数据永远不会改变。因此，如果我先编写联系人查询，那么它会按预期工作，但地址最终都会成为一个联系人地址的副本。如果我先插入地址记录，那么我会得到一个联系人的重复记录。我首先尝试嵌套查询，但当我遇到这种情况时，我将它们分开了。我的想法是我可以编写查询。我需要写联系人并取回ID，然后用contactID 写地址。或者我需要写地址并取回该 ID，然后写联系人，获取该 ID，然后执行更新查询以将 contactId 放入地址记录。最后一种方法是下面代码中显示的尝试。

这是我目前的代码:

var sql = "SELECT * FROM pfPeople";
connection.query(sql, function(err, result, fields)  {
    if (err) throw err;

    for (var i = 0; i < result.length; i++) {
        var names = result[i].fullName.replace(/"/g, "'").split(" ");

        var sql2 = "INSERT INTO contact (firstName, lastName, title, phone1, phone2, fax, email1, notes, org) VALUES (" + 
        "\"" + names[0] + "\", \"" + names[1] + "\", \"" + result[i].title.replace(/"/g, "'") + " \", \"" + result[i].phone1 + "\", \"" + 
        result[i].phone2 + "\", " + "\"" + result[i].phone3 + "\", \"" + result[i].email + "\", \"" + result[i].notes.replace(/"/g, "'") + "\", \"" + 
        result[i].org.replace(/"/g, "'") + "\")";

        var street = result[i].street;
        var city = result[i].city;
        var state = result[i].state;
        var zip = result[i].zip;
        var country = result[i].country;

        var sql3 = "INSERT INTO address (address1, city, state, zip, country) VALUES (" + 
        "\"" + street + "\", \" " + city + "\", \"" + state + "\", \"" + zip + "\", \"" + country + "\")";

        var contactId;
        var addressId;

        connection.query(sql2, function(err, cResult) {
            if (err) throw err;

            var contactId = cResult.insertId;

            console.log("Inserted ID: " + contactId);
        });

        connection.query(sql3, function(err, aResult) {
            if (err) throw err;

            var addressId = aResult.insertId;
        });

        var sql4 = "UPDATE address set contactId = " + contactId + " WHERE (addressId = " + addressId + ")";

        connection.query(sql4, function(err, uRes) {
            console.log("Address updated " + addressId + " " + contactId);
        });

        if (i > 3) break;
    }
});

Answer 1

好吧，我添加了一些 promise 以使其更容易。问题是您没有等待响应完成。试试这段代码

var sql = "SELECT * FROM pfPeople";
connection.query(sql, function(err, result, fields)  {
    if (err) throw err;

    for (var i = 0; i < result.length; i++) {
        var names = result[i].fullName.replace(/"/g, "'").split(" ");

        var sql2 = "INSERT INTO contact (firstName, lastName, title, phone1, phone2, fax, email1, notes, org) VALUES (" + 
        "\"" + names[0] + "\", \"" + names[1] + "\", \"" + result[i].title.replace(/"/g, "'") + " \", \"" + result[i].phone1 + "\", \"" + 
        result[i].phone2 + "\", " + "\"" + result[i].phone3 + "\", \"" + result[i].email + "\", \"" + result[i].notes.replace(/"/g, "'") + "\", \"" + 
        result[i].org.replace(/"/g, "'") + "\")";

        var street = result[i].street;
        var city = result[i].city;
        var state = result[i].state;
        var zip = result[i].zip;
        var country = result[i].country;

        var sql3 = "INSERT INTO address (address1, city, state, zip, country) VALUES (" + 
        "\"" + street + "\", \" " + city + "\", \"" + state + "\", \"" + zip + "\", \"" + country + "\")";

        const contacPromise = new Promise((resolve,reject) =>{
           connection.query(sql2, function(err, cResult) {
              if (err) return reject( err);
              resolve( cResult.insertId);
          });
        })
       
        const addressPromise = new Promise((resolve,reject) =>{
            connection.query(sql3, function(err, aResult) {
                if (err) return reject( err);
                resolve(aResult.insertId);
            });
        })
        Pomise.all([contacPromise,addressPromise])
        .then(([contactId,addressId]) =>{
           var sql4 = "UPDATE address set contactId = " + contactId + " WHERE (addressId = " + addressId + ")";

          connection.query(sql4, function(err, uRes) {
              console.log("Address updated " + addressId + " " + contactId);
          });
        
        })
        .catch(err => throw err)



        if (i > 3) break;
    }
});