Questo articolo mostra come trovare righe duplicate in una tabella di database. Questa è una domanda per principianti molto comune. La tecnica di base è semplice. Mostrerò anche alcune varianti, come ad esempio come trovare “duplicati in due colonne” (una domanda recente sul canale IRC #mysql).
Come trovare righe duplicate
Il primo passo è definire cosa rende esattamente una riga un duplicato di un’altra riga. La maggior parte delle volte è facile: hanno lo stesso valore in alcune colonne. La prenderò come una definizione funzionante per questo articolo, ma potresti è necessario modificare le query seguenti se la tua nozione di “duplicato” è più complicata.
Per questo articolo, userò questi dati di esempio:
Le prime due righe hanno lo stesso valore nella colonna day, quindi se considero duplicati, ecco una query per trovarli. La query utilizza una clausola GROUP BY per inserire tutte le righe con lo stesso valore day in un “gruppo” e quindi contare la dimensione del gruppo:
Le righe duplicate hanno un conteggio maggiore di uno. Se desideri visualizzare solo le righe duplicate, devi utilizzare un HAVING clausola (non una WHERE clausola), come questa:
Questa è la tecnica di base: raggruppa per colonna che contiene duplicati e mostra solo quei gruppi che hanno più di una riga.
Perché non puoi usare una clausola WHERE?
A La clausola WHERE filtra le righe prima che vengano raggruppate. Una clausola HAVING le filtra dopo il raggruppamento. Ecco perché non puoi utilizzare un WHERE clausola nella query precedente.
Come eliminare le righe duplicate
Una domanda correlata è come eliminare le righe “duplicate” una volta trovarli. Un comune t chiedere quando si puliscono dati danneggiati è eliminare tutti i duplicati tranne uno, in modo da poter inserire gli indici e le chiavi primarie corretti nella tabella e impedire che i duplicati entrino di nuovo nella tabella.
Di nuovo, il primo la cosa da fare è assicurarsi che la tua definizione sia chiara. Esattamente quale riga vuoi mantenere? Il primo? Quello con il valore più grande di qualche colonna? Per questo articolo, presumo che tu voglia mantenere la “prima” riga, quella con il valore più piccolo della colonna id. Ciò significa che vuoi eliminare ogni altra riga.
Probabilmente il modo più semplice per farlo è con una tabella temporanea. Soprattutto in MySQL, ci sono alcune restrizioni sulla selezione da una tabella e l’aggiornamento nella stessa query. Puoi aggirarli, come spiego nel mio articolo Come selezionare da una destinazione di aggiornamento in MySQL, ma eviterò semplicemente queste complicazioni e userò una tabella temporanea.
La definizione esatta dell’attività è per eliminare ogni riga che ha un duplicato, eccetto la riga con il valore minimo di id per quel gruppo. Quindi devi trovare non solo le righe in cui ce n’è più di una nel gruppo, devi anche trovare la riga che desideri mantenere. Puoi farlo con la funzione MIN(). Di seguito sono riportate alcune query per creare la tabella temporanea e trovare i dati necessari per eseguire DELETE:
Ora che hai questi dati, puoi procedere con l’eliminazione le righe “cattive”. Ci sono molti modi per farlo, e alcuni sono migliori di altri (vedi il mio articolo sui problemi molti-a-uno in SQL), ma ancora una volta eviterò i punti più fini e ti mostrerò solo una sintassi standard che dovrebbe funzionare in qualsiasi RDBMS che supporti le sottoquery:
Se il tuo RDBMS non supporta le sottoquery, o se è più efficiente, potresti voler eseguire un’eliminazione multi-tabella. La sintassi varia da sistema a sistema, quindi è necessario consultare la documentazione del sistema. Potresti anche dover fare tutto questo in una transazione per evitare che altri utenti modifichino i dati mentre stai lavorando, se questo è un problema.
Come trovare duplicati in più colonne
Qualcuno ha recentemente posto una domanda simile a questa sul canale IRC #mysql:
Ho una tabella con colonne
becche collega altre due tabellebece desidero per trovare tutte le righe con duplicati inboc.
Era difficile capire esattamente cosa significasse, ma dopo un po ‘di conversazione l’ho capito: la persona voleva essere in grado di mettere indici univoci sulle colonne b e c separatamente.
È abbastanza facile trovare righe con valori duplicati in una o nell’altra colonna, come ti ho mostrato sopra: raggruppa solo per quella colonna mn e conta la dimensione del gruppo. Ed è facile trovare intere righe che sono duplicati esatti di altre righe: basta raggruppare per tutte le colonne di cui hai bisogno.Tuttavia, è più difficile identificare le righe che hanno un valore b duplicato o un valore c duplicato. Prendi la seguente tabella di esempio, che è più o meno ciò che la persona ha descritto:
Ora, puoi facilmente vedere che ci sono alcune righe “duplicate” in questa tabella, ma non ci sono due righe effettivamente la stessa tupla {b, c}. Ecco perché è un po ‘più difficile da risolvere.
Query che non funzionano
Se raggruppate per due colonne, otterrete risultati diversi a seconda di come raggruppate e contare. È qui che l’utente IRC veniva bloccato. A volte le query trovano alcuni duplicati ma non altri. Ecco alcune delle cose che questa persona ha provato:
Questa query restituisce ogni riga della tabella, con un COUNT(*) di 1, che sembra essere un comportamento sbagliato, ma in realtà non lo è. Perché? Perché > 1 si trova all’interno di COUNT(). È abbastanza facile da perdere, ma questa query è in realtà la stessa di
Perché? Perché (b > 1) è un’espressione booleana. Non è affatto quello che vuoi. Vuoi
Questo restituisce zero righe, ovviamente, perché non ci sono {b, c} tuple duplicate. La persona ha provato molte altre combinazioni di HAVING clausole e OR e AND, raggruppando per una colonna e contando l’altra e così via:
Tuttavia, non sono stati trovati tutti i duplicati. Quello che penso abbia reso più frustrante è che ha funzionato parzialmente, facendo pensare alla persona che fosse quasi la query giusta … forse solo un’altra variazione l’avrebbe ottenuta …
In effetti, è impossibile fare con questo tipo di semplice query GROUP BY. Perchè è questo? È perché quando raggruppate per una colonna, distribuite i valori simili dell’altra colonna su più gruppi. Puoi vederlo visivamente ordinando in base a quelle colonne, che è ciò che fa il raggruppamento. Per prima cosa, ordina per colonna b e guarda come sono raggruppate:
| 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 |
Quando ordini (gruppo) per colonna b, i valori duplicati nella colonna c sono distribuiti in diversi gruppi, quindi non puoi contarli con COUNT(DISTINCT c) come stava cercando di fare la persona. Le funzioni aggregate come COUNT() operano solo all’interno di un gruppo e non hanno accesso alle righe inserite in altri gruppi. Allo stesso modo, quando ordini per c, i valori duplicati nella colonna b vengono distribuiti in diversi gruppi. Non è possibile fare in modo che questa query esegua ciò che si desidera.
Alcune soluzioni corrette
Probabilmente la soluzione più semplice è trovare i duplicati per ciascuna colonna separatamente e UNION insieme, in questo modo:
La colonna what_col nell’output indica in quale colonna è stato trovato il valore duplicato. Un altro approccio consiste nell’utilizzare sottoquery:
Questo è probabilmente molto meno efficiente dell’approccio UNION e mostrerà ogni riga duplicata, non solo i valori che sono duplicati. Un altro approccio ancora consiste nell’effettuare auto-join contro sottoquery raggruppate nella clausola FROM. È più complicato scrivere correttamente, ma potrebbe essere necessario per alcuni dati complessi o per motivi di efficienza:
Qualsiasi di queste query andrà bene, e sono sicuro che ci sono anche altri modi. Se puoi utilizzare UNION, è probabilmente il più semplice.
