How do I do a bulk insert in mySQL using node.js

问题:

How would one do a bulk insert into mySQL if using something like https://github.com/felixge/node-mysql

回答1:

Bulk inserts are possible by using nested array, see the github page

Nested arrays are turned into grouped lists (for bulk inserts), e.g. [[\'a\', \'b\'], [\'c\', \'d\']] turns into (\'a\', \'b\'), (\'c\', \'d\')

You just insert a nested array of elements.

An example is given in here

var mysql = require(\'node-mysql\');
var conn = mysql.createConnection({
    ...
});

var sql = \"INSERT INTO Test (name, email, n) VALUES ?\";
var values = [
    [\'demian\', \'[e-mail]\', 1],
    [\'john\', \'[e-mail]\', 2],
    [\'mark\', \'[e-mail]\', 3],
    [\'pete\', \'[e-mail]\', 4]
];
conn.query(sql, [values], function(err) {
    if (err) throw err;
    conn.end();
});

Note: values is an array of arrays wrapped in an array

[ [ [...], [...], [...] ] ]

回答2:

@Ragnar123 answer is correct, but I see a lot of people saying in the comments that it is not working. I had the same problem and it seems like you need to wrap your array in [] like this:

var pars = [
    [99, \"1984-11-20\", 1.1, 2.2, 200], 
    [98, \"1984-11-20\", 1.1, 2.2, 200], 
    [97, \"1984-11-20\", 1.1, 2.2, 200]
];

It needs to be passed like [pars] into the method.

回答3:

All props to Ragnar123 for his answer.

I just wanted to expand it after the question asked by Josh Harington to talk about inserted IDs.

These will be sequential. See this answer : Does a MySQL multi-row insert grab sequential autoincrement IDs?

Hence you can just do this (notice what I did with the result.insertId):

  var statement = \'INSERT INTO ?? (\' + sKeys.join() + \') VALUES ?\';
  var insertStatement = [tableName, values];
  var sql = db.connection.format(statement, insertStatement);
  db.connection.query(sql, function(err, result) {
    if (err) {
      return clb(err);
    }
    var rowIds = [];
    for (var i = result.insertId; i < result.insertId + result.affectedRows; i++) {
      rowIds.push(i);
    }
    for (var i in persistentObjects) {
      var persistentObject = persistentObjects[i];
      persistentObject[persistentObject.idAttributeName()] = rowIds[i];
    }
    clb(null, persistentObjects);
  });

(I pulled the values from an array of objects that I called persistentObjects.)

Hope this helps.

回答4:

I was looking around for an answer on bulk inserting Objects.

The answer by Ragnar123 led me to making this function:

function bulkInsert(connection, table, objectArray, callback) {
  let keys = Object.keys(objectArray[0]);
  let values = objectArray.map( obj => keys.map( key => obj[key]));
  let sql = \'INSERT INTO \' + table + \' (\' + keys.join(\',\') + \') VALUES ?\';
  connection.query(sql, [values], function (error, results, fields) {
    if (error) callback(error);
    callback(null, results);
  });
}

bulkInsert(connection, \'my_table_of_objects\', objectArray, (error, response) => {
  if (error) res.send(error);
  res.json(response);
});

Hope it helps!

回答5:

In case that needed here is how we solved insert of array

request is from postman (You will look at \"guests\" )

 {
  \"author_id\" : 3,
  \"name\" : \"World War II\",
  \"date\" : \"01 09 1939\", 
  \"time\" : \"16 : 22\",
  \"location\" : \"39.9333635/32.8597419\",
  \"guests\" : [2, 3, 1337, 1942, 1453]
}

And how we scripted

var express = require(\'express\');
var utils = require(\'./custom_utils.js\');

module.exports = function(database){
    var router = express.Router();

    router.post(\'/\', function(req, res, next) {
        database.query(\'INSERT INTO activity (author_id, name, date, time, location) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name), date = VALUES(date), time = VALUES(time), location = VALUES(location)\', 
                [req.body.author_id, req.body.name, req.body.date, req.body.time, req.body.location], function(err, results, fields){
            if(err){
                console.log(err);
                res.json({ status: utils.respondMSG.DB_ERROR });
            }
            else {
                var act_id = results.insertId;
                database.query(\'INSERT INTO act_guest (user_id, activity_id, status) VALUES ? ON DUPLICATE KEY UPDATE status = VALUES(status)\', 
                        [Array.from(req.body.guests).map(function(g){ return [g, act_id, 0]; })], function(err, results, fields){
                    if(err){
                        console.log(err);
                        res.json({ status: utils.respondMSG.DB_ERROR });
                    }
                    else {
                        res.json({ 
                            status: utils.respondMSG.SUCCEED,
                            data: {
                                activity_id : act_id
                            }
                        });
                    }
                });
            }
        });
    });
    return router;
};

回答6:

I ran into this today (mysql 2.16.0) and thought I\'d share my solution:

const items = [
    {name: \'alpha\', description: \'describes alpha\', value: 1},
    ...
];

db.query(
    \'INSERT INTO my_table (name, description, value) VALUES ?\',
    [items.map(item => [item.name, item.description, item.value])],
    (error, results) => {...}
);

回答7:

If Ragnar\'s answer doesn\'t work for you. Here is probably why (based on my experience) -

1. I wasn\'t using node-mysql package as shown my Ragnar. I was using mysql package. They\'re different (if you didn\'t notice - just like me). But I\'m not sure if it has anything to do with the ? not working, since it seemed to work for many folks using the mysql package.

2. Try using a variable instead of ?

The following worked for me -

var mysql = require(\'node-mysql\');
var conn = mysql.createConnection({
    ...
});

var sql = \"INSERT INTO Test (name, email, n) VALUES :params\";
var values = [
    [\'demian\', \'[e-mail]\', 1],
    [\'john\', \'[e-mail]\', 2],
    [\'mark\', \'[e-mail]\', 3],
    [\'pete\', \'[e-mail]\', 4]
];
conn.query(sql, { params: values}, function(err) {
    if (err) throw err;
    conn.end();
});

Hope this helps someone.

回答8:

I was having similar problem. It was just inserting one from the list of arrays. It worked after making the below changes.

1. Passed [params] to the query method.
2. Changed the query from insert (a,b) into table1 values (?) ==> insert (a,b) into table1 values ? . ie. Removed the paranthesis around the question mark.

Hope this helps. I am using mysql npm.

回答9:

Few things I want to mention is that I\'m using mysql package for making a connection with my database and what you saw below is working code and written for insert bulk query.

const values = [
  [1, \'DEBUG\', \'Something went wrong. I have to debug this.\'],
  [2, \'INFO\', \'This just information to end user.\'],
  [3, \'WARNING\', \'Warning are really helping users.\'],
  [4, \'SUCCESS\', \'If everything works then your request is successful\']
];

const query = \"INSERT INTO logs(id, type, desc) VALUES ?\";

const query = connection.query(query, [values], function(err, result) {
  if (err) {
    console.log(\'err\', err)
  }

  console.log(\'result\', result)
});