Использование команды UPSERT в MySQL

В MySQL команда UPSERT реализуется с помощью конструкции INSERT ... ON DUPLICATE KEY UPDATE. Эта команда позволяет вставить новую запись в таблицу или обновить существующую, если возникает конфликт с уникальным ключом.

Синтаксис

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...;

Принцип работы

1. Вставка: Команда пытается вставить новую строку с указанными значениями.

2. Обновление: Если строка с уникальным или первичным ключом, указанным в VALUES, уже существует, вместо вставки произойдет обновление существующей строки на основе выражений в ON DUPLICATE KEY UPDATE.

Примеры использования

Простая вставка или обновление

INSERT INTO employees (employee_id, name, salary)
VALUES (1, 'John Doe', 75000)
ON DUPLICATE KEY UPDATE name = VALUES(name), salary = VALUES(salary);

• Если запись с employee_id = 1 не существует, она будет вставлена с name = 'John Doe' и salary = 75000.

• Если запись с employee_id = 1 уже существует, она будет обновлена, и новые значения name и salary будут установлены.

Обновление с выражениями

INSERT INTO inventory (item_id, quantity)
VALUES (1001, 50)
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);

• Если запись с item_id = 1001 существует, quantity будет увеличена на значение, указанное в VALUES(quantity) (в этом случае, на 50).

• Если запись с item_id = 1001 не существует, она будет вставлена с quantity = 50.

Использование функций в обновлении

INSERT INTO products (product_id, stock)
VALUES (10, 100)
ON DUPLICATE KEY UPDATE stock = stock - 10;

• Если запись с product_id = 10 существует, stock будет уменьшен на 10.

• Если запись с product_id = 10 не существует, она будет вставлена с stock = 100.

Особенности

• Уникальные ключи: Для того чтобы ON DUPLICATE KEY UPDATE работала, столбцы, по которым происходит конфликт, должны иметь уникальные ограничения (уникальный индекс или первичный ключ).

• Производительность: Команда может быть менее производительной по сравнению с простыми вставками, так как требуется дополнительная проверка на наличие конфликтов.

• Поддержка: INSERT ... ON DUPLICATE KEY UPDATE поддерживается в MySQL начиная с версии 5.1.

Ограничения

• Нет поддержки в некоторых СУБД: Эта команда специфична для MySQL и не является частью стандартного SQL. В других системах баз данных могут использоваться различные методы для достижения аналогичного функционала, такие как MERGE в SQL Server или INSERT ... ON CONFLICT в PostgreSQL.

Команда UPSERT в MySQL предоставляет мощный инструмент для управления данными, позволяя вам автоматически обновлять существующие записи или вставлять новые в зависимости от условий конфликта.