How to insert batch data to the MySQL database under Node. js, node. jsmysql Database

How to insert batch data to the MySQL database under Node. js, node. jsmysql Database

In the project (nodejs), you need to insert multiple pieces of data to the database at a time. The database is mysql. because of the poor performance of loop insertion, it is like using batch insertion to improve data insertion performance.

The table structure of the batch inserted database is as follows:

1. Database Connection

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

Convert inserted data into nested Arrays

For example, two pieces of data to be inserted:

Record 1:

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

Record 2:

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

Convert to the following format:

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

Write insert statement

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

Call the query function to insert data.

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

Complete code:

Var mysql = require ('mysql'); // database information var connection = mysql. createConnection ({host: 'localhost', user: 'database username', password: 'database logon password', database: 'Operation database name '}); var values = [["index", "www.alibaba.com",], ["index1", "www.google.com",]; 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, a transaction-based operation is recorded here (there is no practice yet, and the specific effect is unknown)

Use transaction loop insert. If one insert fails, perform rollback.

Mysql module and connection. beginTransaction are transactions.

Then I encapsulate a function here, and perform operations such as loop insert or update on the input array. If one fails, it will be rolled back. If it is all right, it will commit.

Summary

The above is a small part of the Node. insert batch data to the MySQL database under js. I hope it will be helpful to you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!