如何在node.js中使用sqlite3执行批量插入？

I need to insert 10 rows to a sqlite3 table, and once the insertions are done, pass the 10 id's of the new rows to a callback function in an array.

我需要在sqlite3表中插入10行,一旦插入完成,将新行的10个id传递给数组中的回调函数。

My problem is that I can't figure out how to make a prepared statement perform multiple inserts at once. I found this post on how to do this with mySQL.

我的问题是我无法弄清楚如何使预准备语句一次执行多个插入。我发现这篇关于如何使用mySQL执行此操作的帖子。

Bulk Inserts in mySQL

mySQL中的批量插入

But this doesn't work with sqlite. My code is below:

但这不适用于sqlite。我的代码如下:

params = [[1,2],[3,4],[5,6],[7,8]]

stmt = db.prepare("INSERT INTO test (num1, num2) VALUES (?)");
stmt.all([params], function(err, res) {
    if(err) {
        console.log(err);
        return;
    } else {
        createdIDs = []
        for (i in result) {
            createdIDs.push(result[i].id);
        }
        nextFunction(createdIDs);
    }
});

Which gives the following error:

这给出了以下错误:

Error: SQLITE_ERROR: 1 values for 2 columns
at Error (native)

The table's schema is like this:

表的架构如下:

db.run('CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, num1 INTEGER NOT NULL, num2 INTEGER NOT NULL)')

Edit: Using Alvaro's solution, I now get this error message:

编辑:使用Alvaro的解决方案,我现在收到此错误消息:

{ [Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: test.num1] errno: 19, code: 'SQLITE_CONSTRAINT' }

1 个解决方案

#1

You have to enumerate the values in the order of their appearance:

您必须按其外观的顺序枚举值:

stmt = db.prepare("INSERT INTO test (num1, num2) VALUES (?1,?2)");

stmt = db.prepare(“INSERT INTO test(num1,num2)VALUES(?1,?2)”);

That's why, only one variable is detected instead of the expected two.

这就是为什么,只检测到一个变量而不是预期的两个变量。

Reference here.

One more way to do it:

还有一种方法:

var mode = process.argv[2], runs = "100";
db.serialize(function() {
    db.run("begin transaction");
    db.run("drop table if exists data");
    db.run("create table data (value integer)");
    var stmt = db.prepare("insert into data values (?)");
    // Three different methods of doing a bulk insert
    for (var i = 0; i < runs; i++) {
            db.run("insert into data values (?)", i);
            process.exit(1);
        }
    }
    db.run("commit");
});

reference: https://gist.github.com/NelsonMinar/2db6986d5b3cda8ad167

github concern: https://gist.github.com/NelsonMinar/2db6986d5b3cda8ad167

github关注:https://gist.github.com/NelsonMinar/2db6986d5b3cda8ad167

#1