Tento článek ukazuje, jak najít duplicitní řádky v databázové tabulce. Toto je velmi častá otázka pro začátečníky. Základní technika je přímočará. Ukážu také některé varianty, například jak najít „duplikáty ve dvou sloupcích“ (nedávná otázka na kanálu #mysql IRC).
Jak najít duplicitní řádky
Prvním krokem je definovat, co přesně dělá řádek duplikátem jiného řádku. Většinou je to snadné: mají v nějakém sloupci stejnou hodnotu. Budu to brát jako funkční definici tohoto článku, ale můžete je-li vaše představa „duplikátu“ komplikovanější, je třeba upravit níže uvedené dotazy.
U tohoto článku použiji tato ukázková data:
První dva řádky mají stejné hodnota ve sloupci day, takže pokud je považuji za duplikáty, je zde dotaz na jejich vyhledání. Dotaz používá klauzuli GROUP BY k vložení všech řádků se stejnou hodnotou day do jedné „skupiny“ a poté spočítá velikost group:
Duplikované řádky mají počet větší než jeden. Pokud chcete zobrazit pouze duplikované řádky, musíte použít HAVING klauzule (nikoli klauzule WHERE), jako je tato:
Toto je základní technika: seskupte podle sloupce, který obsahuje duplikáty, a zobrazte pouze ty skupiny, které mají více než jeden řádek.
Proč nemůžete použít klauzuli WHERE?
A Klauzule WHERE filtruje řádky před jejich seskupením. Klauzule HAVING je filtruje po seskupení. Proto nemůžete použít WHERE klauzule ve výše uvedeném dotazu.
Jak odstranit duplicitní řádky
Související otázkou je, jak odstranit „duplicitní“ řádky, jakmile najít je. Společný t zeptat se při čištění špatných dat je odstranit všechny duplikáty kromě jednoho, abyste mohli do tabulky vložit správné indexy a primární klíče a zabránit tak tomu, aby se duplikáty znovu dostaly do tabulky.
Opět první musíte se ujistit, že vaše definice je jasná. Přesný řádek, který si chcete ponechat? První? Ten s největší hodnotou nějakého sloupce? U tohoto článku předpokládám, že si chcete ponechat „první“ řádek – ten s nejmenší hodnotou ve sloupci id. To znamená, že chcete smazat všechny ostatní řádky.
Pravděpodobně nejjednodušší způsob, jak to udělat, je dočasná tabulka. Zejména v MySQL existují určitá omezení týkající se výběru z tabulky a její aktualizace ve stejném dotazu. Můžete je obejít, jak vysvětluji ve svém článku Jak vybrat z cíle aktualizace v MySQL, ale těmto komplikacím se jen vyhnu a použiji dočasnou tabulku.
Přesná definice úkolu je smazat každý řádek, který má duplikát, kromě řádku s minimální hodnotou id pro danou skupinu. Musíte tedy najít nejen řádky, kde je ve skupině více než jeden, ale také musíte najít řádek, který si chcete ponechat. Můžete to udělat pomocí funkce MIN(). Tady je několik dotazů, jak vytvořit dočasnou tabulku a vyhledat data, která potřebujete k provedení DELETE:
Nyní, když máte tato data, můžete pokračovat v mazání „špatné“ řádky. Existuje mnoho způsobů, jak to udělat, a některé jsou lepší než jiné (viz můj článek o problémech typu mnoho-na-jeden v SQL), ale zase se vyhnu jemnějším bodům a ukážu vám standardní syntaxi, která by měla fungovat jakýkoli RDBMS, který podporuje poddotazy:
Pokud váš RDBMS nepoddotazy nepodporuje, nebo je-li to efektivnější, můžete provést mazání více tabulek. Syntaxe se u různých systémů liší, takže je třeba nahlédnout do dokumentace k vašemu systému. Možná budete muset toto všechno provést v transakci, abyste zabránili ostatním uživatelům měnit data, když pracujete, pokud je to problém.
Jak najít duplikáty ve více sloupcích
Někdo nedávno položil podobnou otázku na IRM kanálu #mysql:
Mám tabulku se sloupci
bacpropojující další dvě tabulkybaca já chci najít všechny řádky, které mají duplikáty vbneboc.
Bylo těžké přesně pochopit, co to znamená, ale po nějakém rozhovoru jsem to pochopil: osoba chtěla mít možnost vložit jedinečné indexy na sloupce b a c samostatně.
Je celkem snadné najít řádky s duplicitními hodnotami v jednom nebo druhém sloupci, jak jsem vám ukázal výše: stačí seskupit podle toho colu mn a spočítat velikost skupiny. A je snadné najít celé řádky, které jsou přesnými duplikáty ostatních řádků: stačí je seskupit podle tolika sloupců, kolik potřebujete.Je však těžší identifikovat řádky, které mají buď duplikovanou b hodnotu, nebo duplicitní c hodnotu. Vezměte si následující ukázkovou tabulku, což je zhruba to, co osoba popsala:
Nyní můžete snadno vidět, že v této tabulce jsou nějaké „duplicitní“ řádky, ale žádné dva řádky nemají stejnou n-tici {b, c}. Proto je řešení o něco obtížnější.
Dotazy, které nefungují
Pokud seskupujete podle dvou sloupců, získáte různé výsledky podle toho, jak seskupujete a počítat. To je místo, kde byl uživatel IRC naštvaný. Někdy by dotazy našly duplikáty, jiné ne. Zde jsou některé z věcí, které tato osoba vyzkoušela:
Tento dotaz vrátí každý řádek v tabulce s COUNT(*) of 1, což se zdá být nesprávným chováním, ale ve skutečnosti tomu tak není. Proč? Protože > 1 je uvnitř COUNT(). Je to docela snadné přehlédnout, ale tento dotaz je ve skutečnosti stejný jako
Proč? Protože (b > 1) je booleovský výraz. To vůbec není to, co chcete. Chcete
Tím se samozřejmě vrátí nulové řádky, protože neexistují duplicitní {b, c} n-tice. Osoba vyzkoušela mnoho dalších kombinací klauzulí HAVING a ORs a ANDs, seskupení podle jednoho sloupce a počítání druhého atd.:
Nic však nenašlo všechny duplikáty. Myslím, že to nejvíce frustruje, je to, že to částečně fungovalo, což člověka přimělo myslet si, že to byl téměř správný dotaz … možná by ho získala jen další variace …
Ve skutečnosti je to s tímto typem jednoduchý GROUP BY dotaz. Proč je to? Je to proto, že když seskupujete podle jednoho sloupce, distribuujete jako hodnoty druhého sloupce do více skupin. Vidíte to vizuálně seřazením podle těchto sloupců, což seskupení dělá. Nejprve objednejte podle sloupce b a podívejte se, jak jsou seskupeny:
| a | b | c |
|---|---|---|
| 7 | 1 | 1 |
| 8 | 1 | 2 |
| 9 | 1 | 3 |
| 10 | 2 | 1 |
| 11 | 2 | 2 |
| 12 | 2 | 3 |
| 13 | 3 | 1 |
| 14 | 3 | 2 |
| 15 | 3 | 3 |
Když objednáváte (seskupujete) podle sloupce b, duplicitní hodnoty ve sloupci c jsou rozděleny do různých skupin, takže je nelze počítat s COUNT(DISTINCT c) tak, jak se daná osoba pokoušela. Agregované funkce, jako je COUNT(), fungují pouze ve skupině a nemají přístup k řádkům umístěným v jiných skupinách. Podobně, když objednáváte u c, jsou duplicitní hodnoty ve sloupci b rozděleny do různých skupin. Není možné, aby tento dotaz provedl to, co je požadováno.
Některá správná řešení
Pravděpodobně nejjednodušším řešením je najít duplikáty pro každý sloupec zvlášť a UNION společně, takto:
Sloupec what_col ve výstupu označuje, ve kterém sloupci byla duplicitní hodnota nalezena. Dalším přístupem je použít poddotazy:
To je pravděpodobně mnohem méně efektivní než přístup UNION a zobrazí každý duplikovaný řádek, nejen hodnoty, které jsou duplikovány. Ještě dalším přístupem je provést vlastní připojení proti seskupeným poddotazům v klauzuli FROM. To je složitější při správném psaní, ale může to být nutné pro některá složitá data nebo pro efektivitu:
Kterýkoli z těchto dotazů bude stačit a jsem si jist, že existují i jiné způsoby. Pokud můžete použít UNION, je to pravděpodobně nejjednodušší.
