Оптимизация вставки и обновления строк MySQL: ON DUPLICATE KEY UPDATE

Быстрый ответ

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

INSERT INTO table_name (col1, col2) VALUES
  (val1_1, val1_2),
  (val2_1, val2_2),
  ...
ON DUPLICATE KEY UPDATE 
  col1 = VALUES(col1), 
  col2 = VALUES(col2);

Такой подход позволяет обновлять столбцы col1 и col2 на соответствующие новые значения из списка VALUES, если ключи совпадают.

Мощь ON DUPLICATE KEY UPDATE

Возможности ON DUPLICATE KEY UPDATE незаменимы при массовых вставках. Инструкция эффективно совмещает в себе функциональность операторов INSERT и UPDATE, снижая нагрузку на базу данных, уменьшая количество сетевых запросов и упрощая логику ввода на уровне приложения.

# SQL "два-в-одном" 🎉
INSERT INTO table_name (col1, col2) VALUES 
  (val1_1, val1_2),
  (val2_1, val2_2),
  ...
ON DUPLICATE KEY UPDATE 
  col1 = VALUES(col1), 
  col2 = VALUES(col2);

С версией MySQL 8.0.19 VALUES(col1) можно заменить на VALUE, что делает запрос более читаемым. Ключевое слово VALUE указывает на значения, предназначенные для обновления.

Особую внимательность следует уделить столбцам с не первичными уникальными ключами: важно глубоко понимать уникальные ограничения, чтобы предотвратить нежелательные перезаписи. В целом, благодаря MySQL, ваша работа с массовыми операциями будет значительно эффективнее.

# Время для UPSERT 🚀
INSERT INTO table_name (id, col1, col2) VALUES 
  (1, 'A', 'X'),
  (2, 'B', 'Y'),
  ...
ON DUPLICATE KEY UPDATE 
  col1 = VALUES(col1), 
  col2 = VALUES(col2);

Альтернатива в SQL Server

SQL Server предлагает альтернативный подход: использование временных таблиц и команду MERGE. Как и ON DUPLICATE KEY UPDATE, MERGE позволяет обновлять существующие записи или вставлять новые в зависимости от условий совпадения.

# Привет от SQL Server для MySQL! 👋
MERGE INTO target_table AS t
USING source_table AS s
ON t.key_col = s.key_col
WHEN MATCHED THEN 
  UPDATE SET t.col1 = s.col1, t.col2 = s.col2
WHEN NOT MATCHED BY TARGET THEN
  INSERT (key_col, col1, col2)
  VALUES (s.key_col, s.col1, s.col2);

Увеличение производительности без снижения безопасности

Охраняйтесь от SQL-инъекций, используя плейсхолдеры (?) и передавая параметры в виде массивов. Эffectively increases безопасность при массовых операциях с данными.

В JavaScript-среде использование async/await и Promises делает асинхронные операции с базой данных высокопроизводительными и упрощает обработку ошибок. Стрелочные функции делают код кратким и лаконичным.

Работа с десятичными значениями

Для корректной работы с десятичными значениями и сохранения их точности при транзакциях используйте символ обратной кавычки (`):

# Мы сохраняем точность до копейки 💰
INSERT INTO financials (account, amount) VALUES
  (`123456`, `25.75`),
  (`654321`, `100.50`),
  ...
ON DUPLICATE KEY UPDATE 
  amount = VALUES(amount);

Визуализация

Давайте рассмотрим пример:

🛒 Корзина покупок: [🍎, 🍞, 🧀, 🥦]

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

🛍️ Каждое сканирование товара:
- Добавляет товар, если его нет в чеке
- Обновляет цену, если есть скидочный купон

В таком контексте ON DUPLICATE KEY UPDATE в MySQL работает следующим образом:

INSERT INTO bill (item, price) VALUES
  ('🍎', 1.00), ('🍞', 2.00), ('🧀', 3.00)
ON DUPLICATE KEY UPDATE price=VALUES(price);

Процесс сканирования на кассе:

• Добавляет товар, если он еще не был добавлен 🆕, или
• Обновляет цену, если товар уже был отсканирован ранее 🔁

До: 🧾 Чек: {🍎: 1.50, 🍞: 2.00}
После: 🧾 Чек: {🍎: 1.00, 🍞: 2.00, 🧀: 3.00}

Такой подход обеспечивает быструю и плавную работу.

Полезные материалы

1. MySQL :: Руководство по MySQL 8.0 :: 13.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE — официальная документация MySQL по "INSERT ON DUPLICATE KEY UPDATE".
2. sql – Вставка или обновление записи в MySQL, если она существует – Stack Overflow — возможности UPSERT в MySQL на практике.
3. mysql – "INSERT IGNORE" против "INSERT ... ON DUPLICATE KEY UPDATE" – Stack Overflow — разбор отличий между "INSERT IGNORE" и "INSERT ... ON DUPLICATE KEY UPDATE".
4. sql – Обновление сразу нескольких строк в MySQL – Stack Overflow — стратегии массового обновления строк в MySQL.
5. INSERT ON DUPLICATE KEY UPDATE – База знаний MariaDB — подробное описание и примеры использования, применимые также для MySQL.