MySQL - obsługa duplikatów

Zazwyczaj tabele lub zestawy wyników zawierają czasami zduplikowane rekordy. W większości przypadków jest to dozwolone, ale czasami wymagane jest zatrzymanie zduplikowanych rekordów. Wymagane jest zidentyfikowanie zduplikowanych rekordów i usunięcie ich z tabeli. W tym rozdziale opisano, jak zapobiec występowaniu zduplikowanych rekordów w tabeli i jak usunąć już istniejące zduplikowane rekordy.

Zapobieganie występowaniu duplikatów w tabeli

Możesz użyć PRIMARY KEY lub a UNIQUE Indeksuj w tabeli z odpowiednimi polami, aby zatrzymać zduplikowane rekordy.

Weźmy przykład - poniższa tabela nie zawiera takiego indeksu ani klucza podstawowego, więc pozwoliłaby na zduplikowane rekordy dla first_name i last_name.

CREATE TABLE person_tbl (
   first_name CHAR(20),
   last_name CHAR(20),
   sex CHAR(10)
);

Aby zapobiec tworzeniu wielu rekordów z tymi samymi wartościami imienia i nazwiska w tej tabeli, dodaj rozszerzenie PRIMARY KEYdo jego definicji. Kiedy to zrobisz, konieczne jest również zadeklarowanie indeksowanych kolumnNOT NULL, ponieważ PRIMARY KEY nie pozwala NULL wartości -

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

Obecność unikatowego indeksu w tabeli zwykle powoduje wystąpienie błędu, jeśli wstawisz rekord do tabeli, który powiela istniejący rekord w kolumnie lub kolumnach definiujących indeks.

Użyj INSERT IGNORE polecenie zamiast INSERTKomenda. Jeśli rekord nie powiela istniejącego rekordu, MySQL wstawia go jak zwykle. Jeśli rekord jest duplikatem, plikIGNORE słowo kluczowe mówi MySQL, aby po cichu je odrzucił bez generowania błędu.

Poniższy przykład nie zawiera błędów, a jednocześnie nie wstawia zduplikowanych rekordów.

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

Użyj REPLACEzamiast polecenia WSTAW. Jeśli rekord jest nowy, jest wstawiany tak samo, jak w przypadku polecenia INSERT. Jeśli jest to duplikat, nowy rekord zastępuje stary.

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 2 rows affected (0.00 sec)

Polecenia INSERT IGNORE i REPLACE należy wybrać zgodnie z zachowaniem obsługi duplikatów, które chcesz zastosować. Polecenie INSERT IGNORE zachowuje pierwszy zestaw zduplikowanych rekordów, a pozostałe odrzuca. Polecenie REPLACE zachowuje ostatni zestaw duplikatów i usuwa wszystkie wcześniejsze.

Innym sposobem na wymuszenie wyjątkowości jest dodanie UNIQUE index zamiast klucza podstawowego do tabeli.

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

Liczenie i identyfikacja duplikatów

Poniżej znajduje się kwerenda licząca zduplikowane rekordy z imieniem i nazwiskiem w tabeli.

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
   -> FROM person_tbl
   -> GROUP BY last_name, first_name
   -> HAVING repetitions > 1;

To zapytanie zwróci listę wszystkich zduplikowanych rekordów w tabeli person_tbl. Ogólnie rzecz biorąc, aby zidentyfikować zestawy wartości, które są zduplikowane, wykonaj kroki podane poniżej.

Określ, które kolumny zawierają wartości, które mogą zostać zduplikowane.
Wymień te kolumny na liście wyboru kolumn, wraz z rozszerzeniem COUNT(*).
Wymień kolumny w GROUP BY klauzula również.
Dodać HAVING klauzula, która eliminuje unikatowe wartości, wymagając, aby liczba grup była większa niż jeden.

Eliminowanie duplikatów z wyniku zapytania

Możesz użyć DISTINCT polecenie wraz z instrukcją SELECT, aby znaleźć unikalne rekordy dostępne w tabeli.

mysql> SELECT DISTINCT last_name, first_name
   -> FROM person_tbl
   -> ORDER BY last_name;

Alternatywą dla polecenia DISTINCT jest dodanie klauzuli GROUP BY, która nazywa wybrane kolumny. Powoduje to usunięcie duplikatów i wybranie tylko unikatowych kombinacji wartości w określonych kolumnach.

mysql> SELECT last_name, first_name
   -> FROM person_tbl
   -> GROUP BY (last_name, first_name);

Usuwanie duplikatów za pomocą wymiany tabeli

Jeśli masz zduplikowane rekordy w tabeli i chcesz usunąć wszystkie zduplikowane rekordy z tej tabeli, postępuj zgodnie z procedurą podaną poniżej.

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
   -> FROM person_tbl;
   -> GROUP BY (last_name, first_name);

mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

Łatwym sposobem na usunięcie zduplikowanych rekordów z tabeli jest dodanie INDEKSU lub KLUCZA PODSTAWOWEGO do tej tabeli. Nawet jeśli ta tabela jest już dostępna, możesz użyć tej techniki, aby usunąć zduplikowane rekordy, a także będziesz bezpieczny w przyszłości.

mysql> ALTER IGNORE TABLE person_tbl
   -> ADD PRIMARY KEY (last_name, first_name);