Сегодня возникла проблема дублирования строк в базе 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, создание нового пользователя и изменение пароля существующего, обрабатывались бы по-разному. Стандартный алгоритм мог бы быть таким:
- Запустить SELECT, чтобы проверить, существует ли уже запись с указанным значением email.
- Если такой записи нет, добавить новую при помощи INSERT.
- Если запись существует, обновить ее при помощи UPDATE.
Все это можно выполнить внутри транзакции или заблокировав таблицы, чтобы запретить другим пользователям изменять таблицы в течение того времени, пока вы с ними работаете. Применив REPLACE, вы можете свести оба случая к одному предложению:
REPLACE INTO users (email,password) VALUES(адрес,пароль);
Если запись с указанным адресом электронной почты не существует, то MySQL создает новую. Если запись существует, MySQL заменяет ее. В результате обновляется столбец password записи, содержащей данный адрес.
Источник: https://oooportal.ru/?cat=arti...
