MySQL - Gestione dei duplicati

In genere, tabelle o set di risultati a volte contengono record duplicati. La maggior parte delle volte è consentito, ma a volte è necessario interrompere i record duplicati. È necessario identificare i record duplicati e rimuoverli dalla tabella. Questo capitolo descriverà come prevenire il verificarsi di record duplicati in una tabella e come rimuovere i record duplicati già esistenti.

Prevenire la presenza di duplicati in una tabella

Puoi usare un file PRIMARY KEY o a UNIQUE Indice su una tabella con i campi appropriati per interrompere i record duplicati.

Facciamo un esempio: la tabella seguente non contiene tale indice o chiave primaria, quindi consentirebbe record duplicati per first_name e last_name.

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

Per evitare che più record con gli stessi valori di nome e cognome vengano creati in questa tabella, aggiungi un file PRIMARY KEYalla sua definizione. Quando si esegue questa operazione, è anche necessario dichiarare le colonne indicizzate comeNOT NULL, perché a PRIMARY KEY non permette NULL valori -

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)
);

La presenza di un indice univoco in una tabella normalmente causa un errore se si inserisce un record nella tabella che duplica un record esistente nella colonna o nelle colonne che definiscono l'indice.

Utilizzare il INSERT IGNORE comando piuttosto che il INSERTcomando. Se un record non duplica un record esistente, MySQL lo inserisce come al solito. Se il record è un duplicato, il fileIGNORE parola chiave dice a MySQL di scartarlo silenziosamente senza generare un errore.

L'esempio seguente non genera errori e allo stesso tempo non inserirà anche record duplicati.

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)

Utilizzare il REPLACEcomando piuttosto che il comando INSERT. Se il record è nuovo, viene inserito come con INSERT. Se è un duplicato, il nuovo record sostituisce quello vecchio.

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)

I comandi INSERT IGNORE e REPLACE devono essere scelti in base al comportamento di gestione dei duplicati che si desidera applicare. Il comando INSERT IGNORE mantiene il primo set di record duplicati e scarta i restanti. Il comando REPLACE mantiene l'ultimo set di duplicati e cancella quelli precedenti.

Un altro modo per rafforzare l'unicità è aggiungere un file UNIQUE indice piuttosto che una CHIAVE PRIMARIA a una tabella.

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

Conteggio e identificazione dei duplicati

Di seguito è riportata la query per contare i record duplicati con first_name e last_name in una tabella.

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

Questa query restituirà un elenco di tutti i record duplicati nella tabella person_tbl. In generale, per identificare gli insiemi di valori duplicati, seguire i passaggi indicati di seguito.

  • Determina quali colonne contengono i valori che possono essere duplicati.

  • Elenca quelle colonne nell'elenco di selezione delle colonne, insieme al file COUNT(*).

  • Elenca le colonne nel file GROUP BY clausola pure.

  • Aggiungere un HAVING clausola che elimina i valori univoci richiedendo che i conteggi del gruppo siano maggiori di uno.

Eliminazione dei duplicati dal risultato di una query

Puoi usare il file DISTINCT insieme all'istruzione SELECT per scoprire i record univoci disponibili in una tabella.

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

Un'alternativa al comando DISTINCT consiste nell'aggiungere una clausola GROUP BY che denomina le colonne che si stanno selezionando. Ciò ha l'effetto di rimuovere i duplicati e selezionare solo le combinazioni univoche di valori nelle colonne specificate.

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

Rimozione dei duplicati utilizzando la sostituzione della tabella

Se hai record duplicati in una tabella e desideri rimuovere tutti i record duplicati da quella tabella, segui la procedura indicata di seguito.

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;

Un modo semplice per rimuovere i record duplicati da una tabella consiste nell'aggiungere un INDICE o una CHIAVE PRIMARIA a quella tabella. Anche se questa tabella è già disponibile, puoi utilizzare questa tecnica per rimuovere i record duplicati e sarai al sicuro anche in futuro.

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