SQLite: реализация функции INSERT ON DUPLICATE KEY UPDATE

Быстрый ответ

INSERT INTO table_name(id, data) VALUES(1, 'New Data')
ON CONFLICT(id) DO UPDATE SET data = 'Updated Data';

Рассмотренный выше пример демонстрирует операцию UPSERT в SQLite, реализуемую через INSERT ON CONFLICT. Если при попытке вставки данных возникает конфликт по уникальному ключу, в данном случае id, то обновляется существующая запись (значение в колонке data станет 'Updated Data'). Значения, которые пытались вставиться, доступны через ключевое слово excluded.

Реализация UPSERT в SQLite

SQLite не содержит операцию "INSERT ON DUPLICATE KEY UPDATE", характерную для MySQL. Однако доступен клоз ON CONFLICT, позволяющий управлять поведением при нарушении ограничений уникальности. Есть возможность игнорировать дубликаты, обновлять их или полностью остановить операцию вставки.

Создание таблицы для UPSERT

Для выполнения UPSERT, таблица должна содержать первичный ключ или уникальные индексы:

CREATE TABLE table_name (
    id INTEGER PRIMARY KEY,
    data TEXT,
    UNIQUE(id)  -- да, я уникален 😎
);

SQLite сначала производит стандартную вставку данных. Однако при возникновении конфликта с уникальными ограничениями срабатывает операция UPDATE, указанная в ON CONFLICT.

Метод "INSERT OR IGNORE"

В некоторых ситуациях подходит метод "INSERT OR IGNORE", который позволяет игнорировать вставку в случае обнаружения дубликатов. Так, запись добавляется в таблицу только при её отсутствии, в противном случае операция игнорируется. После этого можно выполнить обновление, если это требуется:

INSERT OR IGNORE INTO table_name(id, data) VALUES(1, 'Initial Data');  -- INSERT без волнений 😇
UPDATE table_name SET data = 'Updated Data' WHERE id = 1;

Таким образом, исчезает необходимость в проверочном запросе SELECT перед принятием решения о вставке или обновлении данных.

Обеспечение атомарности

Все связанные операции следует выполнять в рамках одной транзакции, чтобы обеспечить целостность данных:

BEGIN TRANSACTION;  -- Начало транзакции
-- здесь расположена логика UPSERT
COMMIT;  -- Завершение транзакции

Это предотвращает вмешательство в процесс и обеспечивает сохранность данных, гарантируя, что транзакция либо выполнится полностью, либо не будет выполнена совсем.

Визуализация

📬 Статус: | 💌 Вставка  | 🚫📬➡🔄💌 Upsert           |
-----------|-------------|-------------------------|
Пусто      | 📥 💌       | 🔄 Не требуется         |
Занято     | 🚫 Уже есть | 🔄 📤➡📥 Замена 💌       |

Суть в том, что при пустой ячейке, в неё добавляются новые данные. Если ячейка занята, то её содержимое обновляется.

Полезные материалы

1. UPSERT — Официальная документация UPSERT SQLite.
2. sql – UPSERT *не INSERT и не REPLACE – Stack Overflow – Дискуссия об использовании UPSERT и различных примерах его применения.
3. SQLite Syntax: upsert-clause – Объяснение синтаксиса клоза UPSERT в SQLite.
4. SQLite: INSERT Statement — Информация о командах INSERT.
5. Postgres – How to get the ID of conflicting row in upsert? – DBA Stack Exchange – Решения и обмен опытом использования UPSERT, включая другие СУБД.

Завершение

Усердно практикуйтесь, продолжайте кодировать и не забудьте поддержать этот материал своим голосом! До новых встреч в следующих статьях. Удачного кодирования!👩‍💻