Denne artikkelen viser hvordan du finner dupliserte rader i en databasetabell. Dette er et veldig vanlig nybegynnerspørsmål. Den grunnleggende teknikken er grei. Jeg vil også vise noen varianter, for eksempel hvordan du finner «duplikater i to kolonner» (et nylig spørsmål på #mysql IRC-kanalen).
Hvordan finne dupliserte rader
Det første trinnet er å definere hva som gjør en rad til et duplikat av en annen rad. Det meste av tiden er dette enkelt: de har samme verdi i en eller annen kolonne. Jeg tar dette som en arbeidsdefinisjon for denne artikkelen, men du kan må du endre spørsmålene nedenfor hvis begrepet «duplikat» er mer komplisert.
For denne artikkelen vil jeg bruke disse eksempeldataene:
De to første radene har samme verdi i day -kolonnen, så hvis jeg anser de som duplikater, er det et spørsmål om å finne dem. Spørringen bruker en GROUP BY -klausul for å plassere alle radene med den samme day -verdien i en «gruppe» og deretter telle størrelsen på gruppe:
De dupliserte radene har et antall større enn en. Hvis du bare vil se rader som er dupliserte, må du bruke en HAVING klausul (ikke en WHERE klausul), slik:
Dette er den grunnleggende teknikken: gruppere etter kolonnen som inneholder duplikater, og viser bare de gruppene som har mer enn en rad.
Hvorfor kan du ikke bruke en WHERE-setning?
A WHERE klausul filtrerer radene før de grupperes. En HAVING klausul filtrerer dem etter gruppering. Derfor kan du ikke bruke en WHERE klausul i spørringen ovenfor.
Slik sletter du dupliserte rader
Et beslektet spørsmål er hvordan du sletter «dupliserte» rader når du finn dem. En vanlig t spør når du rydder opp med dårlige data, er å slette alle duplikatene unntatt en, slik at du kan sette riktige indekser og primærnøkler på bordet, og forhindre at duplikater kommer inn i tabellen igjen.
Igjen, den første ting å gjøre er å sørge for at definisjonen din er klar. Nøyaktig hvilken rad vil du beholde? Den første? Den med den største verdien av en kolonne? For denne artikkelen antar jeg at du vil beholde den første raden – den med den minste verdien i id -kolonnen. Det betyr at du vil slette annenhver rad.
Den enkleste måten å gjøre dette på er sannsynligvis med en midlertidig tabell. Spesielt i MySQL er det noen begrensninger for å velge fra en tabell og oppdatere den i samme spørring. Du kan komme deg rundt disse, som jeg forklarte i artikkelen min Hvordan velge fra et oppdateringsmål i MySQL, men jeg vil bare unngå disse komplikasjonene og bruke en midlertidig tabell.
Den eksakte definisjonen av oppgaven er å slette hver rad som har duplikat, bortsett fra raden med den minimale verdien id for den gruppen. Så du må ikke bare finne radene der det er mer enn en i gruppen, du må også finne raden du vil beholde. Du kan gjøre det med MIN() -funksjonen. Her er noen spørsmål for å lage den midlertidige tabellen og finne dataene du trenger for å gjøre DELETE:
Nå som du har disse dataene, kan du fortsette å slette de ‘dårlige’ radene. Det er mange måter å gjøre dette på, og noen er bedre enn andre (se artikkelen min om mange-til-en-problemer i SQL), men igjen vil jeg unngå de finere punktene og bare vise deg en standard syntaks som burde fungere i eventuelle RDBMS som støtter underforespørsler:
Hvis RDBMS ikke støtter underforespørsler, eller hvis det er mer effektivt, kan det være lurt å slette flertabeller. Syntaksen for dette varierer mellom systemene, så du må lese systemets dokumentasjon. Du kan også trenge å gjøre alt dette i en transaksjon for å unngå at andre brukere endrer dataene mens du jobber, hvis det er et problem.
Hvordan finne duplikater i flere kolonner
Noen spurte nylig et spørsmål som ligner på dette på #mysql IRC-kanalen:
Jeg har en tabell med kolonner
bogcsom lenker to andre tabellerbogc, og jeg vil for å finne alle rader som har duplikater i entenbellerc.
Det var vanskelig å forstå nøyaktig hva dette betydde, men etter en samtale grep jeg det: personen ønsket å kunne sette unike indekser på kolonner b og c hver for seg.
Det er ganske enkelt å finne rader med dupliserte verdier i den ene eller den andre kolonnen, som jeg viste deg ovenfor: bare grupper etter den kolonnen mn og tell gruppestørrelsen. Og det er enkelt å finne hele rader som er eksakte duplikater av andre rader: bare gruppere etter så mange kolonner som du trenger.Men det er vanskeligere å identifisere rader som enten har en duplisert b -verdi eller en duplisert c -verdi. Ta følgende eksempeltabell, som omtrent er hva personen beskrev:
Nå kan du enkelt se at det er noen ‘dupliserte’ rader i denne tabellen, men ingen to rader har faktisk den samme tupelen {b, c}. Derfor er dette litt vanskeligere å løse.
Spørringer som ikke fungerer
Hvis du grupperer etter to kolonner sammen, får du forskjellige resultater, avhengig av hvordan du grupperer og telle. Det er her IRC-brukeren ble stubbet. Noen ganger kan spørsmål finne noen duplikater, men ikke andre. Her er noen av tingene denne personen prøvde:
Dette spørsmålet returnerer hver rad i tabellen, med en COUNT(*) av 1, som ser ut til å være feil oppførsel, men det er det faktisk ikke. Hvorfor? Fordi > 1 er inne i COUNT(). Det er ganske lett å gå glipp av, men dette spørsmålet er faktisk det samme som
Hvorfor? Fordi (b > 1) er et boolsk uttrykk. Det er ikke det du vil ha i det hele tatt. Du vil
Dette returnerer selvfølgelig null rader, fordi det ikke er duplikat {b, c} tupler. Personen prøvde mange andre kombinasjoner av HAVING ledd og ORs og ANDs, gruppert etter en kolonne og talt den andre, og så videre:
Ingenting fant imidlertid alle duplikatene. Det jeg tror gjorde det mest frustrerende er at det delvis virket, noe som fikk personen til å tro at det var nesten det rette spørsmålet … kanskje bare en annen variant ville få det …
Det er faktisk umulig å gjøre med denne typen enkel GROUP BY forespørsel. Hvorfor er det sånn? Det er fordi når du grupperer etter en kolonne, fordeler du like verdier i den andre kolonnen over flere grupper. Du kan se dette visuelt ved å bestille etter disse kolonnene, det er hva gruppering gjør. Først bestiller du etter kolonne b og ser hvordan de er gruppert:
| 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 bestiller (gruppe) etter kolonne b, dupliseres verdiene i kolonne c er fordelt i forskjellige grupper, så du kan ikke telle dem med COUNT(DISTINCT c) slik personen prøvde å gjøre. Aggregerte funksjoner som COUNT() fungerer bare i en gruppe, og har ikke tilgang til rader som er plassert i andre grupper. På samme måte, når du bestiller etter c, fordeles duplikatverdiene i kolonne b i forskjellige grupper. Det er ikke mulig å få dette spørsmålet til å gjøre det som ønskes.
Noen riktige løsninger
Den enkleste løsningen er sannsynligvis å finne duplikatene for hver kolonne separat og UNION dem sammen, slik:
what_col -kolonnen i utgangen indikerer hvilken kolonne duplikatverdien ble funnet i. En annen tilnærming er å bruke underforespørsler:
Dette er sannsynligvis mye mindre effektivt enn UNION -tilnærmingen, og vil vise hver dupliserte rad, ikke bare verdiene som dupliseres. Nok en tilnærming er å gjøre selvtilslutninger mot grupperte underspørsler i FROM klausulen. Dette er mer komplisert å skrive riktig, men det kan være nødvendig for kompliserte data eller for effektivitet:
Noen av disse spørsmålene vil gjøre det, og jeg er sikker på at det også er andre måter. Hvis du kan bruke UNION, er det sannsynligvis det enkleste.
