How to Perform Efficient Bulk Inserts into MySQL using Node.js?

Bulk Insertion in MySQL Using Node.js

Performing bulk insertions into MySQL databases using Node.js has become a common task for optimizing database operations. One popular Node.js module for interacting with MySQL is node-mysql.

How to Perform Bulk Inserts Using Node.js and Node-MySQL

To insert multiple rows into a MySQL table using node-mysql, you can use nested arrays. The nested array structure allows you to specify multiple sets of values for insertion.

Consider the following example:

var values = [
    ['demian', '[email protected]', 1],
    ['john', '[email protected]', 2],
    ['mark', '[email protected]', 3],
    ['pete', '[email protected]', 4]
];

In this example, values is an array of arrays. Each subarray represents a set of values to be inserted into the database.

SQL Query

To execute the bulk insert, you will need to create an SQL query that accepts the nested array as a parameter. The query should resemble the following:

INSERT INTO Test (name, email, n) VALUES ?;

The ? placeholder in the query will be replaced by the nested array during the query execution.

Node.js Code

Here's an example of how you can perform the bulk insert using node-mysql:

var mysql = require('mysql');
var conn = mysql.createConnection({
    ...
});

var sql = "INSERT INTO Test (name, email, n) VALUES ?";
conn.query(sql, [values], function(err) {
    if (err) throw err;
    conn.end();
});

Note on Value Structure

It's important to note that the values array must be nested correctly. The outer array represents the collection of rows, while the inner arrays represent the values for each row. The following structure is required:

[ [ [...], [...], [...] ] ]

Alternative Node-MySQL Package for Bulk Insertion

Besides node-mysql, there's another Node.js package designed specifically for bulk insertions into MySQL:

• [node-mysql-plus](https://www.npmjs.com/package/node-mysql-plus)

The above is the detailed content of How to Perform Efficient Bulk Inserts into MySQL using Node.js?. For more information, please follow other related articles on the PHP Chinese website!