如何从 SQLite 表中检索最后一个自动递增的 ID？

我有一个包含列 ID (主键，自动增量)和 Content (文本)的表 Messages。
我有一个包含列用户名(主键、文本)和 Hash 的用户表。
一个消息由一个发件人(用户)发送给多个收件人(用户) ，而一个收件人(用户)可以有多个消息。
我创建了一个 Messages _ Recipients 表，其中有两列: MessageID (引用 Messages 表的 ID 列和 Recipient (引用 Users 表中的 username 列)。此表表示收件人和邮件之间的多对多关系。

我的问题是。新邮件的 ID 将在其存储在数据库中之后创建。但是如何保存刚刚添加的 MessageRow 的引用以检索这个新的 MessageID 呢？当然，< br/> 我总是可以在数据库中搜索添加的最后一行，但是这可能会在多线程环境中返回不同的行吗？译注:

编辑: 据我所知，对于 SQLite，您可以使用 SELECT last_insert_rowid()。但是如何从 ADO.Net 调用这个语句呢？

我的持久性代码(消息和 message 收件人是 DataTable) :

public void Persist(Message message)
{
pm_databaseDataSet.MessagesRow messagerow;
messagerow=messages.AddMessagesRow(message.Sender,
message.TimeSent.ToFileTime(),
message.Content,
message.TimeCreated.ToFileTime());
UpdateMessages();
var x = messagerow;//I hoped the messagerow would hold a
//reference to the new row in the Messages table, but it does not.
foreach (var recipient in message.Recipients)
{
var row = messagesRecipients.NewMessages_RecipientsRow();
row.Recipient = recipient;
//row.MessageID= How do I find this??
messagesRecipients.AddMessages_RecipientsRow(row);
UpdateMessagesRecipients();//method not shown
}


}


private void UpdateMessages()
{
messagesAdapter.Update(messages);
messagesAdapter.Fill(messages);
}
141016 次浏览
小开
最佳答案

With SQL Server you'd SELECT SCOPE_IDENTITY() to get the last identity value for the current process.

With SQlite, it looks like for an autoincrement you would do 

SELECT last_insert_rowid()

immediately after your insert.

http://www.mail-archive.com/sqlite-users@sqlite.org/msg09429.html

In answer to your comment to get this value you would want to use SQL or OleDb code like:

using (SqlConnection conn = new SqlConnection(connString))
{
string sql = "SELECT last_insert_rowid()";
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
int lastID = (Int32) cmd.ExecuteScalar();
}
小开

I've had issues with using SELECT last_insert_rowid() in a multithreaded environment. If another thread inserts into another table that has an autoinc, last_insert_rowid will return the autoinc value from the new table.

Here's where they state that in the doco:

If a separate thread performs a new INSERT on the same database connection while the sqlite3_last_insert_rowid() function is running and thus changes the last insert rowid, then the value returned by sqlite3_last_insert_rowid() is unpredictable and might not equal either the old or the new last insert rowid.

That's from sqlite.org doco

小开

One other option is to look at the system table sqlite_sequence. Your sqlite database will have that table automatically if you created any table with autoincrement primary key. This table is for sqlite to keep track of the autoincrement field so that it won't repeat the primary key even after you delete some rows or after some insert failed (read more about this here http://www.sqlite.org/autoinc.html).

So with this table there is the added benefit that you can find out your newly inserted item's primary key even after you inserted something else (in other tables, of course!). After making sure that your insert is successful (otherwise you will get a false number), you simply need to do:

select seq from sqlite_sequence where name="table_name"
小开

Sample code from @polyglot solution

SQLiteCommand sql_cmd;
sql_cmd.CommandText = "select seq from sqlite_sequence where name='myTable'; ";
int newId = Convert.ToInt32( sql_cmd.ExecuteScalar( ) );
小开

According to Android Sqlite get last insert row id there is another query:

SELECT rowid from your_table_name order by ROWID DESC limit 1
小开

sqlite3_last_insert_rowid() is unsafe in a multithreaded environment (and documented as such on SQLite) However the good news is that you can play with the chance, see below

ID reservation is NOT implemented in SQLite, you can also avoid PK using your own UNIQUE Primary Key if you know something always variant in your data.

Note: See if the clause on RETURNING won't solve your issue https://www.sqlite.org/lang_returning.html As this is only available in recent version of SQLite and may have some overhead, consider Using the fact that it's really bad luck if you have an insertion in-between your requests to SQLite

see also if you absolutely need to fetch SQlite internal PK, can you design your own predict-able PK: https://sqlite.org/withoutrowid.html

If need traditional PK AUTOINCREMENT, yes there is a small risk that the id you fetch may belong to another insertion. Small but unacceptable risk.

A workaround is to call twice the sqlite3_last_insert_rowid()

#1 BEFORE my Insert, then #2 AFTER my insert

as in :

int IdLast = sqlite3_last_insert_rowid(m_db);   // Before (this id is already used)


const int rc = sqlite3_exec(m_db, sql,NULL, NULL, &m_zErrMsg);


int IdEnd = sqlite3_last_insert_rowid(m_db);    // After Insertion most probably the right one,

In the vast majority of cases IdEnd==IdLast+1. This the "happy path" and you can rely on IdEnd as being the ID you look for.

Else you have to need to do an extra SELECT where you can use criteria based on IdLast to IdEnd (any additional criteria in WHERE clause are good to add if any)

Use ROWID (which is an SQlite keyword) to SELECT the id range that is relevant.

"SELECT my_pk_id FROM Symbols WHERE ROWID>%d && ROWID<=%d;",IdLast,IdEnd);

// notice the > in: ROWID>%zd, as we already know that IdLast is NOT the one we look for.

As second call to sqlite3_last_insert_rowid is done right away after INSERT, this SELECT generally only return 2 or 3 row max. Then search in result from SELECT for the data you Inserted to find the proper id.

Performance improvement: As the call to sqlite3_last_insert_rowid() is way faster than the INSERT, (Even if mutex may make that wrong it is statistically true) I bet on IdEnd to be the right one and unwind the SELECT results by the end. Nearly in every cases we tested the last ROW does contain the ID you look for).

Performance improvement: If you have an additional UNIQUE Key, then add it to the WHERE to get only one row.

I experimented using 3 threads doing heavy Insertions, it worked as expected, the preparation + DB handling take the vast majority of CPU cycles, then results is that the Odd of mixup ID is in the range of 1/1000 insertions (situation where IdEnd>IdLast+1)

So the penalty of an additional SELECT to resolve this is rather low.

Otherwise said the benefit to use the sqlite3_last_insert_rowid() is great in the vast majority of Insertion, and if using some care, can even safely be used in MT.

Caveat: Situation is slightly more awkward in transactional mode.

Also SQLite didn't explicitly guaranty that ID will be contiguous and growing (unless AUTOINCREMENT). (At least I didn't found information about that, but looking at the SQLite source code it preclude that)

小开

the simplest method would be using :

SELECT MAX(id) FROM yourTableName LIMIT 1;

if you are trying to grab this last id in a relation to effect another table as for example :
( if invoice is added THEN add the ItemsList to the invoice ID )
in this case use something like :

var cmd_result = cmd.ExecuteNonQuery(); // return the number of effected rows

then use cmd_result to determine if the previous Query have been excuted successfully,
something like :
if(cmd_result > 0) followed by your Query SELECT MAX(id) FROM yourTableName LIMIT 1;
just to make sure that you are not targeting the wrong row id in case the previous command did not add any Rows.

in fact cmd_result > 0 condition is very necessary thing in case anything fail . specially if you are developing a serious Application, you don't want your users waking up finding random items added to their invoice.


提交答案