Acest articol arată cum să găsiți rânduri duplicate într-un tabel de baze de date. Aceasta este o întrebare foarte frecventă pentru începători. Tehnica de bază este simplă. Voi arăta, de asemenea, câteva variante, cum ar fi cum să găsești „duplicate în două coloane” (o întrebare recentă pe canalul IRC #mysql).
Cum să găsești rânduri duplicate
Primul pas este să definiți ce anume face dintr-un rând un duplicat al unui alt rând. De cele mai multe ori acest lucru este ușor: au aceeași valoare într-o coloană. O voi lua ca o definiție de lucru pentru acest articol, dar este posibil trebuie să modificați interogările de mai jos dacă noțiunea dvs. de „duplicat” este mai complicată.
Pentru acest articol, voi folosi acest exemplu de date:
Primele două rânduri au aceleași valoare din coloana day, deci dacă consider că acestea sunt duplicate, iată o interogare pentru a le găsi. Interogarea utilizează o clauză GROUP BY pentru a pune toate rândurile cu aceeași valoare day într-un singur „grup” și apoi să numere dimensiunea group:
Rândurile duplicate au un număr mai mare de unul. Dacă doriți doar să vedeți rândurile care sunt duplicate, trebuie să utilizați un HAVING clauză (nu o clauză WHERE), astfel:
Aceasta este tehnica de bază: grupați după coloana care conține duplicate și afișați numai acele grupuri care au mai mult de un rând.
De ce nu puteți utiliza o clauză WHERE?
A Clauza WHERE filtrează rândurile înainte ca acestea să fie grupate împreună. O clauză HAVING le filtrează după grupare. De aceea nu puteți utiliza un WHERE clauză în interogarea de mai sus.
Cum se șterg rânduri duplicate
O întrebare legată este cum să ștergeți rândurile „duplicate” odată ce găsește-le.Un t obișnuit întrebați când curățați datele greșite este să ștergeți toate, cu excepția unuia dintre duplicate, astfel încât să puteți pune indexurile și cheile principale pe masă și să împiedicați duplicatele să intre din nou în tabel.
Din nou, primul lucru de făcut este să vă asigurați că definiția dvs. este clară. Exact ce rând doriți să păstrați? Primul? Cea cu cea mai mare valoare a unei coloane? Pentru acest articol, presupun că doriți să păstrați „primul” rând – cel cu cea mai mică valoare a coloanei id. Asta înseamnă că doriți să ștergeți fiecare rând.
Probabil cel mai simplu mod de a face acest lucru este cu un tabel temporar. Mai ales în MySQL, există unele restricții privind selectarea dintr-un tabel și actualizarea acestuia în aceeași interogare. Puteți să le ocoliți, așa cum explic în articolul meu Cum să selectați dintr-o țintă de actualizare în MySQL, dar voi evita aceste complicații și voi folosi un tabel temporar.
Definiția exactă a sarcinii este pentru a șterge fiecare rând care are un duplicat, cu excepția rândului cu valoarea minimă id pentru grupul respectiv. Deci, trebuie să găsiți nu numai rândurile în care există mai multe din grup, ci trebuie să găsiți și rândul pe care doriți să îl păstrați. Puteți face acest lucru cu funcția MIN(). Iată câteva interogări pentru a crea tabelul temporar și pentru a găsi datele de care aveți nevoie pentru a face DELETE:
Acum că aveți aceste date, puteți continua să ștergeți rândurile „rele”. Există multe modalități de a face acest lucru, iar unele sunt mai bune decât altele (a se vedea articolul meu despre problemele multiple în SQL), dar din nou voi evita punctele mai fine și vă voi arăta doar o sintaxă standard care ar trebui să funcționeze în orice RDBMS care acceptă subinterogări:
Dacă RDBMS nu acceptă subinterogări sau dacă este mai eficient, poate doriți să faceți o ștergere pe mai multe tabele. Sintaxa pentru aceasta variază între sisteme, deci trebuie să consultați documentația sistemului. Este posibil să fie necesar să faceți toate acestea într-o tranzacție pentru a evita ca alți utilizatori să schimbe datele în timp ce lucrați, dacă aceasta este o problemă.
Cum să găsiți duplicate în mai multe coloane
Cineva a pus recent o întrebare similară cu aceasta pe canalul IRC #mysql:
Am un tabel cu coloane
bșiccare leagă alte două tabelebșicși vreau pentru a găsi toate rândurile care au duplicate fie înb, fie înc.
A fost dificil să înțelegem exact ce înseamnă acest lucru, dar după o conversație am înțeles-o: persoana dorea să poată pune indexuri unice pe coloanele b și c separat.
Este destul de ușor să găsiți rânduri cu valori duplicat într-una sau în cealaltă coloană, așa cum v-am arătat mai sus: grupați doar după colu-ul respectiv mn și numărați dimensiunea grupului. Și este ușor să găsiți rânduri întregi care sunt duplicate exacte ale altor rânduri: grupați doar câte coloane aveți nevoie.Dar este mai greu să identificați rândurile care au fie o valoare b duplicată, fie o valoare c duplicată. Luați următorul exemplu de tabel, care este aproximativ ceea ce a descris persoana respectivă:
Acum, puteți vedea cu ușurință că există câteva rânduri „duplicate” în acest tabel, dar niciun rând nu are de fapt același tuplu {b, c}. De aceea, acest lucru este puțin mai dificil de rezolvat.
Interogări care nu funcționează
Dacă grupați câte două coloane împreună, veți obține rezultate variate în funcție de modul în care grupați si numara. Acesta este locul în care utilizatorul IRC a fost obosit. Uneori, interogările ar găsi unele duplicate, dar nu altele. Iată câteva dintre lucrurile pe care le-a încercat această persoană:
Această interogare returnează fiecare rând din tabel, cu un COUNT(*) din 1, care pare a fi un comportament greșit, dar de fapt nu este. De ce? Deoarece > 1 se află în COUNT(). Este destul de ușor să ratați, dar această interogare este de fapt aceeași ca
De ce? Deoarece (b > 1) este o expresie booleană. Nu asta vrei deloc. Doriți
Aceasta returnează zero rânduri, desigur, deoarece nu există duplicate {b, c}. Persoana a încercat multe alte combinații de HAVING clauze și OR și AND, grupându-se după o coloană și numărând cealaltă și așa mai departe:
Totuși, nimic nu a găsit toate duplicatele. Ceea ce cred că a făcut-o cel mai frustrant este că a funcționat parțial, făcând persoana să creadă că este aproape o interogare potrivită … poate că o altă variantă ar putea să o obțină …
De fapt, este imposibil de făcut cu acest tip de interogare simplă GROUP BY. De ce asta? Acest lucru se datorează faptului că atunci când grupați câte o coloană, distribuiți ca valorile celeilalte coloane în mai multe grupuri. Puteți vedea acest lucru vizual ordonând după acele coloane, ceea ce face gruparea. Mai întâi, ordonați după coloana b și vedeți cum sunt grupate:
| 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 |
Când comandați (grupați) după coloana b, valorile duplicate din coloana c sunt distribuite în diferite grupuri, deci nu le puteți număra cu COUNT(DISTINCT c) așa cum încerca să facă persoana respectivă. Funcțiile cumulate cum ar fi COUNT() funcționează numai într-un grup și nu au acces la rândurile plasate în alte grupuri. În mod similar, atunci când comandați cu c, valorile duplicate din coloana b sunt distribuite în diferite grupuri. Nu este posibil să faceți această interogare să facă ceea ce doriți.
Unele soluții corecte
Probabil cea mai simplă soluție este să găsiți duplicatele pentru fiecare coloană separat și UNION le împreună, astfel:
Coloana what_col din ieșire indică în ce coloană s-a găsit valoarea duplicat. O altă abordare este de a utiliza subinterogări:
Aceasta este probabil mult mai puțin eficientă decât abordarea UNION și va afișa fiecare rând duplicat, nu doar valorile care sunt duplicate. O altă abordare este aceea de a face auto-îmbinări împotriva subinterogărilor grupate în clauza FROM. Acest lucru este mai complicat pentru a scrie corect, dar poate fi necesar pentru unele date complexe sau pentru eficiență:
Oricare dintre aceste interogări o va face și sunt sigur că există și alte modalități. Dacă puteți utiliza UNION, este probabil cel mai ușor.
