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

2izufjch  于 2023-01-05  发布在  SQLite
关注(0)|答案(8)|浏览(431)

我有一个Messages表,其中包含列ID(主键、自动增量)和Content(文本)。
我有一个表Users,其中包含列username(主键,文本)和Hash。
一条消息由一个发送方(用户)发送给多个接收方(用户),一个接收方(用户)可以有多条消息。
我创建了一个包含两列的表Messages_Recipients:MessageID(指“消息”表中的ID列)和收件人(指“用户”表中的用户名列)。此表表示收件人与消息之间的多对多关系。
因此,我的问题是,新消息的ID将在它存储到数据库之后创建,但是如何保存对刚刚添加的MessageRow的引用以便检索这个新的MessageID呢?
当然,我总是可以在数据库中搜索最后添加的行,但是在多线程环境中,这可能会返回不同的行。
编辑:据我所知,SQLite可以使用SELECT last_insert_rowid(),但是如何从ADO.Net调用这个语句呢?
我的持久性代码(消息和消息收件人是数据表):

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

evrscar21#

另一个选择是查看系统表sqlite_sequence.如果你创建了任何带有自动增量主键的表,你的sqlite数据库会自动拥有这个表.这个表是为了让sqlite跟踪自动增量字段,这样即使你删除了一些行或者一些插入失败后,它也不会重复主键(在这里阅读更多关于http://www.sqlite.org/autoinc.html的信息).
因此,使用此表还有一个额外的好处,即即使在插入了其他内容(当然是在其他表中!)之后,您也可以找到新插入项的主键。在确保插入成功(否则将得到一个错误的数字)之后,您只需执行以下操作:

select seq from sqlite_sequence where name="table_name"
9jyewag0

9jyewag02#

在SQL Server中,您可以使用SELECT SCOPE_IDENTITY()来获取当前进程的最后一个标识值。
对于SQlite,看起来就像是自动增量

SELECT last_insert_rowid()

在你插入后立即开始。
http://www.mail-archive.com/sqlite-users@sqlite.org/msg09429.html
作为对您的注解的回答,您可能希望使用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();
}
k3bvogb1

k3bvogb13#

我在多线程环境中使用SELECT last_insert_rowid()时遇到过一些问题,如果另一个线程插入到另一个有autoinc的表中,last_insert_rowid将从新表返回autoinc值。
这是他们在文档中声明的地方:
如果在sqlite3_last_insert_rowid()函数运行时,另一个线程在同一数据库连接上执行新的INSERT操作,从而更改了上次插入的rowid,则sqlite3_last_insert_rowid()返回的值是不可预测的,可能不等于旧的或新的上次插入的rowid。
这来自sqlite.org doco

5jdjgkvh

5jdjgkvh4#

根据Android Sqlite get last insert row id,存在另一个查询:

SELECT rowid from your_table_name order by ROWID DESC limit 1
egdjgwm8

egdjgwm85#

来自@polyglot solution的示例代码

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

jfewjypa6#

sqlite3_last_insert_rowid()在多线程环境中是不安全的(在SQLite中也是如此)。不过,好消息是,您可以利用这个机会,如下所示
SQLite中没有实现ID保留,如果您知道数据中的某些变量,您也可以使用自己的UNIQUE主键来避免PK。
注意:查看RETURNING子句是否不能解决您的问题https://www.sqlite.org/lang_returning.html由于这仅在SQLite的最新版本中可用,并且可能会产生一些开销,请考虑使用以下事实:如果您在对SQLite的请求之间进行插入,则会带来很坏的运气
看看你是否真的需要获取SQlite内部PK,你能不能设计你自己的可预测PK:https://sqlite.org/withoutrowid.html
如果需要传统的PK自动增量,是的,有一个小的风险,你获取的id可能属于另一个插入。小,但不可接受的风险。
解决方法是调用两次sqlite3_last_insert_rowid()

1插入前,然后#2插入后

如:

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,

在绝大多数情况下,IdEnd == IdLast +1。这是一条"快乐之路",您可以依赖IdEnd作为您要查找的ID。
否则,您必须执行额外的SELECT,其中您可以使用基于IdLast到IdEnd的条件(WHERE子句中的任何附加条件都可以添加,如果有的话)
使用ROWID(这是一个SQlite关键字)来选择相关的id范围。

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

//注意〉位于:ROWID〉% zd,因为我们已经知道IdLast不是我们要查找的对象。
由于对sqlite3_last_insert_rowid的第二次调用是在INSERT之后立即执行的,因此此SELECT通常最多只返回2或3行。然后在SELECT的结果中搜索您插入的数据,以找到正确的ID。
业绩改进:由于对sqlite3_last_insert_rowid()的调用比INSERT快得多,(即使互斥锁可能会出错,但统计上是正确的)我打赌IdEnd是正确的,并在最后展开SELECT结果。几乎在我们测试的每种情况下,最后一行都包含您要查找的ID)。
业绩改进:如果您有一个额外的UNIQUE键,那么将它添加到WHERE中,这样就只得到一行。
我尝试使用3个线程执行大量插入操作,结果与预期一致,准备+DB处理占用了绝大多数CPU周期,结果是混合ID的奇数在1/1000插入范围内(IdEnd〉IdLast +1的情况)
因此,解决此问题的额外SELECT的代价相当低。
否则,在绝大多数插入操作中使用sqlite3_last_insert_rowid()的好处是巨大的,如果小心使用,甚至可以安全地用于MT。
警告:在事务模式下,情况稍微有些尴尬。
SQLite也没有明确地保证ID是连续的和增长的(除非是AUTOINCREMENT)(至少我没有找到这方面的信息,但是看看SQLite的源代码,它排除了这一点)

mwkjh3gx

mwkjh3gx7#

最简单的方法是使用:

SELECT MAX(id) FROM yourTableName LIMIT 1;

如果您试图获取关系中的最后一个id以影响另一个表,例如:

  • 一月一日*

在本例中,请使用类似以下内容:

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

然后使用cmd_result来确定先前的查询是否已成功执行,
比如:
if(cmd_result > 0),后跟查询SELECT MAX(id) FROM yourTableName LIMIT 1;
这只是为了确保在前面的命令没有添加任何行的情况下,您没有将错误的行id作为目标。
事实上,cmd_result > 0条件是非常必要,以防出现任何故障。特别是如果您正在开发一个严肃的应用程序,您不希望您的用户醒来时发现随机项目添加到他们的发票。

sauutmhj

sauutmhj8#

我最近提出了一个解决这个问题的方案,它牺牲了一些性能开销,以确保您获得正确的最后插入的ID。
假设您有一个表people,添加一个名为random_bigint的列:

create table people (
    id int primary key,
    name text,
    random_bigint int not null
);

random_bigint上添加唯一索引:

create unique index people_random_bigint_idx
ON people(random_bigint);

在应用程序中,每当插入一条记录时都要生成一个随机bigint,我猜发生冲突的可能性很小,所以应该处理这个错误。
我的应用在Go语言中,生成随机bigint的代码如下所示:

func RandomPositiveBigInt() (int64, error) {
    nBig, err := rand.Int(rand.Reader, big.NewInt(9223372036854775807))
    if err != nil {
        return 0, err
    }

    return nBig.Int64(), nil
}

插入记录后,使用where过滤器对随机bigint值查询表:

select id from people where random_bigint = <put random bigint here>

唯一索引会在插入时增加少量开销,而id查找虽然因为索引而非常快,但也会增加一点开销。
但是,此方法将保证最后插入的ID正确。

相关问题