如何使用 node.js 在 mySQL 中进行批量插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8899802/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I do a bulk insert in mySQL using node.js
提问by crickeys
How would one do a bulk insert into mySQL if using something like https://github.com/felixge/node-mysql
如果使用诸如https://github.com/felixge/node-mysql 之类的东西，如何将批量插入到 mySQL 中
回答by Ragnar123
Bulk inserts are possible by using nested array, see the github page
可以使用嵌套数组进行批量插入，请参阅github 页面
Nested arrays are turned into grouped lists (for bulk inserts), e.g.
[['a', 'b'], ['c', 'd']]turns into('a', 'b'), ('c', 'd')
嵌套数组变成分组列表（用于批量插入），例如
[['a', 'b'], ['c', 'd']]变成('a', 'b'), ('c', 'd')
You just insert a nested array of elements.
您只需插入一个嵌套的元素数组。
An example is given in here
这里给出了一个例子
var mysql = require('mysql');
var conn = mysql.createConnection({
...
});
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();
});
Note: valuesis an array of arrays wrapped in an array
注：values是数组包裹在数组中的数组
[ [ [...], [...], [...] ] ]
There is also a totally different node-msqlpackage for bulk insertion
还有一个完全不同的node-msql包用于批量插入
回答by Codendaal
@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:
@Ragnar123 答案是正确的，但我看到很多人在评论中说它不起作用。我有同样的问题，似乎你需要[]像这样包装你的数组：
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.
它需要像传递[pars]到方法中一样。
回答by Stephen Gibson
I was looking around for an answer on bulk inserting Objects.
我正在四处寻找有关批量插入对象的答案。
The answer by Ragnar123 led me to making this function:
Ragnar123 的回答让我做出了这个功能：
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!
希望能帮助到你！
回答by SnobOfTheGolfClub
I ran into this today (mysql2.16.0) and thought I'd share my solution:
我今天遇到了这个（mysql2.16.0）并想我会分享我的解决方案：
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) => {...}
);
回答by thewormsterror
All props to Ragnar123 for his answer.
所有道具都支持 Ragnar123 的回答。
I just wanted to expand it after the question asked by Josh Harington to talk about inserted IDs.
我只是想在 Josh Harington 提出的关于插入 ID 的问题之后扩展它。
These will be sequential. See this answer : Does a MySQL multi-row insert grab sequential autoincrement IDs?
这些将是连续的。请参阅此答案：是否 MySQL 多行插入会抓取顺序自动增量 ID？
Hence you can just do this (notice what I did with the result.insertId):
因此你可以这样做（注意我对 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.)
（我从我称为persistentObjects的对象数组中提取值。）
Hope this helps.
希望这可以帮助。
回答by Manuel Spigolon
This is a fast "raw-copy-paste" snipped to push a file column in mysql with node.js >= 11
这是一个快速的“原始复制粘贴”，用于在 mysql 中使用 node.js >= 11 推送文件列
250k row in few seconds
几秒钟内 250k 行
'use strict';
const mysql = require('promise-mysql');
const fs = require('fs');
const readline = require('readline');
async function run() {
const connection = await mysql.createConnection({
host: '1.2.3.4',
port: 3306,
user: 'my-user',
password: 'my-psw',
database: 'my-db',
});
const rl = readline.createInterface({ input: fs.createReadStream('myfile.txt') });
let total = 0;
let buff = [];
for await (const line of rl) {
buff.push([line]);
total++;
if (buff.length % 2000 === 0) {
await connection.query('INSERT INTO Phone (Number) VALUES ?', [buff]);
console.log(total);
buff = [];
}
}
if (buff.length > 0) {
await connection.query('INSERT INTO Phone (Number) VALUES ?', [buff]);
console.log(total);
}
console.log('end');
connection.close();
}
run().catch(console.log);
回答by Sam
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;
};
回答by Aswin Ramakrishnan
If Ragnar's answer doesn't work for you. Here is probably why (based on my experience) -
如果Ragnar的答案对您不起作用。这可能是原因（根据我的经验）-
I wasn't using
node-mysqlpackage as shown myRagnar. I was usingmysqlpackage. 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 themysqlpackage.Try using a variable instead of
?
我没有使用
node-mysql我的Ragnar. 我正在使用mysql包。他们是不同的（如果你没有注意到 - 就像我一样）。但我不确定它是否与?不工作有关，因为它似乎对许多使用该mysql软件包的人都有效。尝试使用变量而不是
?
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', '[email protected]', 1],
['john', '[email protected]', 2],
['mark', '[email protected]', 3],
['pete', '[email protected]', 4]
];
conn.query(sql, { params: values}, function(err) {
if (err) throw err;
conn.end();
});
Hope this helps someone.
希望这可以帮助某人。
回答by Sagar Gavhane
Few things I want to mention is that I'm using mysqlpackage for making a connection with my database and what you saw below is working code and written for insert bulk query.
我想提的几件事是，我正在使用mysql包与我的数据库建立连接，您在下面看到的是工作代码，并为插入批量查询而编写。
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)
});
回答by user1998289
I was having similar problem. It was just inserting one from the list of arrays. It worked after making the below changes.
我遇到了类似的问题。它只是从数组列表中插入一个。在进行以下更改后它起作用了。
- Passed [params] to the query method.
- Changed the query from insert (a,b) into table1 values (?) ==> insert (a,b) into table1 values ? . ie. Removed the paranthesis around the question mark.
- 将 [params] 传递给查询方法。
- 将查询从 insert (a,b) 更改为 table1 values (?) ==> insert (a,b) into table1 values ？. IE。删除了问号周围的括号。
Hope this helps. I am using mysql npm.
希望这可以帮助。我正在使用 mysql npm。
