Mysql: Question: How to get expressions into bulk inserts?

[DaveStein]

I posted this question on SO: http://stackoverflow.com/questions/43481075/how-do-i-use-expression-with-bulk-insert-using-npm-mysql

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

How would I pass an expression as one of those values? I know in a singular insert I can do INSERT INTO Test SET name=?, modified_on=NOW() for example. But the way the bulk insert converts to a string, not sure if it's even possible. Is this ask just impossible? There a a recommended way to write the query out properly?

[ifsnow]

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', '[email protected]', 1, '::NOW()'],
    ['john', '[email protected]', 2, '::UNIX_TIMESTAMP()'],
    ['mark', '[email protected]', 3, '::DATE()'],
    ['pete', '[email protected]', 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', '[email protected]', 1, NOW()), ('john', '[email protected]', 2, UNIX_TIMESTAMP()), ('mark', '[email protected]', 3, DATE()), ('pete', '[email protected]', 4, NOW())

I hope this helps.