Условная вставка в Oracle, PostgreSQL, MySQL: проверка ключа

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

Для устранения лишних дубликатов в Oracle используется оператор MERGE:

MERGE INTO my_table t USING (SELECT 1 as id, 'Образец данных' as data FROM dual) s
ON (t.id = s.id)
WHEN NOT MATCHED THEN INSERT (id, data) VALUES (s.id, s.data);

Роль команды MERGE заключается в проверке наличия соответствующих записей в my_table по условию ON. При их отсутствии (WHEN NOT MATCHED) происходит добавление новой записи. Этот способ оптимально подходит для функции upsert в Oracle.

Знакомство с Oracle: условное добавление записей

Использование PL/SQL с обработкой исключительных ситуаций

В рамках задач, где идеалом является максимальная надежность и большая конкурентность, предварительная проверка наличия записи с помощью PL/SQL способна увеличить производительность.

BEGIN
   INSERT INTO my_table (id, data)
   SELECT 2, 'Еще один образец' FROM dual
   WHERE NOT EXISTS (SELECT 1 FROM my_table WHERE id = 2);

EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      NULL; // Исключение сработало, ошибка дублирования проигнорирована
END;

В этом примере демонстрируется попытка вставки данных. Если в процессе возникает ошибка дублирования ключа (DUP_VAL_ON_INDEX), она перехватывается и обрабатывается без лишних хлопот.

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

IGNORE_ROW_ON_DUPKEY_INDEX: прощай, дубликаты (начиная с версии 11.2.0.1)

В Oracle версии 11.2.0.1 и более поздних доступна подсказка IGNORE_ROW_ON_DUPKEY_INDEX, облегчающая процесс вставки данных:

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(my_table, my_table_pk) */ INTO my_table (id, data) 
VALUES (3, 'Уникальные данные');

Эта подсказка предотвращает добавление записей, уже присутствующих в таблице. Здесь my_table_pk – это индекс первичного ключа таблицы my_table.

Условная логика в Oracle SQL? Без проблем.

MERGE с оговоркой

Эффективность оператора MERGE зависит от его правильного применения.

MERGE INTO my_table t USING (
    SELECT 4 as id, 'Новые данные' as data FROM dual
) s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.data = s.data
WHEN NOT MATCHED THEN INSERT (id, data) VALUES (s.id, s.data);

Здесь MERGE объединяет в себе UPDATE и INSERT. Вызываемые действия определяются в зависимости от результатов поиска с помощью WHEN MATCHED. Таким образом, операция upsert происходит эффективно и легко.

На сцену выходит LEFT OUTER JOIN

Традиционные способы условных вставок вас не устраивают? Обратите внимание на LEFT OUTER JOIN:

INSERT INTO my_table (id, data) 
SELECT 5, 'Исключительные данные' FROM dual
WHERE NOT EXISTS (
    SELECT 1 FROM my_table t 
    LEFT OUTER JOIN dual d ON t.id = 5 
    WHERE t.id IS NOT NULL
);

LEFT OUTER JOIN проверяет наличие id в my_table. Если таковой не находится, NULL позволяет условию WHERE добавить данные.

Условное добавление записей: в поисках максимальной эффективности

ТЕСТИРОВАНИЕ – ЗАЛОГ УСПЕХА!

Тестирование – это ваш главный союзник на пути к успеху. Обязательно учитывайте индексы, размер таблицы, нагрузку и конкурентность. Не бойтесь проходить этот путь не один раз: ТЕСТИРОВАНИЕ – ВАШ ЛУЧШИЙ ДРУГ!

Обращайте внимание на конкуренцию

При одновременном выполнении транзакций с операцией MERGE, будьте внимательны. Используйте уровень изоляции SERIALIZABLE или блокировки для предотвращения конфликтов.

Если не знаешь, спроси

Всегда продолжайте учиться. Не стесняйтесь изучать материалы – книги, блоги, форумы. Настоящие клады знаний ждут вас, например, в блоге Гая Харрисона.

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

'Insert if not exists' можно представить в виде сторожа, который пропускает в клуб только новых посетителей:

🛡: "Вы мне не знакомы, как вас зовут?"

// Новый гость
🧍‍♂️: "Меня зовут Джо."
🛡: "Рад вас видеть, Джо! Заходите! 🎪"

// Гость, который уже был в клубе
🧍‍♂️: "Я снова Джо."
🛡: "Джо, вы уже с нами. Не нужно дублируться! 🚫"

Если вас нет в списке, вам отказано в доступе:

INSERT INTO pavilion (visitor_name)
SELECT 'Joe' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM pavilion WHERE visitor_name = 'Joe');

Так сторож (🛡) и Джо (🧔🏽) становятся идеальным объяснением принципа действия!

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

1. Документация Oracle – Оператор MERGE — ваше главное руководство по MERGE.
2. Ask TOM – Как применять 'Insert if not exists' — полезные советы от эрудита Oracle.
3. Oracle-Blog – ‘UPSERT’ через MERGE — детальный разбор использования MERGE при выполнении операций UPSERT.
4. Stack Overflow – Примеры использования 'INSERT IF NOT EXISTS' — разнообразные примеры применения INSERT IF NOT EXISTS от участников сообщества.
5. Tech on the Net – Oracle / PLSQL: Условие EXISTS — понятное объяснение использования условия EXISTS в Oracle/PLSQL.