How to insert batch data into MySQL database under Node.js

In the project (nodejs), multiple data need to be inserted into the database at one time. The database is MySQL. Since the performance of loop insertion is too poor, it is like using batch insertion to improve the data insertion performance.

The table structure of the database for batch insertion is as follows:

1. Database connection

var mysql = require('mysql');
// Database information var connection = mysql.createConnection({
 host : 'localhost',
 user: 'Database user name',
 password: 'Database login password',
 database: 'Operation database name'
});

Convert the inserted data into a nested array

For example, two pieces of data to be inserted:

Record 1:

from:"index" 
to: "www.alibaba.com"
status:1
is_new:0

Record 2:

from:"index1"
to:"www.google.com"
status:1
is_new:0

Convert to the following format:

var values ​​= [
 ["index","www.alibaba.com",1,0],
 ["index1","www.google.com",1,0]
];

Write an insert statement

var sql = "INSERT INTO url(`from`,`to`,`status`, `is_new`) VALUES ?";

Call the query function to complete the data insertion

connection.query(sql, [values], function (err, rows, fields) {
 if(err){
    console.log('INSERT ERROR - ', err.message);
    return;
   }
   console.log("INSERT SUCCESS");
});

Full code:

var mysql = require('mysql');
// Database information var connection = mysql.createConnection({
 host : 'localhost',
 user: 'Database user name',
 password: 'Database login password',
 database: 'Operation database name'
});
var values ​​= [
 ["index","www.alibaba.com",1,0],
 ["index1","www.google.com",1,0]
];
var sql = "INSERT INTO url(`from`,`to`,`status`, `is_new`) VALUES ?";
connection.query(sql, [values], function (err, rows, fields) {
 if(err){
    console.log('INSERT ERROR - ', err.message);
    return;
   }
   console.log("INSERT SUCCESS");
});

At the same time, record a transaction-based operation here (no practice yet, the specific effect is unknown)

Use transaction loop to insert and roll back if one insert fails

mysql module, connection.beginTransaction is to do transactions

Then I encapsulate a function here to perform operations such as loop insertion or update on the passed array. If one fails, roll back, and commit if all are correct.

Summarize

The above is what I introduced to you about inserting batch data into MySQL database under Node.js. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!