Обработка на дубликати на етапа на създаване на запис
ЗадачаСъздали сте таблица с уникален индекс за предотвратяване на дублиращи се стойности в индексирана колона или колони. Но сега, когато се опитате да вмъкнете запис с дублирана стойност, се генерира грешка и бихте искали да избегнете обработката на такива грешки.
РешениеЕдиният начин е просто да игнорирате грешката, другият е да използвате клаузата INSERT IGNORE или REPLACE, като и двете променят поведението на MySQL по отношение на обработката на повторен опит. За операция за пакетно зареждане клаузата LOAD DATA има модификатори, за да уточни как се обработват дубликати.
ДискусияПо подразбиране MySQL генерира грешка при вмъкване на запис, който дублира съществуващ уникален ключ. Например, ако таблицата с лица съдържа уникален индекс на колоните last_name и first_name, тогава ще видите следното:
mysql> INSERT INTO лице (фамилия, собствено име) -> VALUES('X1','Y1'); Заявката е ОК, 1 засегнат ред (0,00 сек) mysql> INSERT INTO лице (фамилия, собствено име) -> VALUES('X1','Y1'); ГРЕШКА 1062 на ред 1: Дублиран запис „X1-Y1“ за ключ 1
Ако изпълнявате изрази интерактивно от програма mysql, можете просто да кажете „Разбрах, че не работи“, игнорирайте грешката и продължете.
Но ако пишете програма, която вмъква записи, грешка може да доведе до нейното прекратяване. Един от начините да избегнете това е да промените поведението на програмата, като хванете грешката и я игнорирате.
Ако искате да предотвратите появата на грешка, вероятно обмисляте да решите проблема с обработката на дубликати с две заявки: изпълнете SELECT, за да определите дали вече има такъв запис, и след това INSERT, ако записът вече не съществува. Но всъщност нищо няма да работи.Друго клиентско приложение може да вмъкне същия запис между вашите SELECT и INSERT и след това отново ще се генерира грешка. За да предотвратите това да се случи, можете да обвиете две изречения в транзакция или да заключите таблици, но тогава вместо две изречения ще имате четири. MySQL предлага две решения на проблема с обработката на дубликати, всяко от които се състои от едно изречение:
• Използвайте клаузата INSERT IGNORE вместо INSERT. Ако записът не е дубликат на съществуващ, тогава MySQL го вмъква както обикновено. Ако записът е дубликат, тогава ключовата дума IGNORE казва на MySQL тихо да го отхвърли, без да генерира грешка:
mysql> INSERT IGNORE INTO човек (фамилия, собствено име) -> VALUES('X2','Y2'); Заявката е ОК, 1 засегнат ред (0,00 сек) mysql> INSERT IGNORE INTO човек (фамилия, собствено име) -> VALUES('X2','Y2'); Заявката е ОК, 0 засегнати реда (0,00 сек)
Стойността на брояча на реда показва дали записът е вмъкнат или игнориран.
В програма можете да получите тази стойност, като използвате функцията за преброяване на редове, предоставена във вашия API.
• Използвайте клаузата REPLACE вместо INSERT. Ако записът е нов, той се вмъква, сякаш е клауза INSERT. Ако е дубликат, тогава новият запис замества стария:
mysql> ЗАМЕНИТЕ В човек (фамилия, име) -> VALUES('X3','Y3'); Заявката е ОК, 1 засегнат ред (0,00 сек) mysql> ЗАМЕНИТЕ В човек (фамилия, име) -> VALUES('X3','Y3'); Заявката е ОК, засегнати са 2 реда (0,00 сек.)
Стойността на броя обработени редове във втория случай е 2, тъй като оригиналният запис е изтрит и на негово място е вмъкнат нов запис.
Изборът на INSERT IGNORE или REPLACE зависи откакво поведение е най-добро за вас. INSERT IGNORE съхранява първия от много дублиращи се записи и премахва останалите. REPLACE запазва последния от дубликатите и премахва всички останали. Клаузата INSERT IGNORE е по-ефективна от REPLACE, защото в таблицата не се вмъкват дубликати. Тоест, по-добре е да го използвате, когато просто искате да сте сигурни, че копие на посочения запис се съдържа в таблицата.
CREATE TABLE passtbl ( имейл CHAR(60) NOT NULL, парола CHAR(20) BINARY NOT NULL, PRIMARY KEY (имейл) );
Как да създам записи за нови потребители и да променя паролите за съществуващите? Без REPLACE създаването на нов потребител и промяната на паролата на съществуващ ще се третира по различен начин. Стандартният алгоритъм може да бъде:
• Изпълнете SELECT, за да видите дали вече съществува запис с дадената имейл стойност.
• Ако няма такъв запис, добавете нов с INSERT.
• Ако записът съществува, актуализирайте го с UPDATE.
Всичко това може да се направи в рамките на транзакция или чрез заключване на таблиците, за да се попречи на други потребители да променят таблиците, докато работите върху тях. С помощта на REPLACE можете да намалите двата случая до едно изречение:
Предимството на INSERT IGNORE и REPLACE е, че те не водят до режийни разходи, каквито би имала транзакция. Но това предимство идва с цената на преносимостта, тъй като и двете предложения са специфични за MySQL. Ако преносимостта е важна за вас, тогава е за предпочитане да използвате транзакция.
За операции за групово зареждане, при които клаузата LOAD DATA се използва за зареждане на набор от записи от файл в таблица, обработката на дубликати може да се контролира от модификаторите на клаузите IGNORE и REPLACE. Теосигурява поведение, подобно на използването на клаузите INSERT IGNORE и REPLACE.