SQLite3没有按顺序插入多行 [英] Sqlite3 is not inserting multiple rows in order

问题描述

我正在使用Node js应用程序，数据库是sqlite3(2.15.8).我正在使用 sqlite3模块.在这种情况下，我必须按顺序插入行. 我有表tests，其中的行应按循环插入的相同顺序插入.

I am working with node js application and database is sqlite3 (2.15.8). I am using sqlite3 module . In this I have to insert rows in order. I have table tests in which rows should be inserted as the same order in which they coming in loop.

我的代码如下:

要创建表:

GLOBAL.db1 = new sqlite3.Database(APPDATA+'/xyz/mydb.db');
var check;
db1.serialize(function() {

db1.run("CREATE TABLE IF NOT EXISTS tests (id INTEGER NOT NULL PRIMARY KEY , parent_name varchar(255), test_name varchar(255), test_alias varchar(255), sequences varchar(55), duration varchar(55), prog_id int(11), org_id int(11), test_createdby int(11), test_created varchar(20), test_modified varchar(11), test_status int(11))");
});

要插入表格

try{
                
  db1.run("INSERT INTO programs (`prog_name`, `prog_exercises`, `prog_orgid`, `prog_createdby`, `prog_created`, `prog_modified`, `prog_status`)  VALUES (?,?,?,?,?,?,?)",program_name, JSON.stringify(step2PostedData), org_id, createdby, timestamp, timestamp, 0,function(err){
 // as it is single record insertion so inserting good obviously.
                    
if(err){
 throw err;
}else{
 for (i in step2PostedData.testsInfo) {
 
  var c = 0;
   
   for(j in step2PostedData.testsInfo[i]){
                        
   var obj = Object.keys(step2PostedData.testsInfo[i])[c];
   
   console.log(obj); // here I am getting correct order of test names in console
   
   db1.prepare("INSERT INTO `tests` ( `parent_name`,`test_name`, `test_alias`, `sequences`, `duration`, `prog_id`, `org_id`, `test_createdby`, `test_created`, `test_modified`, `test_status`) VALUES(?,?,?,?,?,?,?,?,?,?,?)").run('', obj, step2PostedData.testsInfo[i][j].alias, step2PostedData.testsInfo[i][j].sequences, step2PostedData.testsInfo[i][j].duration, this.lastID, org_id, createdby, timestamp, timestamp, 0);

     // Insertion is going in loop but order of insertion of rows is not same as per required                       
                            
     c++;
     }
    }
   }
  });
 }catch(ex){
    throw ex;
 }

但是，当我在表中获取结果时，插入到tests表中的行的顺序与我发送到循环中的顺序不同.有人可以建议我什么吗?应该在那里更改?

But, when I am getting result in table then order of rows inserted in tests table are not same as the same order in which I am sending into loop. Can anyone please suggest me what should be changed there?

谢谢！

推荐答案

关系中没有顺序

关系中没有顺序(即人们通常错误地称其为表")，因为关系是集合，而不是列表或数组.

There is no order in a relation

There is no order in a relation (i.e. in what people usually incorrectly call a "table") because relation is a set, not a list or array.

如果人们没有错误地将关系称为表"，那么没有人会期望从他们那里得到命令.不幸的是，它们确实导致了无数的误解，这种误解如此普遍以至于我写了一篇文章:

If people didn't incorrectly call relations "tables" then no one would expect an order from them. Unfortunately they do which leads to countless misconceptions that are so common that I wrote an article:

• 如果我告诉您关系数据库中没有表怎么办?

简而言之-不要期望任何集合的任何顺序(包括关系).如果要订购，请在SQL中使用sort.如果需要特定的订单，请添加一个可以排序的整数值，以获取所需的订单.

In short - don't expect any order from any set (this includes relations). If you want an order, use sort in SQL. If you want a specific order, add an integer value that you can sort to get the order that you need.

(或者不要使用关系数据库-还有其他类型的数据库支持排序的结构，例如数组.关系数据库不支持数组-至少它们不应该支持-并且在任何关系中都没有隐式顺序. )

(Or don't use relational databases - there are other types of databases that support sorted structures like arrays. Relational databases don't support arrays - at least they shouldn't - and there is no implicit order in any relation.)

这篇关于SQLite3没有按顺序插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！