Jak odzyskać ostatni automatycznie zwiększany identyfikator z tabeli SQLite?


84

Mam tabelę Wiadomości z identyfikatorem kolumn (klucz podstawowy, autoincrement) i Treść (tekst).
Mam tabelę Użytkownicy z kolumnami nazwa użytkownika (klucz podstawowy, tekst) i hash.
Wiadomość jest wysyłana przez jednego Nadawcę (użytkownika) do wielu odbiorców (użytkownika), a odbiorca (użytkownik) może mieć wiele wiadomości.
Utworzyłem tabelę Messages_Recipients z dwiema kolumnami: MessageID (odnosząca się do kolumny ID w tabeli Messages i Recipient (odnosząca się do kolumny nazwa użytkownika w tabeli Users).

Więc mam takie pytanie. Identyfikator nowej wiadomości zostanie utworzony po jej zapisaniu w bazie danych. Ale jak mogę przechowywać odniesienie do MessageRow, który właśnie dodałem, aby pobrać ten nowy MessageID?
Zawsze mogę oczywiście przeszukać bazę danych pod kątem ostatniego dodanego wiersza, ale to może zwrócić inny wiersz w środowisku wielowątkowym?

EDYCJA: Jak rozumiem dla SQLite, możesz użyć SELECT last_insert_rowid(). Ale jak nazwać to oświadczenie z ADO.Net?

Mój kod trwałości (wiadomości i wiadomościRecipients to 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);
}

Używam SQlite (wersja ADO.Net)
Dabblernl

Odpowiedzi:


88

W przypadku SQL Server należy SELECT SCOPE_IDENTITY (), aby uzyskać ostatnią wartość tożsamości dla bieżącego procesu.

W przypadku SQlite wygląda na to, że zrobisz to automatycznie

SELECT last_insert_rowid()

natychmiast po włożeniu wkładu.

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

W odpowiedzi na Twój komentarz, aby uzyskać tę wartość, chciałbyś użyć kodu SQL lub OleDb, takiego jak:

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

2
Jeszcze raz dziękuję! Niestety nie działa, ponieważ funkcja last_insert_rowid () musi zostać wywołana przed zamknięciem połączenia używanego do aktualizacji DataTable. Może to być jednak dziwactwo SQLite ...
Dabblernl

1
Przepraszam, tak, to prawdopodobnie prawda. Czy próbowałeś go wykonać po komunikatach messagesAdapter.Update (komunikaty);
Mike W.

2
@Dabblernl wydaje się, że inna odpowiedź jest bardziej wiarygodna, możesz zmienić zaakceptowaną odpowiedź, jeśli uważasz, że inna jest bardziej pomocna
MikeW

Ale RowId nie zawsze jest tym samym, co PRIMARY KEY. Z wyjątkiemIf the table has a column of type INTEGER PRIMARY KEY then that column is another alias for the rowid.
Кое Кто

110

Inną opcją jest przyjrzenie się tabeli systemowej sqlite_sequence. Twoja baza danych sqlite będzie miała tę tabelę automatycznie, jeśli utworzyłeś tabelę z kluczem podstawowym autoincrement. Ta tabela jest przeznaczona dla sqlite do śledzenia pola autoincrement, aby nie powtarzał klucza podstawowego nawet po usunięciu niektórych wierszy lub po niepowodzeniu wstawiania (więcej informacji na ten temat można znaleźć tutaj http://www.sqlite.org/autoinc .html ).

Tak więc z tą tabelą jest dodatkowa korzyść, że możesz znaleźć klucz podstawowy nowo wstawionego elementu nawet po wstawieniu czegoś innego (oczywiście w innych tabelach!). Po upewnieniu się, że Twoja wstawka się powiodła (w przeciwnym razie otrzymasz fałszywą liczbę), wystarczy zrobić:

select seq from sqlite_sequence where name="table_name"

1
Działa, gdy sekwencja jest zwiększana i po usunięciu wiersza.
Marek Bar

Dobra odpowiedź. Dziwne, że w „DB Browser for SQLite”, jeśli usuniesz wiersz z „sqlite_sequence”, nie otrzymasz już ostatniego identyfikatora dla jakiejś tabeli.
CoolMind

9

Miałem problemy z używaniem SELECT last_insert_rowid()w środowisku wielowątkowym. Jeśli inny wątek wstawi do innej tabeli, która ma autoinc, last_insert_rowid zwróci wartość autoinc z nowej tabeli.

Oto, gdzie stwierdzają to w dokumencie:

Jeśli oddzielny wątek wykonuje nowe INSERT w tym samym połączeniu z bazą danych, podczas gdy funkcja sqlite3_last_insert_rowid () jest uruchomiona i zmienia w ten sposób ostatni identyfikator wiersza wstawiania, wówczas wartość zwracana przez sqlite3_last_insert_rowid () jest nieprzewidywalna i może nie odpowiadać ani starej, ani nowej ostatniej wstaw rowid.

To pochodzi z sqlite.org doco


1
Myślę, że możesz użyć osobnego połączenia dla każdego wątku, ale zauważyłem, że ma duży hit wydajnościowy. W moim scenariuszu mogę wykonać tylko około 15 wstawień na sekundę.
Fidel

Domyślam się, że oddzielne wątki to ten sam problem. Niestety do tej pory nie wydaje się, aby można było bezpiecznie obejść wątki, moje przeczucie jest takie, że wielokrotne transakcje nie wystarczyłyby ... więc bądź ostrożny: |
rogerdpack

5

Przykładowy kod z rozwiązania @polyglot

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

4

Zgodnie z Android Sqlite pobierz ostatni identyfikator wiersza wstawiania, istnieje inne zapytanie:

SELECT rowid from your_table_name order by ROWID DESC limit 1

1
Ta opcja powoduje spadek wydajności ze względu na wykonanie algorytmu sortowania przed znalezieniem rowid
Sr. Libre

2
Może jest mniej optymalny, ale faktycznie działa po zamknięciu i ponownym otwarciu połączenia, a pozostałe dwa rozwiązania nie dla mnie.
Francine DeGrood Taylor

Następujący wariant unika ORDER BY. Powinno być szybciej, ale nie testowałem tego:SELECT MAX(rowid) FROM your_table_name
tanius
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.