UPSERT
1. Синтаксис
2.Описание
Оператор UPSERT добавляется к оператору INSERT и заставляет его вести себя как UPDATE или как ничего не делать, если бы вставка нарушила ограничение уникальности. UPSERT не является стандартным SQL. UPSERT в SQLite следует синтаксису, установленному PostgreSQL, с обобщениями.
UPSERT — это обычное предложение INSERT, за которым следует одно или несколько предложений ON CONFLICT, как показано на диаграмме синтаксиса.
Синтаксис между ключевыми словами «ON CONFLICT» и «DO» называется «целью конфликта». Цель конфликта указывает ограничение уникальности, которое будет триггерить upsert. Цель конфликта может быть опущена в последнем предложении ON CONFLICT в операторе INSERT, но она обязательна для всех остальных предложений ON CONFLICT.
Если операция вставки приведет к нарушению ограничения уникальности в целевом конфликте, то вставка пропускается, и вместо этого выполняется соответствующая операция DO NOTHING или DO UPDATE. Предложения ON CONFLICT проверяются в указанном порядке. Если в последнем предложении ON CONFLICT опущена цель конфликта, то оно будет выполнено, если какое-либо ограничение уникальности будет нарушено, которое не отслеживается предыдущими предложениями ON CONFLICT.
Для каждой строки вставки может быть выполнено только одно предложение ON CONFLICT, а именно первое предложение ON CONFLICT с соответствующей целью конфликта. Когда срабатывает предложение ON CONFLICT, все последующие предложения ON CONFLICT пропускаются для этой строки.
В случае многострочной вставки решение upsert принимается раздельно для каждой строки вставки.
Обработка UPSERT выполняется только для ограничений уникальности. «Ограничение уникальности» — это явное ограничение UNIQUE или PRIMARY KEY в операторе CREATE TABLE или уникальный индекс unique index. UPSERT не вмешивается в случае нарушения ограничений NOT NULL, CHECK или внешних ключей, а также ограничений, реализованных с помощью триггеров.
Имена столбцов в выражениях DO UPDATE относятся к исходному неизменённому значению столбца до попытки вставки. Чтобы использовать значение, которое было бы вставлено, если бы ограничение не было нарушено, добавьте специальный квалификатор таблицы «excluded.» к имени столбца.
2.1.Примеры
Некоторые примеры помогут проиллюстрировать работу UPSERT:
CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
INSERT INTO vocabulary(word) VALUES('jovial')
ON CONFLICT(word) DO UPDATE SET count=count+1;
Вышеупомянутая upsert вставляет новое слово «jovial» в словарь, если это слово ещё не существует в нём, или, если оно уже существует, увеличивает счётчик. Выражение «count+1» также можно записать как «vocabulary.count». PostgreSQL требует второй формы, но SQLite принимает любую из них.
CREATE TABLE phonebook(name TEXT PRIMARY KEY, phonenumber TEXT);
INSERT INTO phonebook(name,phonenumber) VALUES('Alice','704-555-1212')
ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber;
Во втором примере выражение в предложении DO UPDATE имеет вид «excluded.phonenumber». Префикс «excluded.» заставляет «phonenumber» ссылаться на значение для phonenumber, которое было бы вставлено, если бы не было конфликта. Таким образом, эффект upsert заключается в вставке номера телефона Алисы, если он не существует, или в перезаписи любого предыдущего номера телефона для Алисы новым.
Обратите внимание, что предложение DO UPDATE действует только на строку, которая столкнулась с ошибкой ограничения во время вставки. Не нужно включать предложение WHERE, которое ограничивает действие только этой строкой. Единственное использование предложения WHERE в конце DO UPDATE — это необязательно сделать DO UPDATE ничем не делящимся, в зависимости от исходных и/или новых значений. Например:
CREATE TABLE phonebook2(
name TEXT PRIMARY KEY,
phonenumber TEXT,
validDate DATE
);
INSERT INTO phonebook2(name,phonenumber,validDate)
VALUES('Alice','704-555-1212','2018-05-08')
ON CONFLICT(name) DO UPDATE SET
phonenumber=excluded.phonenumber,
validDate=excluded.validDate
WHERE excluded.validDate>phonebook2.validDate;
В этом последнем примере запись phonebook2 обновляется только в том случае, если validDate для нового вставленного значения новее, чем запись, уже существующая в таблице. Если таблица уже содержит запись с тем же именем и текущей validDate, то предложение WHERE заставляет DO UPDATE стать пустой операцией.
2.2.Неясность синтаксического анализа
Когда оператор INSERT, к которому прикреплен UPSERT, получает свои значения из оператора SELECT, существует потенциальная неясность синтаксического анализа. Парсер может не определить, вводит ли ключевое слово «ON» UPSERT или является ли оно предложением ON для соединения. Чтобы обойти эту проблему, предложение SELECT всегда должно включать предложение WHERE, даже если это просто «WHERE true».
Неоднозначное использование ON:
INSERT INTO t1 SELECT * FROM t2 ON CONFLICT(x) DO UPDATE SET y=excluded.y;
Неясность, разрешённая с помощью предложения WHERE:
INSERT INTO t1 SELECT * FROM t2 WHERE true ON CONFLICT(x) DO UPDATE SET y=excluded.y;
3.Ограничения
UPSERT в настоящее время не работает для виртуальных таблиц.
Алгоритм разрешения конфликтов для операции обновления в предложении DO UPDATE всегда ABORT. Другими словами, поведение такое, как если бы предложение DO UPDATE фактически было написано как «DO UPDATE OR ABORT». Если предложение DO UPDATE обнаружит какое-либо нарушение ограничения, весь оператор INSERT откатывается и останавливается. Это верно даже если предложение DO UPDATE содержится внутри оператора INSERT или триггера, который указывает другой алгоритм разрешения конфликтов.
4.История
Синтаксис UPSERT был добавлен в SQLite с версией 3.24.0 (2018-06-04). Исходная реализация тесно следовала синтаксису PostgreSQL, так как она допускала только одно предложение ON CONFLICT и требовала цель конфликта для ON DO UPDATE. Синтаксис был обобщён для разрешения нескольких предложений ON CONFLICT и допускал разрешение DO UPDATE без цели конфликта в версии SQLite 3.35.0 (2021-03-12).
Последнее изменение этой страницы 2024-04-11 23:26:09 UTC
SQLite is in the Public Domain.
https://sqlite.org/lang_upsert.html
