1

This question answers 99% of what I am looking for... How do I do a bulk insert in mySQL using node.js

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

If I want to pass an expression, such as NOW(), how would I do that? If I pass it in the array, it would count as a string. Since VALUES is a ? that gets populated by the array, I can't easily inject an expression. Any ideas?

Community
  • 1
  • 1
Dave Stein
  • 8,653
  • 13
  • 56
  • 104

1 Answers1

2

Basically it seems impossible, so you should make a query string you want.

I would try this as follows.

var sql = "INSERT INTO Test (name, email, n, modified_on) VALUES ?";
var values = [
    ['demian', 'demian@gmail.com', 1, '::NOW()'],
    ['john', 'john@gmail.com', 2, '::UNIX_TIMESTAMP()'],
    ['mark', 'mark@gmail.com', 3, '::DATE()'],
    ['pete', 'pete@gmail.com', 4, '::NOW()']
];

var formattedQuery = connection.format(sql, [values]).replace(/'::(.*?)'/g, '$1');

connection.query(formattedQuery, function(err) {
});

fomattedQuery is as follows.

INSERT INTO Test (name, email, n, modified_on) VALUES ('demian', 'demian@gmail.com', 1, NOW()), ('john', 'john@gmail.com', 2, UNIX_TIMESTAMP()), ('mark', 'mark@gmail.com', 3, DATE()), ('pete', 'pete@gmail.com', 4, NOW())

I hope this helps.

ifsnow
  • 42
  • 2