MySQL – Kako ukloniti duplikate

Ovaj post je donekle inspirisan problemom koji je bluesman opisao na svom blogu. Dva problema koji nemaju veze jedan sa drugim, ali odlično pokazuju zašto poslodavci insistiraju na x godina iskustva (gde x > 2) 🙂

Naime, danas sam “u minut do dvanaest” dobio fajl sa par stotina hiljada keyworda koje treba procesirati ASAP (oh kako volim ovu reč). Naravno, u žurbi nisam mnogo gledao u fajl, provalio sam da je format uobičajen (1 keyword po liniji), importovao u bazu sa load data local infile … , startovao procesiranje i to bi bilo to.

Par minuta nakon toga startuje me kolega koji je zaboravio da mi kaže da se kod nekih keyworda “potkralo” par domena tipa “foo.eu” gde ono “.eu” samo treba izbaciti. Sve je to lepo, ali svi su već importovani u tabelu i samo što nisu pokupljeni. Rešen da brzo delam, odlučim da je najjednostavnije odraditi “search/replace hack” direktno u tabeli, ali …

mysql> update odm_master_keywords 
       set keyword = replace(keyword, '.eu','');
ERROR 1062 (23000): Duplicate entry 'academia' for key 2

Shit. Posto je keyword polje unique, neki od tih “.eu” je nakon search/replace-a napravio problem sa istim identičnim keywordom. Ok, obrisaću ručno te duplikate, valjda ih nema mnogo …

mysql> delete from odm_master_keywords_cl where keyword='academia';
Query OK, 1 row affected (0.02 sec)

mysql> update odm_master_keywords set keyword = replace(keyword, '.eu','');
ERROR 1062 (23000): Duplicate entry 'academic' for key 2
mysql> delete from odm_master_keywords_cl where keyword='academic';
Query OK, 1 row affected (0.02 sec)

mysql> update odm_master_keywords set keyword = replace(keyword, '.eu','');
ERROR 1062 (23000): Duplicate entry 'actriz' for key 2
mysql> delete from odm_master_keywords where keyword='actriz';
Query OK, 1 row affected (0.02 sec)

mysql> update odm_master_keywords set keyword = replace(keyword, '.eu','');
ERROR 1062 (23000): Duplicate entry 'foo' for key 2
mysql> delete from odm_master_keywords where keyword='foo';
Query OK, 1 row affected (0.48 sec)
...

I tako, mogao bih ovako do prekosutra … Mora da postoji nešto pametnije 🙂

Nakon kraćeg razmišljanja, došao sam do ovog rešenja. Privremeno skloniti “unique” index, odraditi update i vratiti index natrag, ali sa ignore opcijom koja će “ubiti” višak duplikata. Dakle:

mysql> alter table odm_master_keywords drop key keyword;
Query OK, 338565 rows affected (13.79 sec)
Records: 338565  Duplicates: 0  Warnings: 0

mysql> update odm_master_keywords set keyword = replace(keyword, '.eu','');
Query OK, 378 rows affected (4.95 sec)
Rows matched: 338565  Changed: 378  Warnings: 0

mysql> alter ignore table odm_master_keywords add unique keyword(keyword);
Query OK, 338565 rows affected (46.45 sec)
Records: 338565  Duplicates: 233  Warnings: 0

Obratite pažnju na ignore deo u poslednjem alteru. On je ključan ovde, jer da ga nismo koristili ponovo bi došlo do greške(duplicate entry …) prilikom update-a.

Inače, poslednja dva query-a su uobičajen “pattern” kada se želite osloboditi duplikata u nekom polju – jednostavno dodajte unique index na polje i višak će biti obrisan iz tabele.