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

How to retrieve the last autoincremented ID from a SQLite table?

提问by Dabblernl

I have a table Messages with columns ID (primary key, autoincrement) and Content (text).
I have a table Users with columns username (primary key, text) and Hash.
A message is sent by one Sender (user) to many recipients (user) and a recipient (user) can have many messages.
I created a table Messages_Recipients with two columns: MessageID (referring to the ID column of the Messages table and Recipient (referring to the username column in the Users table). This table represents the many to many relation between recipients and messages.

我有一个表消息，其中包含列 ID（主键、自动增量）和内容（文本）。
我有一个表用户，其中包含用户名（主键、文本）和哈希列。
一条消息由一个发送者（用户）发送给多个接收者（用户），一个接收者（用户）可以有许多消息。
我创建了一个包含两列的 Messages_Recipients 表：MessageID（指的是 Messages 表的 ID 列和 Recipient（指的是 Users 表中的 username 列）。这个表代表了收件人和消息之间的多对多关系。

So, the question I have is this. The ID of a new message will be created after it has been stored in the database. But how can I hold a reference to the MessageRow I just added in order to retrieve this new MessageID?
I can always search the database for the last row added of course, but that could possibly return a different row in a multithreaded environment?

所以，我的问题是这个。新消息的 ID 将在它存储在数据库中后创建。但是我如何保存对刚刚添加的 MessageRow 的引用，以便检索这个新的 MessageID？
当然，我总是可以在数据库中搜索添加的最后一行，但这可能会在多线程环境中返回不同的行吗？

EDIT: As I understand it for SQLite you can use the SELECT last_insert_rowid(). But how do I call this statement from ADO.Net?

编辑：据我了解，对于 SQLite，您可以使用SELECT last_insert_rowid(). 但是我如何从 ADO.Net 调用这个语句？

My Persistence code (messages and messagesRecipients are DataTables):

我的持久性代码（消息和消息接收者是数据表）：

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);
}

回答by polyglot

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).

另一种选择是查看系统表sqlite_sequence。如果您使用自动增量主键创建任何表，您的 sqlite 数据库将自动拥有该表。此表用于 sqlite 跟踪自动增量字段，以便即使在删除某些行或某些插入失败后它也不会重复主键（在此处阅读有关此内容的更多信息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"

回答by MikeW

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

使用 SQL Server，您需要 SELECT SCOPE_IDENTITY() 来获取当前进程的最后一个标识值。

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

使用 SQlite，它看起来像你会做的自动增量

SELECT last_insert_rowid()

immediately after your insert.

插入后立即。

http://www.mail-archive.com/[email protected]/msg09429.html

http://www.mail-archive.com/[email protected]/msg09429.html

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

为了回答您的评论以获得此值，您需要使用 SQL 或 OleDb 代码，例如：

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();
}

回答by Fidel

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.

我SELECT last_insert_rowid()在多线程环境中使用时遇到了问题。如果另一个线程插入另一个具有 autoinc 的表，last_insert_rowid 将从新表中返回 autoinc 值。

Here's where they state that in the doco:

这是他们在 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.

如果一个单独的线程在 sqlite3_last_insert_rowid() 函数运行时在同一个数据库连接上执行新的 INSERT 并因此更改了最后一个插入 rowid，那么 sqlite3_last_insert_rowid() 返回的值是不可预测的，并且可能不等于旧的或新的最后一个插入rowid。

That's from sqlite.org doco

那来自sqlite.org doco

回答by Fidel

Sample code from @polyglot solution

来自@polyglot 解决方案的示例代码

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

回答by CoolMind

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

根据Android Sqlite get last insert row id还有另一个查询：

SELECT rowid from your_table_name order by ROWID DESC limit 1