Den här artikeln visar hur du hittar dubblerade rader i en databastabell. Detta är en mycket vanlig nybörjare fråga. Grundtekniken är enkel. Jag visar också några variationer, till exempel hur man hittar ”dubbletter i två kolumner” (en ny fråga på #mysql IRC-kanalen).
Hur man hittar dubblerade rader
Det första steget är att definiera vad som exakt gör en rad till en duplikat av en annan rad. För det mesta är det enkelt: de har samma värde i någon kolumn. Jag tar detta som en arbetsdefinition för den här artikeln, men du kanske måste ändra frågorna nedan om din uppfattning om ”duplikat” är mer komplicerad.
För den här artikeln använder jag denna exempeldata:
De två första raderna har samma värde i kolumnen day, så om jag anser att de är dubbletter, här är en fråga för att hitta dem. Frågan använder en GROUP BY -sats för att placera alla rader med samma day -värde i en ”grupp” och sedan räkna storleken på grupp:
De dubblerade raderna har ett antal som är större än en. Om du bara vill se rader som dupliceras måste du använda ett HAVING klausul (inte en WHERE klausul), så här:
Detta är den grundläggande tekniken: gruppera efter kolumnen som innehåller dubbletter och visa bara de grupper som har mer än en rad.
Varför kan du inte använda en WHERE-sats?
A WHERE klausul filtrerar raderna innan de grupperas tillsammans. En HAVING klausul filtrerar dem efter gruppering. Det är därför du inte kan använda en WHERE klausul i ovanstående fråga.
Hur man tar bort dubbletterader
En relaterad fråga är hur man tar bort ”duplicera” -rader när du hitta dem. En vanlig t fråga när du rensar dåliga data är att ta bort alla dubbletterna utom en, så att du kan lägga rätt index och primära nycklar på bordet och förhindra att dubbletter kommer in i tabellen igen. att göra är att se till att din definition är tydlig. Exakt vilken rad vill du behålla? Den första? Den med det största värdet i någon kolumn? För den här artikeln antar jag att du vill behålla den första raden – den med det minsta värdet i kolumnen id. Det betyder att du vill ta bort varannan rad.
Förmodligen är det enklaste sättet att göra det med en tillfällig tabell. Speciellt i MySQL finns det vissa begränsningar för att välja från en tabell och uppdatera den i samma fråga. Du kan komma runt dessa, som jag förklarar i min artikel Hur man väljer från ett uppdateringsmål i MySQL, men jag ska bara undvika dessa komplikationer och använda en tillfällig tabell.
Den exakta definitionen av uppgiften är att radera varje rad som har en duplikat, förutom raden med det minsta värdet id för den gruppen. Så du behöver inte bara hitta rader där det finns fler än en i gruppen, du måste också hitta raden du vill behålla. Du kan göra det med funktionen MIN(). Här är några frågor för att skapa den tillfälliga tabellen och hitta de data du behöver för att göra DELETE:
Nu när du har dessa data kan du fortsätta att radera de ’dåliga’ raderna. Det finns många sätt att göra detta, och vissa är bättre än andra (se min artikel om många-till-en-problem i SQL), men igen kommer jag att undvika de finare punkterna och bara visa en standardsyntax som borde fungera i alla RDBMS som stöder underfrågor:
Om din RDBMS inte stöder underfrågor, eller om den är effektivare, kan du ta bort flera tabeller. Syntaksen för detta varierar mellan systemen, så du måste läsa systemets dokumentation. Du kan också behöva göra allt detta i en transaktion för att undvika att andra användare ändrar data medan du arbetar, om det är ett problem.
Hur man hittar dubbletter i flera kolumner
Någon ställde nyligen en liknande fråga på IRC-kanalen #mysql:
Jag har en tabell med kolumner
bochcsom länkar två andra tabellerbochc, och jag vill för att hitta alla rader som har dubbletter i antingenbellerc.
Det var svårt att förstå exakt vad detta innebar, men efter ett samtal fattade jag det: personen ville kunna sätta unika index på kolumner b och c separat.
Det är ganska enkelt att hitta rader med dubbla värden i en eller annan kolumn, som jag visade dig ovan: bara gruppera efter den kolumnen mn och räkna gruppstorleken. Och det är enkelt att hitta hela rader som är exakta dubbletter av andra rader: gruppera bara efter så många kolumner som du behöver.Men det är svårare att identifiera rader som antingen har ett duplicerat b -värde eller ett duplicerat c -värde. Ta följande provtabell, vilket är ungefär vad personen beskrev:
Nu kan du enkelt se att det finns några ”dubbla” rader i denna tabell, men inga två rader har faktiskt samma tupel {b, c}. Det är därför det är lite svårare att lösa.
Frågor som inte fungerar
Om du grupperar i två kolumner får du olika resultat beroende på hur du grupperar och räkna. Det var här IRC-användaren fick stubbar. Ibland kan frågor hitta några dubbletter men inte andra. Här är några av de saker som den här personen försökte:
Den här frågan returnerar varje rad i tabellen med en COUNT(*) av 1, vilket verkar vara fel beteende, men det är det faktiskt inte. Varför? Eftersom > 1 finns i COUNT(). Det är ganska lätt att missa, men denna fråga är faktiskt densamma som
Varför? Eftersom (b > 1) är ett booleskt uttryck. Det är inte vad du vill alls. Du vill
Detta returnerar naturligtvis noll rader eftersom det inte finns några dubbletter {b, c} tuples. Personen försökte många andra kombinationer av HAVING satser och ORs och ANDs, grupperade efter en kolumn och räknade den andra, och så vidare:
Ingenting hittade dock alla dubbletter. Vad jag tycker gjorde det mest frustrerande är att det delvis fungerade, vilket fick personen att tro att det var nästan rätt fråga … kanske bara en annan variant skulle få det …
Det är faktiskt omöjligt att göra med denna typ av enkel GROUP BY fråga. Varför är detta? Det beror på att när du grupperar efter en kolumn fördelar du samma värden som den andra kolumnen över flera grupper. Du kan se detta visuellt genom att ordna efter dessa kolumner, vilket gruppering gör. Beställ först efter kolumn b och se hur de är grupperade:
| 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 |
När du beställer (grupp) efter kolumn b, dubblerar värdena i kolumn c fördelas i olika grupper, så du kan inte räkna dem med COUNT(DISTINCT c) som personen försökte göra. Aggregerade funktioner som COUNT() fungerar bara inom en grupp och har ingen tillgång till rader som placeras i andra grupper. På samma sätt, när du beställer efter c fördelas dubblettvärdena i kolumn b i olika grupper. Det är inte möjligt att få denna fråga att göra vad som önskas.
Några korrekta lösningar
Förmodligen är den enklaste lösningen att hitta duplikaten för varje kolumn separat och UNION dem tillsammans, så här:
Kolumnen what_col i utdata visar vilken kolumn det dubbla värdet hittades i. Ett annat tillvägagångssätt är att använda underfrågor:
Detta är förmodligen mycket mindre effektivt än UNION -metoden och kommer att visa varje duplicerad rad, inte bara de värden som dupliceras. Ännu ett tillvägagångssätt är att göra självanslutningar mot grupperade underfrågor i FROM -satsen. Det här är mer komplicerat för att skriva korrekt, men kan vara nödvändigt för vissa komplexa data eller för effektivitet:
Någon av dessa frågor kommer att göra det, och jag är säker på att det också finns andra sätt. Om du kan använda UNION är det förmodligen det enklaste.
