Jak usunąć zduplikowane rekody w tabeli bazy danych?

Czasami zdarza się że, poprzez nie do końca poprawną walidację danych wejściowych lub z powodu błędnego działania aplikacji stworzą nam się w bazie danych zduplikowane rekordy.

Takie niepożądane zduplikowane dane w 90% będą nam przeszkadzać i w najlepszym razie mogą powodować niepotrzebny zamęt w aplikacji. Skutki mogą takżę spowodować dużo poważniejsze problemy jak chociażby rozsynchronizowanie się danych w powiązanych tabelach w bazie.

Co należy robić w przypadku wykrycia zduplikowanych rekordów?

W pierwszej kolejności trzeba dojść do tego w jaki sposób powstały a następnie trzeba usprawnić aplikację w taki sposób aby powstawanie duplikatów było już niemożliwe.
Następnie trzeba wziąć się za zapisane dane i należy usunąć z nich nadmiarowe rekordy.

Usuwanie zduplikowanych rekordów

Usuwać duplikaty można na conajmniej kilka sposobów. Najczęstszą radą spotykaną w internecie to przeniesienie unikalnych rekordów do nowej tymczasowej tabeli, skasowanie wszystkich danych ze starej tabeli, a następnie zgranie z powrotem rekordy unikalne.

Niestety nie jest to metoda ani szybka, ani skuteczna ani bezpieczna.

Na prościutkim przykładzie rzedstawię poniżej najlepszy sposób na pozbycie się duplikatów

Tworzymy przykładową tabelę:

CREATE TABLE people(
	id SERIAL PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	surname VARCHAR(100) NOT NULL,
	phone VARCHAR(100) NOT NULL,
	email VARCHAR(100) NOT NULL
);

Tabela zawiera autoinkrementowane pole id, które jest kluczem jej głównym i kilka pól przetrzymujących dane.

Wstawiamy zduplikowane rekordy:

INSERT INTO people(name,surname,phone,email) VALUES('Marcin','Szajda','660111111','marcin@nospam.pl');
INSERT INTO people(name,surname,phone,email) VALUES('Marcin','Szajda','660111111','marcin@nospam.pl');
INSERT INTO people(name,surname,phone,email) VALUES('Agnieszka','Czopek','550123123','aga@nospam.pl');
INSERT INTO people(name,surname,phone,email) VALUES('Jas','Kowalski','555123147','jas@nospam.pl');
INSERT INTO people(name,surname,phone,email) VALUES('Jas','Kowalski','555123147','jas@nospam.pl');

Wyświetlamy zawartość tabeli:

SELECT * FROM people;
 id |   name    | surname  |   phone   |      email       
----+-----------+----------+-----------+------------------
  1 | Marcin    | Szajda   | 660111111 | marcin@nospam.pl
  2 | Marcin    | Szajda   | 660111111 | marcin@nospam.pl
  3 | Agnieszka | Czopek   | 550123123 | aga@nospam.pl
  4 | Jas       | Kowalski | 555123147 | jas@nospam.pl
  5 | Jas       | Kowalski | 555123147 | jas@nospam.pl
(5 rows)

Jak widzimy mamy zduplikowane 2 rekordy, pierwszym krokiem do usunięcia rekordów będzie napisanie zapytania, które je wyświetli a następnie
zmienimy SELECT NA DELETE.

SELECT
	id, name, surname, phone, email
FROM
	people
WHERE
	EXISTS (
		SELECT
			NULL
		FROM
			people AS P
		WHERE
			people.name = P.name
			AND people.surname = P.surname
			AND people.phone = P.phone
			AND people.email = P.email
		GROUP BY
			P.name, P.surname, P.phone, P.email
		HAVING
			people.id < MAX(P.id)
	);

Wynikiem zapytania będzie:

 id |  name  | surname  |   phone   |      email       
----+--------+----------+-----------+------------------
  1 | Marcin | Szajda   | 660111111 | marcin@nospam.pl
  4 | Jas    | Kowalski | 555123147 | jas@nospam.pl
(2 rows)

Wyjaśnienie zapytania:

  • SELECT _FIELDS_ FROM people - deklarujemy pola, które checmy wyświetlić
  • WHERE EXISTS( _CONDITION_ ) - które spełniają warunek _CONDITION_
  • SELECT NULL FROM people AS P - w podzapytaniu nic nie wyświetlamy, wykorzystujemy je jedynie do złączenia
  • WHERE _CONDITION_ - joinujemy po wszystkich polach, które mają tworzyć unikalny klucz
  • GROUP BY _FIELDS_ - grupujemy wg. unikalnych pól
  • HAVING people.id < MAX(P.id) - warunek na id

Modyfikujemy teraz powyższe zapytanie SELECT _FIELDS_ na DELETE

DELETE
FROM
	people
WHERE
	EXISTS (
		SELECT
			NULL
		FROM
			people AS P
		WHERE
			people.name = P.name
			AND people.surname = P.surname
			AND people.phone = P.phone
			AND people.email = P.email
		GROUP BY
			P.name, P.surname, P.phone, P.email
		HAVING
			people.id < MAX(P.id)
	);

I otrzymujemy tabele wyczyszczoną z nadmiarowych rekordów.

Jakie to proste.

**********************edit**********************

Jak słusznie zauważył kolega Wild Child powyższe zapytanie nie działa w mySQL.
Przyznaje się bez bicia, że testowałem jedynie w PostgreSQL gdyż powyższy kod SQL
wyglądał na uniwersalny.

Okazało się jednak, że mySQL nie pozwala na jednoczesne wyświetlanie i modyfikowanie
zawartości tabeli. (jednoczesny DELETE w zapytaniu głównym i SELECT w podzapytaniu)

Posiedziałem trochę i wymyśliłem alternatywne zapytanie:

SELECT 
	D.*
FROM 
	people as U
INNER JOIN 
	people AS D ON (
	   	D.name = U.name AND
   		D.surname = U.surname AND
	   	D.phone = U.phone AND
	   	D.email = U.email
	   )
AND 
	D.id > U.id;

Konstrukcja tego zapytania jest nieco inna:

  • Robimy JOINA wewnątrz tabeli po polach, które mają być unikalne
  • Zostawiamy rekord o najniższym id, a resztę kasujemy (warunek AND D.id > U.id)
  • Skróty: D - duplicate, U - unique
  • Oczywiście aby usunąć rekordy należy zamienić słowo SELECT na DELETE

18 myśli na temat “Jak usunąć zduplikowane rekody w tabeli bazy danych?”

  1. Chyba nie do końca wczytałeś się w treść posta – zaprezentowany SELECT wyświetla zduplikowane rekordy a wiec w przypadku gdy bedzie wiecej niz jeden duplikat (np. będzie 5 takich samych rekordów) w tabeli to zapytanie wyswietli wszystkie duplikaty (czyli 4 takie same wiersze). To jest krok selekconowania duplikatow, następnie gdy w następnym kroku zamienimy SELECT na DELETE to kasujemy wszystkie wczesniej wyswietlone duplikaty.
    Gdy po skasowaniu zrobimy jeszcze raz SELECT w tabeli nie bedzie juz duplikatów.

  2. Hm, no skrypt jest prosty, działa, ale… Przy dużej ilości rekordów potrafi strasznie zamulić serwer co czyni go niespecjalnie funkcjonalnym. Niemniej skrypt sam w sobie całkiem ciekawy, na pewno się przyda ;]

  3. aha i jeszcze jedno, wyświetla duplikaty prawidłowo, ale przy próbie usunięcia (czyli z zamienionym select na delete) wyświetla mi się komunikat – #1093 – You can’t specify target table ‚people’ for update in FROM clause

  4. Opisany przyklad wcale nie dotyczy prawdziwych duplikatow! jak widac (people.id < MAX(P.id)
    ) maja one w powyzszym przykladzie rozniace sie IDentyfikatory!

  5. post stary ale dodam dla potomnych.
    W MySql’u mamy dostepny inny mechanizm.
    Mianowicie zakladamy unikatowy klucz na wartosci ktore sa zduplikowane ale w troszke inny sposob:

    alter ignore table tabela_z_duplikatami add unique index `unqidx` (duplicate_col1,duplicate_col2);

    Dzieki zastosowaniu slowka „ignore”, mysql usunie zduplikowane wartosci.
    Potem mozemy spokojnie usunac ten index, albo go zostawic, kto jak woli ;)
    Dodam ze opcja dostepna conajmniej od MySql’a 4.x

  6. Usuwanie lub tworzenie indeksów nie wpływa w żaden sposób na dane w bazie gdyż operujemy na „danych pomocniczych”. Można je tworzyć i usuwać wielokrotnie bez żadnych obaw.

  7. @lolek
    dzięki wielkie. Właśnie wykorzystałem ten myk z „ignore” i działa szybko i idealnie.

  8. Od jutra zaczynam odchudzanie, kto sie odchudza ze mna? Znalazłam w google dobry
    sposób na chudniecie, wygoglujcie sobie – xxally mój sukces
    w odchudzaniu

  9. Działa na mysql przy ponad 40 tys rekordow w czasie mniejszym niz 1 sekunda.
    Ogromne dzięki za to:
    alter ignore table osiagniecia add unique index `unqidx` (gracz_id,rodzaj,rodzaj_id);

    :)

Dodaj komentarz

Twój adres email nie zostanie opublikowany.