MySQLi - Gestione dei duplicati

Le tabelle o i set di risultati a volte contengono record duplicati. A volte è consentito, ma a volte è necessario interrompere i record duplicati. A volte, è necessario identificare i record duplicati e rimuoverli dalla tabella. Questo capitolo descriverà come impedire la presenza di record duplicati in una tabella e come rimuovere record duplicati già esistenti.

Prevenire la presenza di duplicati in una tabella

Puoi usare un file PRIMARY KEY o UNIQUEIndice su una tabella con campi appropriati per interrompere i record duplicati. Facciamo un esempio: la seguente tabella 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, aggiungere una CHIAVE PRIMARIA alla relativa definizione. Quando si esegue questa operazione, è anche necessario dichiarare le colonne indicizzate come NOT NULL, perché una PRIMARY KEY non consente valori 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)
);

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.

Uso INSERT IGNORE piuttosto che INSERT. Se un record non duplica un record esistente, MySQLi lo inserisce come al solito. Se il record è un duplicato, la parola chiave IGNORE dice a MySQLi di eliminarlo silenziosamente senza generare un errore.

L'esempio seguente non genera errori e allo stesso tempo non inserirà 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)

Uso REPLACEpiuttosto che 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)

INSERT IGNORE e REPLACE dovrebbero essere scelti in base al comportamento di gestione dei duplicati che si desidera applicare. INSERISCI IGNORA conserva il primo di una serie di record duplicati e scarta il resto. SOSTITUISCI conserva l'ultimo di una serie di duplicati e cancella quelli precedenti.

Un altro modo per imporre l'unicità consiste nell'aggiungere un indice UNICO anziché 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, eseguire le seguenti operazioni:

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

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

  • Elenca anche le colonne nella clausola GROUP BY.

  • Aggiungere una clausola HAVING che elimina i valori univoci richiedendo che i conteggi dei gruppi siano maggiori di uno.

Eliminazione dei duplicati dal risultato di una query:

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

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

Un'alternativa a DISTINCT consiste nell'aggiungere una clausola GROUP BY che denomina le colonne che stai 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, ecco la 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;

Un modo semplice per rimuovere i record duplicati da una tabella consiste nell'aggiungere un INDICE o una CHIAVE PRINCIPALE 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);