使用Node JS将记录数组插入mysql [英] Insert array of records into mysql with Node JS

问题描述

我有一系列数据，例如

var records = [
               {Name: '', Id: 1},
               {Name: '', Id: 2},
               {Name: '', Id: 3},
               {Name: '', Id: 4},
               {Name: '', Id: 5},
               {Name: '', Id: 6}
              ];

记录数组中可能有成千上万的项目...

there could be thousands of items inside records array...

问题1：我们可以创建一个存储过程来接受mysql中的对象数组吗？

Ques1: Can we create a stored procedure which will accept an array of objects in mysql?

问题2：是否可以批量插入此数据

Ques2: Is there a way to bulk insert this data into mysql with Node JS?

推荐答案

您可以批量插入记录数组，但在此之前可能需要将其转换为数组

You can bulk insert the array of records ,but before that you might need to convert it into array of arrays

我使用array reduce来获得类似这样的数组

I use array reduce to get an array something like this

let j=[
               {Name: '', Id: 1},
               {Name: '', Id: 2},
               {Name: '', Id: 3},
               {Name: '', Id: 4},
               {Name: '', Id: 5},
               {Name: '', Id: 6}
              ];

              let values=j.reduce((o,a)=>{
                    let ini=[];
                    ini.push(a.Name);
                    ini.push(a.Id);
                    o.push(ini);
                    return o
              },[])
              console.log(values);

这将输出

[["",1],["",2],["",3],["",4],["",5],["",6]]

现在插入mysql数据库

Now inserting into the mysql database

1-使用常规回调

1-Using normal callback

const con=require('./mysql.js'); //mysql connectionin mysql.js 

var sql = "INSERT INTO customers (name, id) VALUES ?";
con.query(sql, [values], function (err, result) { //pass values array (from above)  directly here
    if (err) throw err;
    console.log("Number of records inserted: " + result.affectedRows);
  });
});

因此，多个数据插入的格式应类似于 [[[a， b]，[b，c]，[d，k]]]

so the format of multiple data insert should be like [[[a,b],[b,c],[d,k]]]

2-使用承诺

2-Using promises

var Promise = require("bluebird");//for promises
const promisecon=Promise.promisifyAll(require('./mysql.js'));//
  var sql = "INSERT INTO customers (name, id) VALUES ?";
    promisecon.queryAsync(sql,[values]).then((result)=>{//bluebird identifies with Async
      console.log(result);
    }).catch(function(err){
        console.log(err);
      })

3-使用异步等待

3-Using async await

 var sql = "INSERT INTO customers (name, id) VALUES ?";
async function build() {
            try {
              const result =await con.queryAsync(sql,[values]);

                 console.log(result);

            } catch (err) {
              // do something
            }
          }
          build();

这篇关于使用Node JS将记录数组插入mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！