INSERT или UPDATE строки в SQL: безопасные методы для SQLite, PostgreSQL, MySQL

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

Для одновременного обновления существующих строк и вставки новых в PostgreSQL используйте выражение INSERT INTO ... ON CONFLICT ... DO UPDATE, а в MySQL — команду INSERT INTO ... ON DUPLICATE KEY UPDATE.

Код для PostgreSQL:

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

Код для MySQL:

INSERT INTO table_name (id, data) VALUES (1, 'abc')
ON DUPLICATE KEY UPDATE data = 'abc';

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

Подробнее о теме: UPSERT

Разработчикам баз данных нередко приходится столкнуться с задачей обновления уже существующих данных или вставки новых, если соответствующая запись не найдена. Такие операции называются upsert. В данном разделе мы рассмотрим, как реализовать upsert в различных базах данных.

REPLACE INTO: MySQL и SQLite

В MySQL и SQLite доступна команда REPLACE INTO, выполняющая функцию upsert. Если строка с указанным уникальным ключом уже существует, команда заменяет её на новую.

Важное предупреждение

Использование REPLACE INTO может вызвать потерю связей между таблицами из-за удаления и последующего добавления строк, что может неожиданно активировать триггеры.

Подробнее об этом расскажет наш спикер на видео

INSERT ON DUPLICATE KEY UPDATE: MySQL

Для MySQL наиболее удобной является команда INSERT INTO ... ON DUPLICATE KEY UPDATE, которая обновляет запись в случае дублирования ключа, пропуская этап её удаления и позволяя таким образом избежать описанных выше проблем.

INSERT OR REPLACE INTO: SQLite

В SQLite команда INSERT OR REPLACE INTO эквивалентна REPLACE INTO и может столкнуться с аналогичными проблемами.

UPSERT и параллельные запросы: Совместимость между СУБД

Универсальности в совместимости операций upsert между различными СУБД нет — каждый диалект SQL обладает своими нюансами синтаксиса. Дабы обеспечить целостность данных и учесть особенности параллельных запросов, всегда оборачивайте операции upsert в транзакции.

UPSERT и проблематика параллелизма

Параллельное обновление или вставка данных могут вызвать конфликты. Эксклюзивные блокировки или сериализованные уровни изоляции необходимы для их устранения.

Лучшие практики в работе с параллелизмом:

• Транзакции демонстрируют свою эффективность при обеспечении атомарности операций.
• Важно проводить тестирование логики upsert в условиях одновременного доступа.
• Следует обращать внимание на обработку исключений, связанных с ограничениями уникальности.

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

Пусть вам потребовалось обновить данный о книге в каталоге библиотеки или добавить новую запись, если книга отсутствует:

Если Книга существует 📚: ОБНОВЛЯЕМ данные.
Если Книги нет 🔍: ВСТАВЛЯЕМ новую запись.

Пример использования UPSERT (если поддерживается вашей СУБД):

UPSERT INTO library (id, title, author) VALUES (123, 'New Book', 'New Author');

Таким образом, подобно усердному библиотекарю, компьютер сам решит, что ему следует делать: обновлять существующую запись о книге или создавать новую:

Библиотекарь 🧐: Присутствует ли книга в каталоге?
- Да ✅: *Обновляем сведения* (📖➡️🔄)
- Нет ❌: *Добавляем новую книгу* (📖➡️➕)

Изучаем UPSERT в различных СУБД

Помимо MySQL и SQLite, важно понимать, что СУБД могут предлагать различный синтаксис для выполнения операций upsert.

PostgreSQL

В PostgreSQL upsert реализуется через INSERT INTO ... ON CONFLICT ... DO UPDATE с возможностью подробного управления обработкой конфликтов.

Немного о NoSQL

Некоторые NoSQL базы данных, такие как MongoDB, располагают встроенным механизмом upsert, который активируется опцией upsert в функции update().

SQL Server и MERGE

В SQL Server для выполнения upsert используется оператор MERGE. Для освоения этих запросов следует обратиться к официальной документации соответствующей системы.

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

1. MySQL :: Руководство по MySQL 8.0 :: 13.2.7.2 Инструкция INSERT ... ON DUPLICATE KEY UPDATE — официальное руководство MySQL по использованию INSERT ON DUPLICATE KEY.
2. PostgreSQL: Документация: 16: INSERT — документация PostgreSQL по механизму UPSERT.
3. UPSERT — руководство SQLite по использованию UPSERT.
4. MERGE (Transact-SQL) – SQL Server | Microsoft Learn — официальная документация Microsoft по использованию оператора MERGE в SQL Server.
5. Postgres: INSERT if does not exist already – Stack Overflow — подробное обсуждение на Stack Overflow, демонстрирующее работу UPSERT в PostgreSQL.