Реализация UPSERT в MySQL через ON DUPLICATE KEY UPDATE

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

Синтаксис INSERT ... ON DUPLICATE KEY UPDATE

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

• table_name: Имя таблицы, в которую вы вставляете данные.

• column1, column2, ...: Имена столбцов, в которые будут вставлены данные.

• value1, value2, ...: Значения для вставки.

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

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

1. Простой пример

Предположим, у нас есть таблица employees с полями employee_id (первичный ключ), name и salary.

-- Создание таблицы
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- Вставка новой записи или обновление, если запись с таким же employee_id уже существует
INSERT INTO employees (employee_id, name, salary)
VALUES (1, 'John Doe', 60000)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    salary = VALUES(salary);

В этом примере:

• Если запись с employee_id = 1 не существует, она будет вставлена.

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

2. Обновление только некоторых полей

Можно обновлять только определенные поля при конфликте.

-- Вставка новой записи или обновление только salary, если запись с таким же employee_id уже существует
INSERT INTO employees (employee_id, name, salary)
VALUES (1, 'John Doe', 60000)
ON DUPLICATE KEY UPDATE
    salary = VALUES(salary);

Здесь, если запись с employee_id = 1 уже существует, обновляется только поле salary.

3. Использование выражений в обновлении

Можно использовать выражения и функции в части ON DUPLICATE KEY UPDATE.

-- Вставка новой записи или обновление salary с увеличением на 5000, если запись с таким же employee_id уже существует
INSERT INTO employees (employee_id, name, salary)
VALUES (1, 'John Doe', 60000)
ON DUPLICATE KEY UPDATE
    salary = salary + 5000;

В этом примере, если запись с employee_id = 1 уже существует, salary увеличивается на 5000.

4. Вставка и обновление с несколькими значениями

Можно вставлять и обновлять несколько записей за один запрос.

-- Вставка нескольких записей или обновление, если запись с таким же employee_id уже существует
INSERT INTO employees (employee_id, name, salary)
VALUES 
    (1, 'John Doe', 60000),
    (2, 'Jane Smith', 70000),
    (3, 'Emily Jones', 75000)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    salary = VALUES(salary);

Здесь будут вставлены или обновлены все записи. Если employee_id уже существует, запись будет обновлена.

Заключение

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