MySQLi - obsługa duplikatów

Tabele lub zestawy wyników czasami zawierają zduplikowane rekordy. Czasami jest to dozwolone, ale czasami wymagane jest zatrzymanie zduplikowanych rekordów. Czasami 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 UNIQUEIndeksuj w tabeli z odpowiednimi polami, aby zatrzymać zduplikowane rekordy. Weźmy jeden 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 KLUCZ PODSTAWOWY do jego definicji. Gdy to zrobisz, konieczne jest również zadeklarowanie indeksowanych kolumn jako NIE NULL, ponieważ KLUCZ PODSTAWOWY nie zezwala na wartości NULL -

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 do tabeli rekord, który powiela istniejący rekord w kolumnie lub kolumnach definiujących indeks.

Posługiwać się INSERT IGNORE zamiast INSERT. Jeśli rekord nie powiela istniejącego rekordu, MySQLi wstawia go jak zwykle. Jeśli rekord jest duplikatem, słowo kluczowe IGNORE nakazuje MySQLi odrzucenie go po cichu bez generowania błędu.

Poniższy przykład nie zawiera błędów i 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)

Posługiwać się REPLACEzamiast WSTAWIĆ. 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)

INSERT IGNORE i REPLACE powinny być wybrane zgodnie z zachowaniem obsługi duplikatów, które chcesz wywołać. INSERT IGNORE przechowuje pierwszy z zestawu zduplikowanych rekordów, a resztę odrzuca. REPLACE zachowuje ostatni z zestawu duplikatów i usuwa wszystkie wcześniejsze.

Innym sposobem wymuszenia unikalności jest dodanie do tabeli indeksu UNIQUE zamiast klucza PRIMARY KEY.

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 first_name i last_name 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, aby zidentyfikować zestawy wartości, które są zduplikowane, wykonaj następujące czynności -

Określ, które kolumny zawierają wartości, które mogą zostać zduplikowane.
Wymień te kolumny na liście wyboru kolumn wraz z COUNT (*).
Wymień również kolumny w klauzuli GROUP BY.
Dodaj klauzulę HAVING, 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 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 DISTINCT jest dodanie klauzuli GROUP BY, która nazywa wybrane kolumny. Powoduje to usunięcie duplikatów i wybranie tylko unikalnych 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, oto procedura -

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 INDEX lub PRIMAY KEY do tej tabeli. Nawet jeśli ta tabela jest już dostępna, możesz użyć tej techniki do usunięcia zduplikowanych rekordów i będziesz bezpieczny również w przyszłości.

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