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

20 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);

    :)

  10. Czesc, ja nie mam klucza głównego id. Jak mogę to rozwiązać?

    mam cztery kolumny:

    nr_sklepu, miasto, grupa, szyld
    1111, Wrocław, A, EM
    1122, Gdynia, B, EM
    1133, Poznań, A, EM
    1111, Wrocław, A, EM
    1122, Gdynia, B, EM
    1133, Poznań, A, EM

    Pozdrawiam
    Adrian.

Dodaj komentarz

Twój adres email nie zostanie opublikowany.