MySQL UPSERT: Вставка или обновление при дублировании ключа

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

Содержание

• Основное объяснение синтаксиса
• Современный синтаксис с псевдонимами строк
• Практические примеры
• Внутреннее устройство
• Вопросы производительности
• Альтернативные подходы

Основное объяснение синтаксиса

Клауза INSERT ... ON DUPLICATE KEY UPDATE является расширением стандартного оператора INSERT в MySQL. Когда вы пытаетесь вставить строку и происходит нарушение ограничения уникального ключа (для PRIMARY или UNIQUE ключа), MySQL вместо этого выполняет UPDATE существующей строки.

Базовая структура синтаксиса:

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

Функция VALUES(column_name) ссылается на значение, которое было изначально указано для этого столбца в операторе INSERT. Это гарантирует, что вы обновляете с использованием предполагаемых новых значений.

Согласно официальной документации MySQL, “INSERT … ON DUPLICATE KEY UPDATE - это расширение MySQL для оператора INSERT, которое, если находит дубликат уникального или первичного ключа, вместо этого выполняет UPDATE.”

Современный синтаксис с псевдонимами строк

Начиная с MySQL 8.0.19, вы можете использовать псевдонимы строк для доступа к новым значениям столбцов более чистым и читаемым способом. Этот современный подход настоятельно рекомендуется для новых приложений.

Синтаксис позволяет указать псевдоним для вставляемой строки, что упрощает ссылку на новые значения в клаузе UPDATE:

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

Вы также можете указать псевдонимы столбцов:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3)
AS new_row(col1, col2, col3) ON DUPLICATE KEY UPDATE
    column1 = col1,
    column2 = col2,
    column3 = col3;

Как показано в примерах документации MySQL, этот подход позволяет выполнять более сложные операции:

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new ON DUPLICATE KEY UPDATE c = new.a+new.b;

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

Практические примеры

Применим это к вашему конкретному примеру. Предположим, у вас есть таблица следующей структуры:

CREATE TABLE users (
    ID INT PRIMARY KEY,
    NAME VARCHAR(50),
    AGE INT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Базовый пример

Чтобы вставить нового пользователя или обновить существующего по ID:

INSERT INTO users (ID, NAME, AGE)
VALUES(1, "Alice", 25)
ON DUPLICATE KEY UPDATE 
    NAME = VALUES(NAME),
    AGE = VALUES(AGE);

Пример с современным синтаксисом

Использование псевдонимов строк для лучшей читаемости:

INSERT INTO users (ID, NAME, AGE)
VALUES(1, "Alice", 25)
AS user_data ON DUPLICATE KEY UPDATE
    NAME = user_data.NAME,
    AGE = user_data.age;

Пример с несколькими строками

Оператор также работает с вставкой нескольких строк:

INSERT INTO users (ID, NAME, AGE)
VALUES
    (1, "Alice", 25),
    (2, "Bob", 30),
    (3, "Charlie", 35)
AS user_data ON DUPLICATE KEY UPDATE
    NAME = user_data.NAME,
    AGE = user_data.age;

Условные обновления

Вы можете добавлять условия к вашим обновлениям:

INSERT INTO users (ID, NAME, AGE)
VALUES(1, "Alice", 25)
AS user_data ON DUPLICATE KEY UPDATE
    NAME = IF(user_data.NAME != "", user_data.NAME, NAME),
    AGE = user_data.age;

В этом примере показано, как обновлять только если новое имя не пустое, в противном случае сохранять существующее имя.

Внутреннее устройство

При выполнении оператора INSERT ... ON DUPLICATE KEY UPDATE MySQL следует этому процессу:

1. Попытка вставки: MySQL сначала пытается вставить новую строку в таблицу.

2. Проверка на дубликаты: Если вставка не удается из-за ограничения уникального ключа на PRIMARY или UNIQUE ключах:

3. Вместо ошибки: MySQL не возвращает ошибку. Вместо этого он:

   • Идентифицирует существующую строку с дублирующимся ключом
   • Выполняет оператор UPDATE, указанный в клаузе ON DUPLICATE KEY UPDATE
   • Возвращает количество затронутых строк (1 для обновления, 0 если обновление не произошло)

4. Возврат информации: Оператор возвращает:

   • Количество затронутых строк (1 для вставки/обновления, 0 если изменений не было)
   • Для MySQL 8.0+: Информацию о том, была ли это вставка или обновление

Важное замечание: Как указано в документации MySQL 5.7, “Для таблицы InnoDB, где a - это столбец автоинкремента, оператор INSERT увеличивает значение автоинкремента, но UPDATE не делает этого.” Это может иметь последствия для логики вашего приложения.

Вопросы производительности

При использовании INSERT ... ON DUPLICATE KEY UPDATE учтите эти аспекты производительности:

Поведение блокировок

• Блокировка на уровне строк: InnoDB использует блокировки на уровне строк, что обычно эффективно
• Продолжительность блокировки: Блокировка удерживается в течение как попытки вставки, так и потенциального обновления
• Взаимоблокировки: Будьте осторожны с возможными взаимоблокировками в средах с одновременным доступом

Использование индексов

• Ограничение уникального ключа должно быть правильно проиндексировано для оптимальной производительности
• MySQL будет использовать тот же индекс, который он использовал бы для проверки дубликатов ключей
• Убедитесь, что ваши первичные/уникальные ключи имеют соответствующие индексы

Операции пакетной обработки

• Оператор эффективно работает с вставкой нескольких строк
• Каждая строка обрабатывается индивидуально для проверки дубликатов ключей
• Рассмотрите возможность пакетной обработки для большей пропускной способности

Влияние транзакций

• Вся операция (попытка вставки + потенциальное обновление) происходит в рамках одной транзакции
• Это обеспечивает гарантии атомарности для вашей операции upsert

Согласно Руководству данных Prisma, этот подход “обычно называется операцией ‘upsert’” и является стандартной моделью в базах данных приложений.

Альтернативные подходы

Хотя INSERT ... ON DUPLICATE KEY UPDATE является стандартным подходом MySQL, существуют альтернативные варианты, которые стоит рассмотреть:

Оператор REPLACE

Оператор REPLACE сначала удаляет существующую строку, а затем вставляет новую:

REPLACE INTO users (ID, NAME, AGE) VALUES(1, "Alice", 25);

Плюсы: Простой синтаксис
Минусы:

• Удаляет и воссоздает строку (триггеры срабатывают снова)
• Значения автоинкремента затрагиваются
• Менее эффективно, чем ON DUPLICATE KEY UPDATE

Сохраненные процедуры

Вы можете создавать сохраненные процедуры для обработки логики:

CREATE PROCEDURE upsert_user(IN p_id INT, IN p_name VARCHAR(50), IN p_age INT)
BEGIN
    INSERT INTO users (ID, NAME, AGE) VALUES(p_id, p_name, p_age)
    ON DUPLICATE KEY UPDATE 
        NAME = p_name,
        AGE = p_age;
END;

Логика приложения

В коде вашего приложения:

START TRANSACTION;
SELECT COUNT(*) FROM users WHERE ID = 1 FOR UPDATE;
-- Если count = 0, INSERT
-- Если count > 0, UPDATE
COMMIT;

Плюсы: Полный контроль над логикой
Минусы: Более сложный, требует управления транзакциями

Сравнение с INSERT IGNORE

Как вы упоминали, INSERT IGNORE предотвращает ошибки, но не обновляет существующие строки. Он просто полностью пропускает дублирующиеся строки.

INSERT IGNORE INTO users (ID, NAME, AGE) VALUES(1, "Alice", 25);

Это полезно, когда вы хотите вставлять только новые записи и пропускать дубликаты, но не когда вам нужно обновлять существующие записи.

Источники

1. MySQL 8.4 Справочное руководство - INSERT … ON DUPLICATE KEY UPDATE
2. MySQL 8.0 Справочное руководство - INSERT … ON DUPLICATE KEY UPDATE
3. MySQL 5.7 Справочное руководство - INSERT … ON DUPLICATE KEY UPDATE
4. Руководство данных Prisma - ON DUPLICATE KEY UPDATE
5. Оператор MySQL INSERT ON DUPLICATE KEY UPDATE
6. Понимание MySQL INSERT ON DUPLICATE UPDATE OR UPSERT
7. MySQL UPSERT объяснено с примерами

Заключение

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

Для вашего конкретного случая использования рекомендуется следующий синтаксис:

INSERT INTO table_name (ID, NAME, AGE)
VALUES(1, "Alice", 25)
AS new_data ON DUPLICATE KEY UPDATE
    NAME = new_data.NAME,
    AGE = new_data.age;

Этот подход предлагает несколько преимуществ:

• Атомарность: Операция выполняется в одном операторе
• Производительность: Эффективно при правильном индексировании
• Читаемость: Современный синтаксис с псевдонимами строк делает код более понятным
• Гибкость: Поддерживает сложные условия обновления и операции пакетной обработки

При реализации операций upsert в ваших приложениях учитывайте:

• Использование псевдонимов строк (MySQL 8.0.19+) для лучшей поддерживаемости кода
• Правильное индексирование столбцов ваших уникальных ключей
• Уровни изоляции транзакций для одновременного доступа
• Альтернативные подходы, такие как REPLACE или сохраненные процедуры для конкретных случаев использования

Эта модель является фундаментальной для многих приложений баз данных и стоит изучить для эффективного управления данными в MySQL.