Оптимизированный запрос SQL: выбор или вставка и возврат ID

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

Для реализации логики IF EXISTS и INSERT вы можете использовать следующий код:

BEGIN TRANSACTION;
IF NOT EXISTS (SELECT 1 FROM MyTable WHERE MyColumn = 'MyValue')
    INSERT INTO MyTable (MyColumn) VALUES ('MyValue');
SELECT * FROM MyTable WHERE MyColumn = 'MyValue';
COMMIT TRANSACTION;

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

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

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
...
COMMIT TRANSACTION;

Управление конкуренцией и контроль транзакций

Обратите внимание на уровень изоляции транзакций, чтобы минимизировать риски взаимной блокировки или конкурентного доступа. Используйте SELECT с подсказкой NOLOCK для уменьшения накладных расходов на блокировку:

BEGIN TRANSACTION;
IF NOT EXISTS (SELECT 1 FROM MyTable WITH (NOLOCK) WHERE MyColumn = 'MyValue')
    INSERT INTO MyTable (MyColumn) VALUES ('MyValue');
SELECT * FROM MyTable WHERE MyColumn = 'MyValue';
COMMIT TRANSACTION;

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

Воспользуйтесь функцией SCOPE_IDENTITY(), чтобы после вставки получить идентификатор новой записи:

SELECT SCOPE_IDENTITY() AS 'Identity';

Она вернет последнее значение идентификатора для колонки, объявленной в качестве идентификатора.

Обработка дублирования данных

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

BEGIN TRY
    BEGIN TRANSACTION;
    ...
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 2627
        SELECT * FROM MyTable WHERE MyColumn = 'MyValue';
    ELSE
        ROLLBACK TRANSACTION;
        RAISERROR('Произошла неожиданная ошибка', 16, 1);
    END CATCH;

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

Рассмотрим операцию "ЕСЛИ СУЩЕСТВУЕТ, ТО ВЫБРАТЬ, ИНАЧЕ ВСТАВИТЬ И ЗАТЕМ ВЫБРАТЬ" на примере библиотеки:

🔍 Ищем конкретную книгу (SELECT)

ЕСЛИ книга ЕСТЬ на полке:
    📖 Читаем её! (ТО ВЫБРАТЬ)
ИНАЧЕ:
    ✍️ Пишем новую книгу, вставляем на полку (ИНАЧЕ ВСТАВИТЬ)
    📖 Читаем свеженаписанную книгу! (И ЗАТЕМ ВЫБРАТЬ)

Библиотекарь в данной операции обеспечивает:

1. Поиск книги на полке. (🔍 ЕСЛИ СУЩЕСТВУЕТ)
2. Вручение её вам, если она найдена. (📖 ТО ВЫБРАТЬ)
3. При отсутствии – написание новой книги и помещение её на полку. (✍️ ИНАЧЕ ВСТАВИТЬ)
4. Вручение вам новой книги. (📖 И ЗАТЕМ ВЫБРАТЬ)

Таким же образом в SQL выполняется операция, гарантируя актуальность данных.

Решения для различных диалектов SQL

Существуют разнообразные методы выполнения операций upsert в разных системах управления базами данных:

1. SQL Server MERGE: Объединяет несколько DML-операций в одном выражении.
2. PostgreSQL UPSERT: Использует ON CONFLICT в команде INSERT для разрешения конфликтов.
3. MySQL's INSERT ON DUPLICATE: Позволяет обрабатывать дублирующиеся записи, нарушающие уникальность ключей.

Сохранение целостности данных и повышение производительности

Ограничение UNIQUE помогает предотвращать дублирование данных. Выбирайте только те столбцы, которые необходимы для запроса, чтобы уменьшить объем передаваемых данных:

SELECT TableID, MyColumn FROM MyTable WHERE MyColumn = 'MyValue';

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

1. EXISTS (Transact-SQL) – SQL Server | Microsoft Learn — Документация о EXISTS.
2. MERGE (Transact-SQL) – SQL Server | Microsoft Learn — Информация о MERGE.
3. PostgreSQL Documentation: INSERT — Руководство по UPSERT в PostgreSQL.
4. How to implement a conditional Upsert stored procedure? – Stack Overflow — Обсуждение upsert в SQL.
5. SQL Server MERGE to insert, update and delete at the same time — Использование MERGE.
6. MySQL 8.0 Reference Manual: INSERT ... ON DUPLICATE KEY UPDATE Statement — Обработка дублирующихся ключей в MySQL.