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
Zapytanie nie działa prawidłowo kiedy w mamy więcej jak jeden duplikat !
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.
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 ;]
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
Post zedytowany, wstawiłem alternatywne rozwiązanie dla mySQLa
Dobry przykład – przydało mi się :)
Pozdrawiam !
Super! dzięki za zapytanie :)
Opisany przyklad wcale nie dotyczy prawdziwych duplikatow! jak widac (people.id < MAX(P.id)
) maja one w powyzszym przykladzie rozniace sie IDentyfikatory!
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
Pytanie, czy na bazie można tak zakładać i zdejmować index’y ile i jak się chce?
Tylko po to aby usunąć duplikaty.
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.
W SQL Server funkcja row_number pozwala rozwiązac ten problem w bardzo prosty sposób.
Pisałem o tym jakis czas temu tutaj http://updatedev.blogspot.com/2008/08/uswanie-duplikatw.html
@lolek
dzięki wielkie. Właśnie wykorzystałem ten myk z „ignore” i działa szybko i idealnie.
Super wpis, dzięki wielkie.
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
Super, genialne :)
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);
:)
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.
Próbuje to zapytanie zrobić na Accessie i nie działa
niby ok, ale mam duplikaty duplikatów (1 i 6 ID nie ma i jest ok) ale po co duplikaty
ID S_ID R_ID Czas
2 100348379 1605575498 29.08.2017-23:59:44
3 100348379 1605575498 29.08.2017-23:59:44
3 100348379 1605575498 29.08.2017-23:59:44
4 100348379 1605575498 29.08.2017-23:59:44
4 100348379 1605575498 29.08.2017-23:59:44
4 100348379 1605575498 29.08.2017-23:59:44
5 100348379 1605575498 29.08.2017-23:59:44
5 100348379 1605575498 29.08.2017-23:59:44
5 100348379 1605575498 29.08.2017-23:59:44
5 100348379 1605575498 29.08.2017-23:59:44
7 100348379 1605575498 29.08.2017-23:59:45
8 100348379 1605575498 29.08.2017-23:59:45
nawet jak zrobię distinct to mam
Additional information: Określ tabelę zawierającą rekordy do usunięcia.
Select działa
„DELETE DISTINCT(D.ID, D.S_ID, D.R_ID, D.Czas) FROM maildata_tab AS U INNER JOIN maildata_tab AS D ON (D.S_ID = U.S_ID AND D.R_ID = U.R_ID AND D.Czas = U.Czas) AND (D.ID > U.ID)”
A co w sytuacji, gdy mam dwa rekordy identyczne, gdzie id (który jest/powinien być PK) też się pokrywa?
You can use the following script to remove duplicate rows from a Microsoft SQL Server table:
SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1
DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)
INSERT original_table
SELECT *
FROM duplicate_table
DROP TABLE duplicate_table
When this script is executed, it follows these steps:
It moves one instance of any duplicate row in the original table to a duplicate table.
It deletes all rows from the original table that also reside in the duplicate table.
It moves the rows in the duplicate table back into the original table.
It drops the duplicate table
wzięte z linku poniżej
https://support.microsoft.com/pl-pl/help/70956/how-to-remove-duplicate-rows-from-a-sql-server-table-by-using-a-script
Jestem naprawdę pod wrażeniem Twoimi umiejętnościami i jak elegancko piszesz.
Format na Twoim blogu jest na dobrym poziomie. Czy to jest płatny motyw, czy też zmodyfikowany
pod siebie? W każdym razie utrzymuj nadal wysoką jakość pisania, to
rzadkie zobaczyć tak świetny blog w dzisiejszych
czasach. :)