Обработка дубликатов на этапе создания записи

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

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

Обсуждение
По умолчанию 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 предлагает два решения задачи обработки дубликатов, каждое из которых состоит из единственного предложения:

• Используйте предложение 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 больше подходит для таблиц, в которых может потребоваться обновление других столбцов, не входящих в ключ. Предположим, что у вас есть таблица passtbl, используемая в веб-приложении для хранения адресов электронной почты и паролей, в которой адрес является ключом:

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

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

• Запустить SELECT, чтобы проверить, существует ли уже запись с указанным значением email.

• Если такой записи нет, добавить новую при помощи INSERT.

• Если запись существует, обновить ее при помощи UPDATE.

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

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

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

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

Для операций пакетной загрузки, в которых предложение LOAD DATA используется для загрузки набора записей из файла в таблицу, обработку дубликатов можно регулировать модификаторами IGNORE и REPLACE предложения. Они обеспечивают поведение, аналогичное использованию предложений INSERT IGNORE и REPLACE.

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Подсчет и выявление дубликатов
Предотвращение появления дубликатов в таблице
Удаление дубликатов из таблицы
Устранение дубликатов из результата запроса
Устранение дубликатов из результата самообъединения