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

Question

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

所以，我的问题是这样的。新消息的 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);
}

Answer 1

另一种选择是查看系统表sqlite_sequence。如果您使用自动增量主键创建了任何表，您的 sqlite 数据库将自动拥有该表。该表用于让 sqlite 跟踪自动增量字段，以便即使在删除某些行或某些插入失败后，它也不会重复主键（在此处阅读有关此内容的更多信息 http://www.sqlite.org/autoinc.html）。

因此，使用此表还有一个额外的好处，即使您插入了其他内容（当然是在其他表中！），您也可以找到新插入项目的主键。确保您的插入成功后（否则您将得到一个错误的数字），您只需执行以下操作：

select seq from sqlite_sequence where name="table_name"

Answer 2

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

使用 SQlite，看起来就像是

SELECT last_insert_rowid()

在插入后立即执行自动增量。

http://www.mail-archive.com/[email protected]/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();
}

Answer 3

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

他们在 doco 中声明如下：

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

这是来自 sqlite.org doco

Answer 4

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

SELECT rowid from your_table_name order by ROWID DESC limit 1

Answer 5

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

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

Answer 6

这是一篇旧文章，但对于更多的读者来说，下面可能是最简单、最可靠的方法 - 只需使用带有 RowId 的 Returning 子句（这是 SQLite 中数字主键的特殊别名）：

insert into MyTable (Column1, Column2) Values (Val1, Val2) Returning RowId;

在 .Net 中，您可以运行它并使用 ExecuteScalar 返回值。

Answer 7

sqlite3_last_insert_rowid() 在多线程环境中是不安全的（并且在 SQLite 上有这样的记录）
然而好消息是你可以利用这个机会，请参阅下面的

ID 保留在 SQLite 中没有实现，如果你知道数据中总是有变化，你也可以使用你自己的唯一主键来避免 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); 

// 注意 > in: ROWID>%zd，因为我们已经知道 IdLast 不是我们要寻找的那个。

由于对 sqlite3_last_insert_rowid 的第二次调用是在 INSERT 之后立即完成的，因此此 SELECT 通常最多只返回 2 或 3 行。
然后在 SELECT 结果中搜索您插入的数据以找到正确的 ID。

性能改进：由于对 sqlite3_last_insert_rowid() 的调用比 INSERT 快得多，（即使互斥锁可能会导致错误，但从统计上来看这是正确的）我打赌 IdEnd 是正确的，并在最后展开 SELECT 结果。几乎在我们测试的每种情况下，最后一个 ROW 都包含您要查找的 ID）。

性能改进：如果您有额外的 UNIQUE Key，则将其添加到 WHERE 即可仅获取一行。

我尝试使用3个线程进行大量插入，它按预期工作，准备+数据库处理占用了绝大多数CPU周期，然后结果是混合ID的奇数在1/1000插入的范围内（IdEnd>的情况） IdLast+1)

所以额外的 SELECT 来解决这个问题的代价相当低。

换句话说，在绝大多数插入中使用 sqlite3_last_insert_rowid() 的好处是很大的，如果小心的话，甚至可以安全地在 MT 中使用。

注意：事务模式下的情况稍微尴尬一些。

另外，SQLite 没有明确保证 ID 是连续的并且不断增长（除非 AUTOINCRMENT）。 （至少我没有找到相关信息，但查看 SQLite 源代码就排除了这一点）

Answer 8

最简单的方法是使用：

SELECT MAX(id) FROM yourTableName LIMIT 1;

如果您试图获取关系中的最后一个 id 以影响另一个表，例如：
( 如果添加了发票，则将 ItemsList 添加到发票 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，以防上一个命令未添加任何行 ID行。

事实上cmd_result> 0 条件是非常必要的，以防万一失败。特别是如果您正在开发一个严肃的应用程序，您不希望您的用户醒来时发现随机项目添加到他们的发票中。

Answer 9

我最近想出了一个解决这个问题的方法，它牺牲了一些性能开销来确保您获得正确的最后插入的 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 正确。

Answer 10

我的解决方案是，在一个事务中，我们插入数据并立即请求 PRIMARY KEY 字段的最大值。

这是我的例子。
DayliForecast.sq：

import kotlin.Int;

CREATE TABLE IF NOT EXISTS DailyForecast (
    pid             INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT NULL,
    year            INTEGER AS Int  NOT NULL,
    month           INTEGER AS Int  NOT NULL,
    day_of_month    INTEGER AS Int  NOT NULL,
    hours_of_sun    REAL            NOT NULL DEFAULT 0,
    forecast_key    TEXT            NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS DailyForecastDate ON DailyForecast (
    year            DESC,
    month           DESC,
    day_of_month    DESC,
    forecast_key    DESC
);

insert:
REPLACE INTO DailyForecast
    VALUES (?,?,?,?,?,?);

lastPid:
SELECT MAX(pid) FROM DailyForecast;

selectByDateRange:
SELECT * FROM DailyForecast
    WHERE
        forecast_key = ?
        AND year >= ?
        AND month >= ?
        AND day_of_month >= ?
        AND year <= ?
        AND month <= ?
        AND day_of_month <= ? LIMIT 5;

ForecastDbRepositoryImpl：

class ForecastDbRepositoryImpl(private val database: Database): ForecastDbRepository() {

    override suspend fun insertDailyForecast(dailyForecast: DailyForecast): Long {
        return database.transactionWithResult {
            insertForecast(dailyForecast)
        }
    }

    override suspend fun insertDailyForecast(dailyForecasts: List<DailyForecast>): List<Long> {
        return database.transactionWithResult {
            dailyForecasts.map { insertForecast(it) }
        }
    }

    private fun insertForecast(dailyForecast: DailyForecast): Long {
        database.dayliForecastQueries.insert(
            pid = dailyForecast.pid.takeIf { it >= 0L },
            year = dailyForecast.year,
            month = dailyForecast.month,
            day_of_month = dailyForecast.day_of_month,
            hours_of_sun = dailyForecast.hours_of_sun,
            forecast_key = dailyForecast.forecast_key
        )
        return database.dayliForecastQueries.lastPid().executeAsOne().MAX ?: 0L
    }
}

我刚刚自己测试过，效果很好！