node.js - node-mysql 插入多条数据

Question

我有100条数据，怎么用node插入到mysql在中啊，

我找了一种这样的但是不好

 var query = 'insert into news set ?';  
 connection.query(query,{   
     title : 'node',   
     content : 'node',   
     nameConpany : 'woman',
     time :'1512151'
}

我的数据结构是这样{{“title ”，“content ”，“nameConpany ”，“time ”}，{“title ”，“content ”，“nameConpany ”，“time ”}，......}求怎么做

Answer 1

Use transaction loop to insert, if an insertion fails, roll back

mysql module, connection.beginTransaction is for doing transactions

Then I encapsulated a function here to perform loop insertion or update operations on the incoming array. If one item fails, roll back, and if everything is correct, commit

Answer 2

Introduced in the document

If your value object

var d = [{
xxx: a,
xxx: b,
xxx: c
},{
xxx: a,
xxx: b,
xxx: c
},{
xxx: a,
xxx: b,
xxx: c
}];

//把对象的转成纯数组
var values = [];
d.forEach(function(n, i){
    var _arr = [];
    for(var m in n){
        _arr.push(n[m]);
    }
    values.push(_arr);
})

It would be simpler if it was just an array

var values = [
[1,2,4],
[5,1,7]
];

var sql = "INSERT INTO xxx(a,b,c) VALUES ?";

mysql.query(sql, [values], function (err, rows, fields) {
    callback(err, rows);
});

When inserting in batches, it is best to insert at the same time. If the query performance is cycled, it will have a great impact.

The following are the test results found online

Innodb engine

InnoDB provides MySQL with transaction-safe (ACID compliant) tables with transaction (commit), rollback (rollback) and crash recovery capabilities (crash recovery capabilities). InnoDB provides row locking (locking on row level) and foreign key constraints (FOREIGN KEY constraints).

InnoDB is designed to handle large-capacity database systems, and its CPU utilization is unmatched by other disk-based relational database engines. Technically, InnoDB is a complete database system placed on the MySQL background. InnoDB establishes its own dedicated buffer pool in main memory for caching data and indexes.

Test environment

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

Total 1 million pieces of data

After insertion, the database size is 38.6MB (without index), 46.8 (with index)

Total time spent on single insert without index: 229s Peak memory: 246KB
Total time spent on single insert with index: 242s Peak memory: 246KB
Total time spent on batch insert without index: 10s Peak memory: 8643KB
Total time spent on batch insert with index : 16s Peak memory: 8643KB
Total time taken for transaction insertion without index: 78s Peak memory: 246KB
Total time taken for transaction insertion with index: 82s Peak memory: 246KB
Total time spent inserting Load Data without index: 12s Peak memory: 246KB
Total time taken to insert Load Data with index: 11s Peak memory: 246KB
MyIASM engine

MyISAM is the default storage engine of MySQL. The design is simple and supports full-text search.

Test environment

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

Total 1 million pieces of data

After insertion, the database size is 19.1MB (without index), 38.6 (with index)

Total time spent on single insert without index: 82s Peak memory: 246KB
Total time spent on single insert with index: 86s Peak memory: 246KB
Total time spent on batch insert without index: 3s Peak memory: 8643KB
Total time spent on batch insert with index : 7s Peak memory: 8643KB
Total time taken for Load Data insertion without index: 6s Peak memory: 246KB
Total time taken for Load Data insertion with index: 8s Peak memory: 246KB