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

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

Вступление

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

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

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

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

CREATE TABLE users (
  id int(20) NOT NULL AUTO_INCREMENT,
  listid INT,
  email VARCHAR(50),
  age INT,
  name VARCHAR(150),
);

insert into users (listid,email,age,name) values 
(1,'vit9@gmail.com',22,"Витя"),
(1,'masha@gmail.com',24,"Маша"),
(1,'pet9@gmail.com',22,"Петя"),
(2,'vit9@gmail.com',22,"Витя");

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

//удалить старый первичный ключ, если он существует: ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (email, listid);

//или создать таблицу с составным первичным ключом сначала
CREATE TABLE users (
  id int(20) NOT NULL AUTO_INCREMENT,
  listid INT,
  email VARCHAR(50),
  age INT,
  name VARCHAR(150),
  PRIMARY KEY (email, listid)
);

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

//INSERT INTO users (val1, val2, val3, val4) VALUES (1, 2, 3, 4) 
//ON DUPLICATE KEY UPDATE val1=1, val2=2, val3=3, val4=4;

INSERT INTO users (listid,email,age,name) VALUES 
(3,'vit9@gmail.com',26,"Витя"),
(3,'kat9@gmail.com',32,"Катя"),
(3,'masha@gmail.com',29,"Маша"),
(2,'vit9@gmail.com',25,"Витя"),
ON DUPLICATE KEY UPDATE listid=VALUES(listid), 
email=VALUES(email), 
age=VALUES(age), 
name=VALUES(name);

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

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