W tym artykule pokazano, jak znaleźć zduplikowane wiersze w tabeli bazy danych. To bardzo częste pytanie początkujących. Podstawowa technika jest prosta. Pokażę też kilka odmian, na przykład jak znaleźć „duplikaty w dwóch kolumnach” (ostatnie pytanie na kanale IRC #mysql).
Jak znaleźć zduplikowane wiersze
Pierwszym krokiem jest zdefiniowanie, co dokładnie sprawia, że wiersz jest duplikatem innego wiersza. W większości przypadków jest to łatwe: mają tę samą wartość w jakiejś kolumnie. Potraktuję to jako definicję roboczą tego artykułu, ale możesz musisz zmienić poniższe zapytania, jeśli twoje pojęcie „duplikatu” jest bardziej skomplikowane.
W tym artykule użyję tych przykładowych danych:
Pierwsze dwa wiersze mają takie same wartość w kolumnie day, więc jeśli uznam je za duplikaty, oto zapytanie, aby je znaleźć. W zapytaniu zastosowano klauzulę GROUP BY, aby umieścić wszystkie wiersze z tą samą wartością day w jednej „grupie”, a następnie policzyć rozmiar group:
Zduplikowane wiersze mają liczbę większą niż jeden. Jeśli chcesz zobaczyć tylko te wiersze, które są zduplikowane, musisz użyć HAVING klauzula (nie klauzula WHERE), na przykład:
Oto podstawowa technika: pogrupuj według kolumny zawierającej duplikaty i wyświetl tylko te grupy, które mają więcej niż jeden wiersz.
Dlaczego nie możesz użyć klauzuli WHERE?
A Klauzula WHERE filtruje wiersze przed ich zgrupowaniem. Klauzula HAVING filtruje je po zgrupowaniu. Dlatego nie możesz użyć WHERE w powyższym zapytaniu.
Jak usunąć zduplikowane wiersze
Powiązane pytanie brzmi: jak usunąć „zduplikowane” wiersze po znajdź je Pytaj, kiedy czyszczenie złych danych polega na usunięciu wszystkich duplikatów z wyjątkiem jednego, aby można było umieścić odpowiednie indeksy i klucze podstawowe w tabeli i zapobiec ponownemu dostaniu się duplikatów do tabeli.
Ponownie, pierwsza musisz upewnić się, że Twoja definicja jest jasna. Który dokładnie wiersz chcesz zachować? Pierwszy? Ten z największą wartością w jakiejś kolumnie? W tym artykule zakładam, że chcesz zachować „pierwszy” wiersz – ten, który ma najmniejszą wartość z kolumny id. Oznacza to, że chcesz usunąć co drugi wiersz.
Prawdopodobnie najłatwiejszym sposobem jest użycie tabeli tymczasowej. Zwłaszcza w MySQL istnieją pewne ograniczenia dotyczące wybierania z tabeli i aktualizowania jej w tym samym zapytaniu. Możesz je obejść, jak wyjaśniam w moim artykule Jak wybrać cel aktualizacji w MySQL, ale po prostu uniknę tych komplikacji i użyję tabeli tymczasowej.
Dokładna definicja zadania to aby usunąć każdy wiersz, który ma duplikat, z wyjątkiem wiersza o minimalnej wartości id dla tej grupy. Musisz więc znaleźć nie tylko wiersze, w których jest więcej niż jeden w grupie, ale także wiersz, który chcesz zachować. Możesz to zrobić za pomocą funkcji MIN(). Oto kilka zapytań potrzebnych do utworzenia tabeli tymczasowej i znalezienia danych potrzebnych do wykonania DELETE:
Teraz, gdy masz już te dane, możesz przystąpić do ich usuwania „złe” rzędy. Jest na to wiele sposobów, a niektóre są lepsze od innych (zobacz mój artykuł o problemach typu „wiele do jednego” w SQL), ale znowu uniknę drobniejszych punktów i po prostu pokażę ci standardową składnię, która powinna działać dowolny RDBMS, który obsługuje podzapytania:
Jeśli twój RDBMS nie obsługuje podzapytań lub jeśli jest bardziej wydajny, możesz chcieć usunąć wiele tabel. Składnia tego jest różna w różnych systemach, dlatego należy zapoznać się z dokumentacją systemu. Może zajść konieczność zrobienia tego wszystkiego w transakcji, aby uniknąć zmiany danych przez innych użytkowników podczas pracy, jeśli jest to problem.
Jak znaleźć duplikaty w wielu kolumnach
Ktoś ostatnio zadał pytanie podobne do tego na kanale #mysql IRC:
Mam tabelę z kolumnami
bic, które łączy dwie inne tabelebic, i chcę aby znaleźć wszystkie wiersze, które mają duplikaty wblubc.
Trudno było dokładnie zrozumieć, co to oznacza, ale po pewnej rozmowie zrozumiałem: osoba ta chciała mieć możliwość umieszczania unikalnych indeksów w kolumnach b i c osobno.
Bardzo łatwo jest znaleźć wiersze ze zduplikowanymi wartościami w jednej lub drugiej kolumnie, jak pokazałem powyżej: po prostu pogrupuj według tego koloru mn i policz wielkość grupy. Łatwo jest znaleźć całe wiersze, które są dokładnymi duplikatami innych wierszy: po prostu pogrupuj je według dowolnej liczby kolumn.Ale trudniej jest zidentyfikować wiersze, które mają zduplikowaną wartość b lub zduplikowaną wartość c. Weź następującą przykładową tabelę, która jest mniej więcej tym, co opisała osoba:
Teraz możesz łatwo zobaczyć, że w tej tabeli jest kilka „zduplikowanych” wierszy, ale żadne dwa wiersze nie mają faktycznie tej samej krotki {b, c}. Dlatego jest to trochę trudniejsze do rozwiązania.
Zapytania, które nie działają
Jeśli pogrupujesz razem dwie kolumny, uzyskasz różne wyniki w zależności od sposobu grupowania i liczyć. W tym miejscu użytkownik IRC był zaskoczony. Czasami zapytania znajdowały jakieś duplikaty, ale nie inne. Oto kilka rzeczy, których próbowała ta osoba:
To zapytanie zwraca każdy wiersz tabeli, z COUNT(*) 1, co wydaje się być niewłaściwym zachowaniem, ale w rzeczywistości tak nie jest. Dlaczego? Ponieważ > 1 znajduje się wewnątrz COUNT(). Łatwo go przeoczyć, ale to zapytanie jest w rzeczywistości takie samo, jak
Dlaczego? Ponieważ (b > 1) to wyrażenie boolowskie. To wcale nie jest to, czego chcesz. Chcesz
Zwraca to oczywiście zero wierszy, ponieważ nie ma zduplikowanych krotek {b, c}. Ta osoba wypróbowała wiele innych kombinacji klauzul HAVING oraz OR i AND, grupując je według jednej kolumny i licząc w drugiej itd .:
Jednak nic nie znalazło wszystkich duplikatów. Myślę, że najbardziej frustrujące jest to, że częściowo zadziałało, co sprawiło, że osoba pomyślała, że to prawie właściwe zapytanie… być może wystarczyłaby inna odmiana…
W rzeczywistości nie można tego zrobić z tego typu proste zapytanie GROUP BY. Dlaczego to? Dzieje się tak, ponieważ gdy grupujesz według jednej kolumny, rozdzielasz podobne wartości z drugiej kolumny na wiele grup. Możesz to zobaczyć wizualnie, porządkując według tych kolumn, co robi grupowanie. Najpierw uporządkuj według kolumny b i zobacz, jak są pogrupowane:
| a | b | c |
|---|---|---|
| 7 | 1 | 1 |
| 8 | 1 | 2 |
| 9 | 1 | 3 |
| 10 | 2 | 1 |
| 11 | 2 | 2 |
| 12 | 2 | 3 |
| 13 | 3 | 1 |
| 14 | 3 | 2 |
| 15 | 3 | 3 |
Przy porządkowaniu (grupowanie) według kolumny b zduplikowane wartości w kolumnie c są podzielone na różne grupy, więc nie możesz ich policzyć za pomocą COUNT(DISTINCT c), tak jak próbowała to osoba. Funkcje agregujące, takie jak COUNT(), działają tylko w obrębie grupy i nie mają dostępu do wierszy umieszczonych w innych grupach. Podobnie w przypadku zamawiania przez c zduplikowane wartości w kolumnie b są rozdzielane na różne grupy. Nie jest możliwe wykonanie tego zapytania zgodnie z oczekiwaniami.
Niektóre poprawne rozwiązania
Prawdopodobnie najprostszym rozwiązaniem jest znalezienie duplikatów dla każdej kolumny osobno i UNION je razem, na przykład:
Kolumna what_col w wynikach wskazuje, w której kolumnie została znaleziona zduplikowana wartość. Innym podejściem jest użycie podzapytania:
Jest to prawdopodobnie dużo mniej wydajne niż podejście UNION i pokaże każdy zduplikowany wiersz, a nie tylko zduplikowane wartości. Jeszcze innym podejściem jest wykonywanie samołączeń dla zgrupowanych podzapytań w klauzuli FROM. Prawidłowe pisanie jest bardziej skomplikowane, ale może być konieczne w przypadku niektórych złożonych danych lub w celu zwiększenia wydajności:
Każde z tych zapytań wystarczy i jestem pewien, że są też inne sposoby. Jeśli możesz użyć UNION, jest to prawdopodobnie najłatwiejsze.
