Jak najít a odstranit duplicity v databázi

Pondělí, Říjen 24, 2011

Když pracujete s databází, do které ukládáte hodně dat, pak se dřív nebo později dostanete do situace, kdy se v ní nachází nějaké duplicity. Otázka je jak je najít a následně pak smazat. Já na to používám velice jednoduchý SQL dotaz, který ale skvěle funguje.

Všechny duplicity v databázi najdete tímto SQL příkazem:
SELECT * FROM `vase_tabulka` as tab where (select count(id) from ` vase_tabulka` where duplicitni_sloupec = tab.duplicitni_sloupec) > 1

Aby vše fungovalo i u vás, musíte změnit 2 věci:

  1. vase_tabulka změňte na název tabulky, kde chcete hledat duplicity.
  2. duplicitni_sloupec změnte na název sloupce, kde chcete najít duplicity.

Pokud máte v databázi hodně dat, tak to může chvíli trvat. Je velice vhodné, aby na sloupci, kde hledáte duplicity, byl index. Dotaz následně vrátí všechny duplicitní řádky tak, že budou vypsané oba dva. Pokud chcete vidět oba dotazy pod sebou, pak použijte toto:

SELECT * FROM `vase_tabulka` as tab where (select count(id) from ` vase_tabulka` where duplicitni_sloupec = tab.duplicitni_sloupec) > 1 order by duplicitni_sloupec;

Pokud chcete vypsat vždy jen jeden záznam, použijte klauzuli group by na konci dotazu takto:

SELECT * FROM `vase_tabulka` as tab where (select count(id) from `vase_tabulka` where duplicitni_sloupec = tab.duplicitni_sloupec) > 1 group by duplicitni_sloupec;

Bohužel tento dotaz by měl teoreticky fungovat, avšak prakticky někdy nefunguje. Proto je nutné udělat ještě jednu kličku a použít celý dotaz jako vnořený do vnějšího, kde se teprve vytvoří skupinky.

SELECT * FROM (SELECT * FROM `vase_tabulka` as tab where (select count(id) from `vase_tabulka` where duplicitni_sloupec = tab.duplicitni_sloupec) > 1) as tab2 group by duplicitni_sloupec;

Odstranění duplicit

Při odstranění budeme postupovat tak, že chceme smazat všechny řádky, které odpovídají prvnímu dotazu, kromě těch, které odpovídají druhému dotazu. Bohužel většině databází není možné mazat ze stejné tabulky, ze které aktuálně čtete, proto není možné použít tyto dotazy jako podmína při mazání:

Musíme to tedy obejít. K tomu abychom mohli duplicity smazat, využijeme dočasné tabulky, do kterých všechna data uložíme a následně přečteme z nich. Celý dotaz pro odstranění duplicit bude vypadat následovně:

1) vytvoříme dočasnou tabulku, do které uložíme id řádků pro smazání.

CREATE TEMPORARY TABLE `smazat_temp` (
`id` INT(255) NOT NULL,
);

2) Do tabulky vložíme data

Insert into `smazat_temp` (id) values (SELECT id FROM `vase_tabulka` as tab where (select count(id) from `vase_tabulka` where duplicitni_sloupec = tab.duplicitni_sloupec) > 1);

3) Momentálně jsou uložena v tabulce veškeré duplicitní id, my ale chceme vždy jednu verzi z nich zachovat. Řádky, které chceme ponechat, proto z této tabulky vyjmeme dotazem:

Delete from `smazat_temp` where id in (SELECT * FROM (SELECT * FROM `vase_tabulka` as tab where (select count(id) from `vase_tabulka` where duplicitni_sloupec = tab.duplicitni_sloupec) > 1) as tab2 group by duplicitni_sloupec);

4) Na závěr odstraníme data z hlavní tabulky a dočasnou odstraníme.

Delete from `vase_tabulka` where id in (select id from `smazat_temp`);
Drop table `smazat_temp`;

Celý dotaz je nutné spustit během jednoho sezení. Pokud, tedy používáte phpMyAdmin, tak do pole s SQL příkazem musíte napsat vše najednou:

CREATE TEMPORARY TABLE `smazat_temp` (
`id` INT(255) NOT NULL,
);

Insert into `smazat_temp` (id)  (SELECT id FROM `vase_tabulka` as tab where (select count(id) from `vase_tabulka` where duplicitni_sloupec = tab.duplicitni_sloupec) > 1);

Delete from `smazat_temp` where id in (SELECT id FROM (SELECT * FROM `vase_tabulka` as tab where (select count(id) from `vase_tabulka` where duplicitni_sloupec = tab.duplicitni_sloupec) > 1) as tab2 group by duplicitni_sloupec);

Delete from `vase_tabulka` where id in (select id from `smazat_temp`);
Drop table `smazat_temp`;

Teď nechte databázi chvíli šrotit a duplicity budou pryč.

2 Comments

  1. Pavel Stehule napsal:

    Dobry den

    Pouziti korelovaneho poddotazu pro vyhledani duplicit neni uplne nejlepsi mozny zpusob – existuje podstatne rychlejsi trik jak odstranit duplicity – DELETE FROM vase_tabulka WHERE
    NOT id IN (SELECT MIN(id) FROM vase_tabulka GROUP BY duplicitni_sloupec)

    1. admin napsal:

      Bohužel v MySQL ale nejde mazat data ze sloupce, ze kterého se aktuálně čte, takže tento dotaz neprojde. Proto to obcházím přes dočasné tabulky.

Napsat komentář