Как избежать дубликатов при добавлении записи в MySQL?

Время чтения: 8 минут
0 142

Сегодня возникла проблема дублирования строк в базе MySQL, потратил много времени на поиски и нашел такую итересную статью. Надеюсь она вам поможет, как и мне. Источник статьи указал в конце страницы.

Задача

Вы создали таблицу с уникальным индексом для предотвращения появления дубликатов значений в индексированном столбце или столбцах. Но теперь при попытке вставки записи с повторяющимся значением генерируется ошибка, а вы хотели бы избежать обработки таких ошибок.

Создать индекс на несколько столбцов можно так:

ALTER TABLE `db_name`.`table_name` ADD UNIQUE `name_index` (`app`, `email`, `other_field`);

Где db_name - имя вашей базы данных, table_name - имя таблицы в базе, name_index - название индекса, `app`, `email`, `other_field` - группируемые поля в уникальный индекс.

Решение

Один из способов – просто игнорировать ошибку, другой – использовать предложение INSERT IGNORE или REPLACE, каждое из которых изменяет поведение MySQL в отношении обработки повторений. 

Обсуждение

По умолчанию MySQL генерирует ошибку при вставке записи, дублирующей существующий уникальный ключ. Например, если таблица person содержит уникальный индекс для столбцов last_name и first_name, то вы увидите следующее:

mysql> INSERT INTO person (last_name, first_name)
-> VALUES('X1','Y1');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO person (last_name, first_name)
-> VALUES('X1','Y1');
ERROR 1062 at line 1: Duplicate entry 'X1-Y1' for key 1

Если вы интерактивно запускаете предложения из программы mysql, то можете просто сказать: «Понял, не сработало», игнорировать ошибку и продолжать работу.

Но если вы пишете программу, вставляющую записи, то ошибка может привести к завершению ее работы. Один из способов избежать этого – изменить поведение программы за счет отлавливания ошибки и ее игнорирования.

Если вы хотите предотвратить появление ошибки, то, вероятно, подумываете о решении задачи обработки дубликатов с помощью двух запросов: запустите SELECT, чтобы определить, есть ли уже такая запись, а затем – INSERT, если записи еще нет. Но на самом деле ничего не получится. Другое клиентское приложение может вставить такую же запись в промежуток между вашими SELECT и INSERT, и тогда опять-таки сгенерируется ошибка. Чтобы это не произошло, можно заключить два предложения в транзакцию или заблокировать таблицы, но тогда вместо двух предложений у вас появится четыре. MySQL предлагает два решения задачи обработки дубликатов, каждое из которых состоит из единственного предложения:

Первый cпособ: Используйте предложение INSERT IGNORE вместо INSERT.

Если запись не дублирует существующую, то MySQL вставляет ее как обычно. Если же запись – это дубликат, то ключевое слово IGNORE указывает MySQL, что следует молча отбросить ее, не генерируя ошибку:

mysql> INSERT IGNORE INTO person (last_name, first_name)
-> VALUES('X2','Y2');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person (last_name, first_name)
-> VALUES('X2','Y2');
Query OK, 0 rows affected (0.00 sec)

Значение счетчика строк показывает, была запись вставлена или проигнорирована.

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

Второй способ: Используйте предложение REPLACE вместо INSERT.

Если запись новая, она вставляется так, как если бы выполнялось предложение INSERT. Если же это дубликат, то новая запись замещает старую:

mysql> REPLACE INTO person (last_name, first_name)
-> VALUES('X3','Y3');
Query OK, 1 row affected (0.00 sec)
mysql> REPLACE INTO person (last_name, first_name)
-> VALUES('X3','Y3');
Query OK, 2 rows affected (0.00 sec)

Значение количества обработанных строк во втором случае равно 2, так как исходная запись удалена, а на ее место вставлена новая запись.

Выбор INSERT IGNORE или REPLACE зависит от того, какое поведение для вас предпочтительно. INSERT IGNORE хранит первую из множества повторяющихся записей и удаляет остальные. REPLACE хранит последний из дубликатов и удаляет все остальные. Предложение INSERT IGNORE эффективнее, чем REPLACE, так как дубликаты не вставляются в таблицу. То есть его лучше применять, когда вы просто хотите убедиться в том, что копия указанной записи содержится в таблице.

С другой стороны, REPLACE больше подходит для таблиц, в которых может потребоваться обновление других столбцов, не входящих в ключ. Предположим, что у вас есть таблица users, используемая в веб-приложении для хранения адресов электронной почты и паролей, в которой email является ключом:

CREATE TABLE users
(
email CHAR(60) NOT NULL,
password CHAR(20) BINARY NOT NULL,
PRIMARY KEY (email)
);

Как создавать записи для новых пользователей и изменять пароли для существующих? Без REPLACE, создание нового пользователя и изменение пароля существующего, обрабатывались бы по-разному. Стандартный алгоритм мог бы быть таким:

  1. Запустить SELECT, чтобы проверить, существует ли уже запись с указанным значением email.
  2. Если такой записи нет, добавить новую при помощи INSERT.
  3. Если запись существует, обновить ее при помощи UPDATE.

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

REPLACE INTO users (email,password) VALUES(адрес,пароль);

Если запись с указанным адресом электронной почты не существует, то MySQL создает новую. Если запись существует, MySQL заменяет ее. В результате обновляется столбец password записи, содержащей данный адрес.

Источник:  https://oooportal.ru/?cat=arti...

« Все статьи

Комментарии 0 комментариев
Только авторизованные пользователи могут оставлять комментарии. Войдите, пожалуйста.
Что еще посмотреть?