MySQL SELECT ALL DUPLICATES, aneb jak nalézt všechny duplicitní řádky
Čas od času by se Vám mohl hodit dotaz, který najde duplicitní názvy. Ať už ve fázi ladění, kdy chcete odstranit duplicity kvůli vazbě na referenční klíče nebo chcete uživateli nabídnout seznam duplicit v databázi pro další úpravy. Zjistit hodnotu, která je duplicitní je celkem snadné, ale co když potřebujeme řádky, které obsahují duplicitní hodnoty.
Duplicitní hodnoty jsou nešvarem všech rozsáhlých databází, myslím tam, kde dosud nebyly nutné unikátní hodnoty a nyní chceme zavést například referenční klíč nebo unikátnost na určitém sloupci.
Běžné je, že několik let spravujeme databázi, kde název článku nebyl unikátní, ale nyní chceme zavést kvůli SEO unikátní název článku, aby v url bylo možné uvádět jen jeho název a vynechat jednoznačný identifikátor. V redakčním systému, který spravuje desítky redaktorů a plní se několik let můžeme s něčím takovým najisto počítat.
Nebo chceme nabídnout administrátorům eshopu, aby měli přehled nad produkty, které mají stejný katalogový kód nebo název produktu, není to stav, který je nejvhodnější alespoň u katalogových kódu, bych se tomu vyhnul, ale není to až zas tak neobvyklé.
Zjistit duplicitní názvy je snadné, inspirovat se přitom můžeme rovnou v phpMyAdminu, je to první ikona při pohledu na strukturu tabulky, viz. následující obrázek.
Malou modifikací phpMyAdmin dotazu získáme jen ty názvy, které se opakují.
- SELECT name FROM clanky GROUP BY name HAVING (count(name)>1)
Tady je právě kámen úrazu. Opakující se názvy jsou seskupeny pomocí GROUP BY a proto nemáme šanci ve výsledku dotazu zjistit jejich jednotlivé identifikátory nebo další vlastnosti jednotlivých duplicitních řádků.
Abychom získali všechny řádky, tedy všechny duplicity, musíme na to jít jinak. Na googlu, lze nalézt několik návodů, ale všechny mají určitě nedostatky. Jsou složeny z vytváření dočasných tabulek, plnění tabulek duplicitami a obvykle se skládají z několika oddělených dotazů do databáze.
Jde například o tyto články:
mysql select all duplicates (google.com)
Counting and Identifying Duplicates (ONLamp.com)
a další.
Jak získat všechny duplicitní řádky? Jako více vyhovující dávám přednost složenému dotazu:
- SELECT * FROM clanky as c JOIN (SELECT name FROM clanky GROUP BY name HAVING (count(name)>1)) as duplicates ON (c.name LIKE duplicates.name)
Snad, by tato metoda mohla být i rychlejší. Už nějakou krátkou dobu tento postup používám v různých složitých dotazech a zatím bez problémů funguje.
komentáře
RSS Komentáře
Kromě rychlého řešení, které navrhuje Karel Dytrych (které se ale špatně používá, pokud kromě ID chceme získat i něco dalšího), je možné ještě jedno:
SELECT * FROM clanky WHERE name IN (SELECT name FROM clanky GROUP BY name HAVING (count(*)>1))
Je to o řád rychlejší než korelovaný poddotaz uvedený v článku.
Špatně jsem se v tom dotazu (přes několik šířek článku) zorientoval. Podmínka není uvnitř poddotazu, takže ten není korelovaný.
U mého dotazu na testovacích datech navíc MySQL z nějakého důvodu nedokáže u vnějšího dotazu použít index, takže by ho bylo potřeba položit v PHP a do MySQL poslat dotazy dva.
odpovídám trochu se spožděním, ale mysql je přece známé že neumí používat v poddotazech indexy ne?
tedy klasická chyba na mysql
SELECT count(*) FROM (poddotaz)
není zrovna nejvhodnější způsob zjištění počtu řádků
kdo používá dibi, tak bude znát, že datasource má tento problém
Asi záleží na které verzi mysql je to testováno, co vím tak stále by neměla být opravena chyba v IN. Snad někdy ve verzi 6.4, WHERE IN je podle mého testování nepoužitelné, mám takový pocit, že Jakub Vrána už o tom taky psal, ale abych si nevymýšlel, sorry nezbývá mi teď čas hledat články a bugy v mysql, ale bezpečně vím, že v WHERE IN mám při velkém množství záznamů obrovský problém na mysql ± 5.0.86
SELECT name, GROUP_CONCAT(id) FROM clanky GROUP BY name HAVING (count(name)>1)
Vysledek GROUP_CONCAT se da dobre prizpusobit k cemukoliv..