Как вставить или обновить множество данных в MySQL?

Допустим, у вас есть таблица с некоторыми данными, скажем, пользовательской информацией с адресами электронной почты, именами, возрастом и т.д. Вы хотите вставить еще 10 строк данных, но не знаете, будут они предназначены для новых пользователей или это будут атрибуты для пользователей, уже имеющихся в базе данных. Это очень распространенный сценарий, когда вам нужно понять, что делать – обновлять строки (update) или вставлять их (insert)…

Вступление

Неоптимальный вариант – создать цикл и для каждой строки проверять существование адреса электронной почты (считая его уникальным критерием для пользователя), а затем вставлять или обновлять строку. Хотелось бы, чтобы все это делала  сама база данных в рамках одного запроса. У вас есть выбор между опциями REPLACE INTO или INSERT on DUPLICATE KEY UPDATE. REPLACE INTO кажется великолепным решением, с учетом того, что запрос имеет такой же синтаксис, как и обычный INSERT INTO. Но, вероятно, следует избегать использования этого оператора, так как фактически он выполняет операции DELETE и INSERT и обновляет все индексы.

Таким образом, INSERT on DUPLICATE KEY UPDATE – это, вероятнее всего, наиболее оптимальный  запрос, но как его использовать для нескольких строк? И как это сделать для нескольких критериев уникальности? Возможно, мы захотим получить, чтобы в таблице были одинаковые адреса электронной почты, так как они принадлежат разным спискам или разным пользователям (находятся в разных столбцах таблицы). Так, адрес электронной почты сам по себе не является единственным уникальным критерием для пользователя, а уникальный критерий основан на нескольких столбцах.

Реализация (MySQL)

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

Обратите внимание, что один и тот же пользователь (Витя) вставлен дважды, но мы хотим, чтобы он принадлежал разным спискам (listid). Теперь, используя INSERT on DUPLICATE KEY UPDATE, необходимо указать критерии, по которым база данных будет выполнять проверку, чтобы обновить строку или вставить в нее информацию. Нужен уникальный ключ – MySQL позволяет указать несколько столбцов с помощью составного ключа, уникально идентифицирующего встречающиеся записи. Таким образом, мы либо изменим таблицу выше и добавим первичный составной ключ, либо зададим его сначала:

Теперь наша таблица готова для массовой вставки ON DUPLICATE KEY UPDATE. Ниже приведен пример:

При запуске вставляются 3 новые первые строки, а последняя строка обновляется. На этом все, возможно моя статья будет кому-нибудь полезной.

Подписаться на новые статьи