Получить идентификатор строки при обновлении

Question

Допустим, у меня есть таблица следующей структуры:

| name        | Type          |
| ----------- |:-------------:|
| id          | primary       |
| word        | unique        |
| frequency   | integer       |

К этой таблице я делаю вставки, когда происходит дублирование, я обновляю столбец частоты. Псевдокод выглядит примерно так:

try {
    INSERT into WORDLIST word1
    id = lastInsertedId
} catch(Exception) {
    //if a duplicate happens
    UPDATE wordlist WHERE word = "word1"
    id = SELECT id FROM wordlist where word = "word1"
}
//save the updated/inserted id somewhere

Проблема с приведенным выше кодом заключается в том, что при возникновении дубликата я вынужден выполнить дополнительный запрос на выборку, чтобы получить идентификатор обновленной строки, которая снижает производительность и замедляет работу приложения примерно на 30%.

Я открыт для других подходов, но не смог придумать что-то лучшее, чем этот подход "попробуй / поймай" с дополнительным запросом

Answer 1

Как word уже есть уникальный индекс, который вы можете попытаться упростить ваш запрос с помощью insert or replace:

INSERT OR REPLACE into WORDLIST word1
id = last_insert_rowid()

Обратите внимание, что в случае конфликта создается новый rowid/ID, а старый удаляется. Если вам нужно сохранить идентификатор, вы можете прибегнуть к использованию триггера, который может работать лучше, чем обработка специального случая в коде приложения.

Если все, что вам нужно, это реализовать счетчик посещений, вы можете посмотреть на следующий ответ: /questions/44592/vstavit-ili-obnovit-v-sqlite/44603#44603

Answer 2

Оператор UPDATE не может вернуть данные. Если вам нужен идентификатор, вам нужно запустить SELECT.

Обратите внимание, что любой ВСТАВКА или ОБНОВЛЕНИЕ должны проверить word столбец для дубликатов, поэтому SELECT будет полностью работать из кэша. Вероятно, любое замедление происходит из-за запуска нескольких изменений с автоматическими транзакциями.

Кроме того, использование исключения для обнаружения дубликата приведет к поломке, если возникнет какая-либо другая ошибка. Лучше сначала проверить наличие дубликатов (что будет не менее эффективно по причине, указанной выше):

BEGIN;
SELECT id FROM wordlist WHERE word = ?;
if found:
    UPDATE wordlist ... WHERE id = ?;   -- faster than word=?
else:
    INSERT INTO wordlist;
COMMIT;