首页 新闻 赞助 找找看

sqlite 插入多条数据 并返回添加行的自增ID

0
悬赏园豆:10 [待解决问题]

场景:

现在使用WPF 绑定数据源(List类似类型的链表类),

这个数据源有可能在一个时间内写入多条记录到Sqlite数据库中,也有可能在很短的时间内写入一条数据到数据(PS:时间可能很快)。

这个程序主要是用于设备通讯的。主要是采集设备上报的一些数据,通过数据在后台进行分析解析,然后将解析后的结果存到数据库中。

由于涉及到画面的数据绑定,并不能每添加一次数据库就进行一次数据库的查询,所以在写入到数据库前是可以做出那个写入对象,但是这个写入对象的数据库主键ID(自增字段)是无法得知的。数据库在本地存放的,并不是网络型的数据库。

数据库:sqlite3 开发工具:vs2012

数据库DLL 使用的是Sqlite官方的 System.Data.Sqlite.dll   (32位版,具体版本号记不清了) 使用技术 WPF 

手写部分的代码吧,有可能不对

添加的对象:

InsertEventData = new EventData();

InsertEventData.Data = XXXXX;InsertEventData.time = XXXXX;

数据库插入语句:

SqliteDataCommand command= new SqliteDataCommand();

command.CommandText = "insert into table1 (data,time,.....) values ('xxxxx','xxxx',....)";

command.ExecuteQueueCount();

大体是这样吧。

百度,谷歌结果 sqlite 貌似一次只能插入一条数据,

1.一次要插入几十条记录的话,sqlite如果不使用事务的话,插入的IO操作很影响性能的。并且插入时间也很长。但是这样是可以一条一条的取得插入后的记录。

  取得的方法 

  1)插入后,再执行一个查询语句 select max(id) from table1 

     2)插入后,使用  sqlite3_last_insert_rowid 这条语句来返回,PS:个人感觉这个不是很靠谱),并且是否存在这个语句还没有调查。

2.如果一次插入了几十条记录,如果开启事务。那么我想无论如何插入数据时是取不到插入后的自增ID的,因为我还没有执行commit 所以。说这样 虽然可能是减少了I/O开销,但是好像就无法返回自增ID了。 

问题:1.如何做插入的速度能在最优的性能上,耗时最少,开销最少。

         2.由于页面模型绑定需要知道这个插入后的ID,那么如何在条件1满足的情况下还可以取得我一次插入了多条记录后,这些记录在写入到数据库里的自增ID的值。

 

谢谢~~~~~~  

二十二号同学的主页 二十二号同学 | 小虾三级 | 园豆:790
提问于:2013-08-15 15:36
< >
分享
所有回答(4)
0

貌似有点困难啊……

你事务以后在查询新增加的ID呢?

幕三少 | 园豆:1384 (小虾三级) | 2013-08-15 15:43
1

2.如果一次插入了几十条记录,如果开启事务。那么我想无论如何插入数据时是取不到插入后的自增ID的,因为我还没有执行commit 所以。说这样 虽然可能是减少了I/O开销,但是好像就无法返回自增ID了。 

这个你做过测试吗?

.net 的封装 dll 没看过,不过按道理,应该也会先编译插入语句,所以,你应该使用参数化的方式,如下:

"insert into table1 (data,time,.....) values (?1,?2,....)";

然后绑定参数 SqliteParameters,然后循环给每个 Sqliteparameter 赋值,然后执行 ExecuteNoQuery.

要速度快的话,出来启动事务外,还可以在启动事务前开启几个选项,通过 ExecuteNoQuery 执行:

PRAGMA synchronous=OFF

PRAGMA count_changes=OFF

PRAGMA journal_mode=MEMORY

PRAGMA temp_store=MEMORY

要再快的话,直接用 Native API,我测试了下,插入约 30000 条数据,300毫秒左右,我用的 C++。

 

 

Launcher | 园豆:45045 (高人七级) | 2013-08-15 15:47

其实呢。我主要是想取得 那个插入后的 ID。然后要保证数据

对照oracle 这样的数据库来说,或者就数据库原理来说,

既然开启了事务,那么你要保证的就是其原子性,要么插入成功,要么插入不成功~

这些插入操作应该都是写在非数据库实际存储的某个地方,所以就算是插入了,并没有返回插入后的ID的。就算是这个时候查询数据,数据库中也不会有刚插入的那条记录的。 

你后面说的应该是插入语句生成方面的。 这个我现在是忽略不记的。 

主要是取ID

支持(0) 反对(0) 二十二号同学 | 园豆:790 (小虾三级) | 2013-08-15 15:57

@天生俪姿: sqlite3 能保证事务原子性。oracle ,sqlserver 在事务开启时,插入成功就会把记录写入数据库,只是标记为 uncommited 状态,如果事务隔离级别是 read uncommited,那么查询就能得到刚插入但还未commited 的记录。

    sqlite3 * pSqlite = NULL;
    ::sqlite3_open16(L"E:\\SVN\\m7framework\\m7db\\m7db2.s3db",&pSqlite);    

    char* err;
    int resultCode = 0;

    resultCode = ::sqlite3_exec(pSqlite,"BEGIN TRANSACTION",0,0,&err);
    resultCode = ::sqlite3_exec(pSqlite,"CREATE TABLE Test (UserName,Password)",0,0,&err);
    resultCode = ::sqlite3_exec(pSqlite,"INSERT INTO Test (UserName,Password) VALUES('name1','123456')",0,0,&err);

    sqlite3_stmt * pStmt = NULL;
    resultCode = ::sqlite3_prepare_v2(pSqlite,"SELECT rowid,UserName,Password FROM Test WHERE UserName = 'name1'",-1,&pStmt,NULL);
    resultCode = ::sqlite3_step(pStmt);

    int rowId = ::sqlite3_column_int(pStmt,0);   // 这个是 row id ,sqlite3 自动生成。
    const char * name = (const char*)::sqlite3_column_text(pStmt,1);   // 这里能得到 uncommited 的 UserName = 'name1' 的记录。

    resultCode = ::sqlite3_finalize(pStmt);
    resultCode = ::sqlite3_exec(pSqlite,"COMMIT TRANSACTION",0,0,&err);
    ::sqlite3_close_v2(pSqlite);
支持(0) 反对(0) Launcher | 园豆:45045 (高人七级) | 2013-08-15 16:17

@Launcher: 这样操作能也能取得 自增的ID?  

支持(0) 反对(0) 二十二号同学 | 园豆:790 (小虾三级) | 2013-08-15 17:55

@天生俪姿: 你可以在建表时自己创建一个自增id字段,然后你可以在 select 时选出来。但是,sqlite3 会自动给你添加一个 row id 的自增字段,你可以从 sqlite 的官方文档中读到此段信息。

支持(0) 反对(0) Launcher | 园豆:45045 (高人七级) | 2013-08-15 17:59

@Launcher: 

嗯  你说这一段我之前是看过的,不过那里也特别的提示了一下吧。 RowId 不一定是等于自增ID的

1)当然我现在sqlite3 的自增ID的设置是  INTEGER PRIMARY KEY AUTOINCREMENT 

2)也有 我的数据应该不会超过那个RowID的最大值的 

嗯 你的意思 就是说 如果我满足我上面说的两点的话 直接取RowID 也就是取得自增ID的值是吧? 

支持(0) 反对(0) 二十二号同学 | 园豆:790 (小虾三级) | 2013-08-15 18:10

@Launcher: 

PRAGMA synchronous=OFF

PRAGMA count_changes=OFF

PRAGMA journal_mode=MEMORY

PRAGMA temp_store=MEMORY

您这几个是C++的优化方式吧。。c# 的在哪里我还真不知道  呵呵 

支持(0) 反对(0) 二十二号同学 | 园豆:790 (小虾三级) | 2013-08-15 18:28

@天生俪姿: 1、sqlite3 的 rowid 就是自增的整形数,你可以使用此字段作为自增ID,你也可以自定义一个自增ID,但是并不妨碍 sqlite3 给你的表默认添加一个 rowid 字段;

2、可以通过连接字符串指定,也可以直接通过你的 Sqlite3Command.ExecuteNoQuery 来执行上面的语句,例如:::sqlite3_exec(pSqlite,"PRAGMA synchronous=OFF",0,0,0),这在 sqlite3 的官方文档上有说明;

支持(0) 反对(0) Launcher | 园豆:45045 (高人七级) | 2013-08-16 09:12
2

这里有一个插入性能比较,Fast Bulk Inserts into SQLite

dudu | 园豆:31075 (高人七级) | 2013-08-15 16:07
支持(0) 反对(0) dudu | 园豆:31075 (高人七级) | 2013-08-15 16:15

@dudu: 还是很感谢dudu 了 

这里面 确实有讲到 用事务写数据库,现在我想我主要的目的是

在频繁的I/O操作中需要取得自增的ID。

比如我一次压入了100条数据需要写入到数据库里,那么我想返回这100记录(对象)在写入后的自增ID。供界面更新。

支持(0) 反对(0) 二十二号同学 | 园豆:790 (小虾三级) | 2013-08-15 18:17

@天生俪姿: 或者填干脆在程序中生成好ID直接插入,连获取自增ID的操作都避开了

支持(0) 反对(0) dudu | 园豆:31075 (高人七级) | 2013-08-15 18:20

@dudu: 

嗯 我也想这么干的,不过这个ID的生成就有说道了。

其实这个ID还关联着的就是一个分页的算法,目前的计算是从这个自增ID来的。 

还有呢,其实我是想偷个懒,因为这个活剩下的时间实在是太少了。可以说明天就要结束。

如果要避开ID 那么有一些代码可能都要重新考虑一下,当然这个除了分页那里需要改造一下外,可能影响的除了数据模型Model 这个层中与ID相关的东西需要改一下外,其他的都没有与之相关联

目前,领导给的意思是 开个线程,做一个队列,然后 线程每隔一段时间(目前定的时间是80ms)然后来访问一下当前的队列,如果数量不为零,那么就将队列做出队操作(取出队首,并移出队伍),然后去写入数据库,当然这样就是将原来几十条 一条一条执行变为了 每隔一个时间段再去写入。这样可能会降低IO的频度。具体的效果还没有实现,但是我想也是一个办法。 

支持(0) 反对(0) 二十二号同学 | 园豆:790 (小虾三级) | 2013-08-15 18:26

@天生俪姿: 

试试这里的方法:

INSERT INTO CelestialObject(name, distance)
VALUES ('Betelgeose', 200),
       ('Procyon', 500),
       ... ;
支持(0) 反对(0) dudu | 园豆:31075 (高人七级) | 2013-08-15 18:37

@天生俪姿: 

试试下面的示例代码:

SELECT * from SQLITE_SEQUENCE where 大于插入之前表中的最大ID;

来源:how to use “select last_insert_rowid();”

支持(0) 反对(0) dudu | 园豆:31075 (高人七级) | 2013-08-15 18:41
0

insert 之后 select max() 在并发的情况下不可靠吧?

0xCAFEBABE | 园豆:402 (菜鸟二级) | 2014-02-22 15:51
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册