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的值。
谢谢~~~~~~
所有回答(4)
0
貌似有点困难啊……
你事务以后在查询新增加的ID呢？
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++。
其实呢。我主要是想取得 那个插入后的 ID。然后要保证数据
对照oracle 这样的数据库来说，或者就数据库原理来说，
既然开启了事务，那么你要保证的就是其原子性，要么插入成功，要么插入不成功~
这些插入操作应该都是写在非数据库实际存储的某个地方，所以就算是插入了，并没有返回插入后的ID的。就算是这个时候查询数据，数据库中也不会有刚插入的那条记录的。
你后面说的应该是插入语句生成方面的。 这个我现在是忽略不记的。
主要是取ID
@天生俪姿: 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);
@Launcher: 这样操作能也能取得 自增的ID？
@天生俪姿: 你可以在建表时自己创建一个自增id字段，然后你可以在 select 时选出来。但是，sqlite3 会自动给你添加一个 row id 的自增字段，你可以从 sqlite 的官方文档中读到此段信息。
@Launcher:
嗯 你说这一段我之前是看过的，不过那里也特别的提示了一下吧。 RowId 不一定是等于自增ID的
1）当然我现在sqlite3 的自增ID的设置是 INTEGER PRIMARY KEY AUTOINCREMENT
2）也有 我的数据应该不会超过那个RowID的最大值的
嗯 你的意思 就是说 如果我满足我上面说的两点的话 直接取RowID 也就是取得自增ID的值是吧？
@Launcher:
PRAGMA synchronous=OFF
PRAGMA count_changes=OFF
PRAGMA journal_mode=MEMORY
PRAGMA temp_store=MEMORY
您这几个是C++的优化方式吧。。c# 的在哪里我还真不知道 呵呵
@天生俪姿: 1、sqlite3 的 rowid 就是自增的整形数，你可以使用此字段作为自增ID，你也可以自定义一个自增ID，但是并不妨碍 sqlite3 给你的表默认添加一个 rowid 字段；
2、可以通过连接字符串指定，也可以直接通过你的 Sqlite3Command.ExecuteNoQuery 来执行上面的语句，例如：::sqlite3_exec(pSqlite,"PRAGMA synchronous=OFF",0,0,0)，这在 sqlite3 的官方文档上有说明；
2
这里有一个插入性能比较，Fast Bulk Inserts into SQLite
@dudu: 还是很感谢dudu 了
这里面 确实有讲到 用事务写数据库，现在我想我主要的目的是
在频繁的I/O操作中需要取得自增的ID。
比如我一次压入了100条数据需要写入到数据库里，那么我想返回这100记录（对象）在写入后的自增ID。供界面更新。
@天生俪姿: 或者填干脆在程序中生成好ID直接插入，连获取自增ID的操作都避开了
@dudu:
嗯 我也想这么干的，不过这个ID的生成就有说道了。
其实这个ID还关联着的就是一个分页的算法，目前的计算是从这个自增ID来的。
还有呢，其实我是想偷个懒，因为这个活剩下的时间实在是太少了。可以说明天就要结束。
如果要避开ID 那么有一些代码可能都要重新考虑一下，当然这个除了分页那里需要改造一下外，可能影响的除了数据模型Model 这个层中与ID相关的东西需要改一下外，其他的都没有与之相关联
目前，领导给的意思是 开个线程，做一个队列，然后 线程每隔一段时间（目前定的时间是80ms）然后来访问一下当前的队列，如果数量不为零，那么就将队列做出队操作（取出队首，并移出队伍），然后去写入数据库，当然这样就是将原来几十条 一条一条执行变为了 每隔一个时间段再去写入。这样可能会降低IO的频度。具体的效果还没有实现，但是我想也是一个办法。
@天生俪姿:
试试这里的方法：
INSERT INTO CelestialObject(name, distance) VALUES ('Betelgeose', 200), ('Procyon', 500), ... ;
@天生俪姿:
试试下面的示例代码：
SELECT * from SQLITE_SEQUENCE where 大于插入之前表中的最大ID;
0
insert 之后 select max() 在并发的情况下不可靠吧？
