Слияние (SQL)
Система управления реляционными базами данных использует SQL. MERGE (также называемые upsert ) для INSERT новые рекорды или UPDATE существующие записи в зависимости от соответствия условию . Он был официально представлен в стандарте SQL:2003 и расширен. [ нужна ссылка ] в стандарте SQL:2008 .
Использование
[ редактировать ]MERGE INTO tablename USING table_reference ON (condition)
WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN
INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);
Правое соединение применяется к цели (таблица INTO) и источнику (таблица/представление/подзапрос USING), где цель – это левая таблица, а источник – правая. Четыре возможные комбинации дают следующие правила:
- Если поле(я) ON в источнике соответствует полю(ям) ON в Target, то UPDATE
- Если поле(я) ON в источнике не соответствует полю(ям) ON в объекте, то INSERT
- Если поле(я) ON не существует в источнике, но существует в цели, то никаких действий не выполняется.
- Если поля ON не существуют ни в источнике, ни в цели, никаких действий не выполняется.
Если несколько исходных строк соответствуют заданной целевой строке, в соответствии со стандартами SQL:2003 возникает ошибка. Вы не можете обновить целевую строку несколько раз с помощью инструкции MERGE.
Реализации
[ редактировать ]Системы управления базами данных PostgreSQL , [1] Базы данных Oracle , IBM Db2 , Teradata , EXASOL , Firebird , CUBRID , H2 , HSQLDB , MS SQL , Vectorwise и Apache Derby поддерживают стандартный синтаксис. Некоторые также добавляют нестандартные расширения SQL.
Синоним
[ редактировать ]В некоторых реализациях баз данных термин « Upsert » ( сочетание обновлений . и вставок ) используется для оператора базы данных или комбинации операторов, который вставляет запись в таблицу в базе данных, если запись не существует или если запись уже существует , обновляет существующую запись. Этот синоним используется в PostgreSQL (v9.5+). [2] и SQLite (v3.24+). [3] Он также используется для сокращения эквивалентного псевдокода «MERGE».
Он используется в базе данных SQL Microsoft Azure . [4]
Другие нестандартные реализации
[ редактировать ]Некоторые другие системы управления базами данных поддерживают это или очень похожее поведение посредством своих собственных нестандартных расширений SQL.
MySQL , например, поддерживает использование INSERT ... ON DUPLICATE KEY UPDATE синтаксис [5] который можно использовать для достижения аналогичного эффекта с тем ограничением, что соединение между целью и источником должно выполняться только по ограничениям PRIMARY KEY или UNIQUE, что не требуется в стандарте ANSI/ISO. Он также поддерживает >REPLACE INTO синтаксис, [6] который сначала пытается вставить, а если это не удается, удаляет строку, если она существует, а затем вставляет новую. Существует также IGNORE положение о INSERT заявление, [7] который сообщает серверу игнорировать ошибки «дубликата ключа» и продолжать работу (существующие строки не будут вставлены или обновлены, но будут вставлены все новые строки).
SQLite INSERT OR REPLACE INTO работает аналогично. Он также поддерживает REPLACE INTO как псевдоним для совместимости с MySQL. [8]
Firebird Поддержка MERGE INTO однако не выдает ошибку при наличии нескольких строк исходных данных. Кроме того, существует однорядная версия, UPDATE OR INSERT INTO tablename (columns) VALUES (values) [MATCHING (columns)], но последний не дает вам возможности выполнять разные действия при вставке и обновлении (например, устанавливать новое значение последовательности только для новых строк, а не для существующих.)
IBM Db2 расширяет синтаксис несколькими WHEN MATCHED и WHEN NOT MATCHED предложения, различая их ... AND some-condition охранники .
Microsoft SQL Server расширяется за счет поддержки защиты, а также поддержки левого соединения через WHEN NOT MATCHED BY SOURCE статьи.
PostgreSQL поддерживает слияние начиная с версии 15, но ранее поддерживалось слияние через INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action. [9]
КУБРИД поддерживает MERGE INTO[10] заявление. И поддерживает использование INSERT ... ON DUPLICATE KEY UPDATE синтаксис. [11] Он также поддерживает REPLACE INTO для совместимости с MySQL. [12]
Апач Феникс поддерживает UPSERT VALUES[13] и UPSERT SELECT[14] синтаксис.
Spark SQL поддерживает UPDATE SET * и INSERT * оговорки в действиях. [15]
Апач Импала поддерживает UPSERT INTO ... SELECT. [16]
Использование в NoSQL
[ редактировать ]Похожая концепция применяется в некоторых базах данных NoSQL .
Например, в MongoDB поля значения, связанного с ключом, могут быть обновлены с помощью update операция. update выдает ошибку, если ключ не найден.
В update работы можно установить upsert флаг: в этом случае сохраняется новое значение, связанное с данным ключом, если он не существует, в противном случае заменяется все значение.
В Redis SET операции устанавливают значение, связанное с данным ключом. Redis не знает никаких подробностей внутренней структуры значения, поэтому обновление не имеет смысла. Итак, SET операция всегда имеет семантику установки или замены .
См. также
[ редактировать ]- Присоединяйтесь, в частности:
Ссылки
[ редактировать ]- ^ «Э.1. Выпуск 15» . Документация PostgreSQL . 13 октября 2022 года. Архивировано из оригинала 13 октября 2022 года . Проверено 13 октября 2022 г.
- ^ «Обновление PostgreSQL с использованием инструкции INSERT ON CONFLICT» . Учебное пособие по PostgreSQL . Архивировано из оригинала 28 ноября 2022 года.
- ^ « upsert », SQLite, посещение 06.06.2018.
- ^ «СЛИВАНИЕ (Transact-SQL)» . Справочник по Transact-SQL (ядро СУБД) . Microsoft Learn. Архивировано из оригинала 24 июня 2016 г.
- ^ MySQL :: Справочное руководство MySQL 5.1 :: 12.2.4.3 Синтаксис INSERT ... ON DUPLICATE KEY UPDATE
- ^ Справочное руководство MySQL 5.1: 11.2.6 Синтаксис ЗАМЕНИТЬ
- ^ «Справочное руководство MySQL 5.5 :: 13.2.5 Синтаксис INSERT» . Проверено 29 октября 2013 г.
- ^ «SQL в понимании SQLite: INSERT» . Проверено 27 сентября 2012 г.
- ^ Страница вставки PostgreSQL
- ^ «Новый КУБРИД 9.0.0» . Официальный блог КУБРИДа. 30 октября 2012 г. Проверено 8 ноября 2012 г.
- ^ CUBRID :: Заявления о манипулировании данными :: Вставка :: Предложение ON DUPLICATE KEY UPDATE
- ^ CUBRID :: Заявления о манипулировании данными :: Заменить
- ^ «УВЕЛИЧИТЬ ЗНАЧЕНИЯ» .
- ^ «UPSERT ВЫБОР» .
- ^ «MERGE INTO (Delta Lake на Databricks)» .
- ^ «Заявление UPSERT (документация Apache Impala)» .
- Сюй, Лео; Обе, Регина (18 мая 2008 г.). «Перекрестное сравнение SQL Server, MySQL и PostgreSQL» . Интернет-журнал Postgres . Проверено 8 октября 2010 г.
- Ходороу, Кристина; Майк Дирольф (сентябрь 2010 г.). MongoDB: Полное руководство . О'Рейли . ISBN 978-1-449-38156-1 .