Denne artikel viser, hvordan du finder duplikerede rækker i en databasetabel. Dette er et meget almindeligt nybegynderspørgsmål. Den grundlæggende teknik er ligetil. Jeg vil også vise nogle variationer, såsom hvordan man finder “duplikater i to kolonner” (et nyligt spørgsmål på #mysql IRC-kanalen).
Sådan finder du duplikerede rækker
Det første trin er at definere, hvad der nøjagtigt gør en række til en duplikat af en anden række. Det meste af tiden er det let: de har samme værdi i en eller anden kolonne. Jeg tager dette som en funktionsdefinition for denne artikel, men du kan har brug for at ændre spørgsmålene nedenfor, hvis din opfattelse af “duplikat” er mere kompliceret.
Til denne artikel bruger jeg disse eksempeldata:
De første to rækker har de samme værdi i kolonnen day, så hvis jeg betragter dem som duplikater, er der en forespørgsel for at finde dem. Forespørgslen bruger en GROUP BY -klausul til at placere alle rækkerne med den samme day -værdi i en “gruppe” og derefter tælle størrelsen på gruppe:
De duplikerede rækker har et antal, der er større end en. Hvis du kun vil se rækker, der er duplikerede, skal du bruge et HAVING klausul (ikke en WHERE klausul), som denne:
Dette er den grundlæggende teknik: gruppér efter kolonnen, der indeholder duplikater, og viser kun de grupper, der har mere end en række.
Hvorfor kan du ikke bruge en WHERE-klausul?
A WHERE klausul filtrerer rækkerne, før de grupperes sammen. En HAVING klausul filtrerer dem efter gruppering. Derfor kan du ikke bruge en WHERE klausul i ovenstående forespørgsel.
Sådan slettes dublerede rækker
Et relateret spørgsmål er, hvordan man sletter de ‘duplikerede’ rækker, når du find dem. En fælles t spørg når rensning af dårlige data er at slette alle undtagen en af dubletterne, så du kan lægge korrekte indekser og primære nøgler på bordet og forhindre, at dubletter kommer ind i tabellen igen.
Igen, den første ting at gøre er at sikre, at din definition er klar. Præcis hvilken række vil du beholde? Den første? Den med den største værdi af en eller anden kolonne? I denne artikel antager jeg, at du vil beholde den ‘første’ række – den med den mindste værdi i kolonnen id. Det betyder, at du vil slette hver anden række.
Den nemmeste måde at gøre dette på er sandsynligvis med en midlertidig tabel. Især i MySQL er der nogle begrænsninger med hensyn til at vælge fra en tabel og opdatere den i samme forespørgsel. Du kan omgå disse, som jeg forklarer i min artikel Sådan vælger du fra et opdateringsmål i MySQL, men jeg vil bare undgå disse komplikationer og bruge en midlertidig tabel.
Den nøjagtige definition af opgaven er at slette hver række, der har en duplikat, undtagen rækken med den minimale værdi id for den gruppe. Så du skal ikke kun finde de rækker, hvor der er mere end en i gruppen, du skal også finde den række, du vil beholde. Du kan gøre det med funktionen MIN(). Her er nogle forespørgsler for at oprette den midlertidige tabel og finde de data, du har brug for for at gøre DELETE:
Nu hvor du har disse data, kan du fortsætte med at slette de ‘dårlige’ rækker. Der er mange måder at gøre dette på, og nogle er bedre end andre (se min artikel om mange-til-en-problemer i SQL), men igen undgår jeg de finere punkter og viser dig bare en standardsyntaks, der burde arbejde i alle RDBMS, der understøtter underforespørgsler:
Hvis din RDBMS ikke understøtter underforespørgsler, eller hvis det er mere effektivt, kan du muligvis foretage en sletning af flere tabeller. Syntaksen for dette varierer fra system til system, så du skal konsultere dokumentationen til dit system. Det kan også være nødvendigt at gøre alt dette i en transaktion for at undgå, at andre brugere ændrer data, mens du arbejder, hvis det er et problem.
Sådan finder du duplikater i flere kolonner
Nogen stillede for nylig et spørgsmål svarende til dette på #mysql IRC-kanalen:
Jeg har en tabel med kolonner
bogcder linker to andre tabellerbogc, og jeg vil have for at finde alle rækker, der har dubletter i entenbellerc.
Det var svært at forstå nøjagtigt, hvad dette betød, men efter en samtale forstod jeg det: personen ønskede at være i stand til at placere unikke indekser på kolonner b og c separat.
Det er ret nemt at finde rækker med duplikatværdier i den ene eller den anden kolonne, som jeg viste dig ovenfor: gruppér bare efter den colu mn og tæl gruppestørrelsen. Og det er nemt at finde hele rækker, der er nøjagtige duplikater af andre rækker: gruppér bare efter så mange kolonner, som du har brug for.Men det er sværere at identificere rækker, der enten har en duplikeret b -værdi eller en duplikeret c -værdi. Tag følgende eksempeltabel, hvilket omtrent er, hvad personen beskrev:
Nu kan du nemt se, at der er nogle ‘duplikerede’ rækker i denne tabel, men ingen to rækker har faktisk den samme tuple {b, c}. Derfor er dette lidt sværere at løse.
Forespørgsler, der ikke fungerer
Hvis du grupperer efter to kolonner sammen, får du forskellige resultater afhængigt af hvordan du grupperer og tæl. Det var her IRC-brugeren blev stumpet. Undertiden finder forespørgsler nogle duplikater, men ikke andre. Her er nogle af de ting, denne person prøvede:
Denne forespørgsel returnerer hver række i tabellen med et COUNT(*) af 1, hvilket synes at være forkert opførsel, men det er det faktisk ikke. Hvorfor? Fordi > 1 er inde i COUNT(). Det er ret let at gå glip af, men denne forespørgsel er faktisk den samme som
Hvorfor? Fordi (b > 1) er et boolsk udtryk. Det er slet ikke, hvad du vil have. Du vil have
Dette returnerer selvfølgelig nul rækker, fordi der ikke er nogen duplikat {b, c} tupler. Personen prøvede mange andre kombinationer af HAVING klausuler og OR’er og AND’er, grupperet efter en kolonne og talt den anden osv.:
Intet fandt dog alle duplikaterne. Det jeg synes gjorde det mest frustrerende er, at det delvist fungerede, hvilket fik personen til at tro, at det næsten var den rigtige forespørgsel … måske ville bare en anden variation få det …
Faktisk er det umuligt at gøre med denne type enkel GROUP BY forespørgsel. Hvorfor er det? Det skyldes, at når du grupperer efter en kolonne, fordeler du samme værdier som den anden kolonne på tværs af flere grupper. Du kan se dette visuelt ved at bestille efter disse kolonner, hvilket gruppering gør. Bestil først efter kolonne b og se, hvordan de er grupperet:
| 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) efter kolonne b, duplikeres værdierne i kolonne c fordeles i forskellige grupper, så du kan ikke tælle dem med COUNT(DISTINCT c), som personen forsøgte at gøre. Samlede funktioner såsom COUNT() fungerer kun inden for en gruppe og har ingen adgang til rækker, der er placeret i andre grupper. På samme måde, når du bestiller efter c, fordeles de dobbelte værdier i kolonne b i forskellige grupper. Det er ikke muligt at få denne forespørgsel til at gøre det, der ønskes.
Nogle korrekte løsninger
Den enkleste løsning er sandsynligvis at finde duplikaterne for hver kolonne separat og UNION dem sammen som denne:
what_col -kolonnen i output angiver, i hvilken kolonne den dobbelte værdi blev fundet. En anden tilgang er at bruge underforespørgsler:
Dette er sandsynligvis meget mindre effektiv end UNION tilgangen, og viser hver duplikeret række, ikke kun de værdier, der duplikeres. Endnu en anden tilgang er at udføre selvtilslutninger mod grupperede underforespørgsler i FROM -klausulen. Dette er mere kompliceret at skrive korrekt, men det kan være nødvendigt for nogle komplekse data eller for effektivitet:
Enhver af disse forespørgsler gør det, og jeg er sikker på, at der også er andre måder. Hvis du kan bruge UNION, er det sandsynligvis det nemmeste.
