Eliminarea duplicate din tabelul

sarcină
Doriți să eliminați duplicate din tabel pentru a conține numai înregistrările unice.

decizie
Selectați rânduri unice dintr-un tabel la o altă masă și să o înlocuiască originalul. Sau adăugați un index unic la un tabel folosind ALTER TABLE, rezultând în duplicate dispar. Sau se aplică DELETE. LIMIT n pentru a elimina toate, dar una dintre copiile rândurilor duplicate.

discuție
La crearea unui tabel, ați uitat să definească un index unic pentru a preveni duplicatele în tabel, apoi, în viitor, vă mozhetestolknutsya cu necesitatea de a obține într-un fel scăpa de duplicate. Tabelul cat_mailing din exemplele din secțiunile anterioare se referă doar la o astfel de masă, deoarece conține mai multe înregistrări ale aceluiași popor?

mysql> SELECT * FROM cat_mailing ORDER BY nume, prenume;

Tabelul conține date redundante, și o idee bună pentru a le elimina pentru a evita re-distribuție și de a reduce costurile poștale. Există mai multe modalități de a face acest lucru:

• Selectați un rând unic al mesei la o altă masă, apoi utilizați noul tabel pentru înlocuirea originalului. Ca rezultat, duplicate vor fi șterse. Această metodă este adecvată atunci când un duplicat înțeles linii complet de potrivire.

• Adăugați la masa un index unic prin utilizarea ALTER TABLE. Această operațiune elimină rânduri duplicat pe baza conținutului coloanelor indexate.

• dubluri ale unui anumit set de rânduri duplicat pot fi șterse cu ajutorul ofertelor de ștergere.

LIMIT n, șterge toate liniile cu excepția uneia.

În această secțiune, toate aceste metode sunt discutate în detaliu. Ma gandesc la care unul pentru a alege, în anumite condiții, amintiți-vă că aplicabilitatea metodei la o anumită sarcină este adesea determinată de răspunsurile la două întrebări:

• Am nevoie de a avea un index unic al mesei?

• Dacă coloana în care sunt duplicate, permite utilizarea NULL, dacă metoda va elimina valorile NULL duplicat?

Eliminarea tabel duplicat prin înlocuirea
O modalitate de a elimina duplicate dintr-un tabel este selecție unică de înregistrări din noul tabel având aceeași structură. Apoi, tabelul original este înlocuit cu unul nou. În cazul în care o linie este considerată a fi un duplicat al unui alt rând numai în cazul în care acestea sunt potrivite complet, acesta poate fi utilizat pentru a selecta rânduri unice SELECT DISTINCT:

mysql> CREATE TABLE tmp SELECT * DIN cat_mailing DISTINCT;
mysql> SELECT * FROM COMANDA tmp nume, prenume;

Această metodă funcționează în absența indicelui (deși poate fi lent pentru tabele mari), dar tabelele care conțin valori NULL duplicat, elimină aceste duplicate. Rețineți că, în acest caz, valori ușor diferite pentru linia de stradă Wallace Baxter considerat diferit.

În cazul în care duplicatele sunt determinate numai în raport cu un subset de coloane într-un tabel, a crea un nou tabel care are un index unic, și apoi selectați-l în linie folosind INSERT IGNORE.

mysql> CREATE TABLE tmp (
-> LAST_NAME CHAR (40) NOT NULL,
-> FIRST_NAME CHAR (40) NOT NULL,
-> Street CHAR (40) NOT NULL,
-> PRIMARY KEY (nume, prenume));
mysql> INSERT INTO IGNORE tmp SELECT * FROM cat_mailing;
mysql> SELECT * FROM COMANDA tmp nume, prenume;

Indicele nu permite introducerea de înregistrări cu valori duplicate în cheie tmp și IGNORE indică faptul MySQL, care nu ar trebui să fie încheiată cu o eroare în cazul detectării duplicat. Dezavantajul este că, dacă coloanele indexate pot conține valori NULL, este necesar să se utilizeze un index în loc să PRIMARY KEY UNIQUE și apoi sunt eliminate din duplicat chei valori NULL (index unic permite mai multe valori NULL).

După crearea unui nou tabel tmp, care conține numai rânduri unice, l utilizați pentru a înlocui masa cat_mailing originală. Ca rezultat, cat_mailing nu va mai conține duplicate:

mysql> cat_mailing DROP TABLE;
mysql> ALTER TABLE tmp Redenumiți cat_mailing;

Eliminarea duplicat prin adăugarea indicelui
Pentru a elimina duplicate dintr-un tabel direct „de pe site-ul“, se adaugă în tabel un index unic prin utilizarea ALTER TABLE, folosind cuvântul cheie IGNORE pentru a indica necesitatea de a șterge înregistrările cu valori duplicate pentru cheie în procesul de construire a indicelui. Cat_mailing tabelul sursă fără indicele este după cum urmează:

mysql> SELECT * FROM cat_mailing ORDER BY nume, prenume;

Adăugați un index unic și a vedea modul în care aceasta va afecta conținutul tabelului:

mysql> ALTER IGNORE cat_mailing TABLE
-> ADD PRIMARY KEY (nume, prenume);
mysql> SELECT * FROM cat_mailing ORDER BY nume, prenume;

În cazul în care coloanele indexate pot conține valori NULL, în locul indicelui PRIMARY KEY trebuie să utilizați unică. Apoi, indexul nu elimină valorile NULL duplicat.

Ștergerea unui șir specific de duplicate
Incepand cu MySQL versiunea 3.22.7, puteți utiliza instrucțiunea LIMIT pentru a limita oferta DELETE pe un subset de rânduri, care altfel ar fi eliminate. În această formă, propunerea poate fi utilizată pentru a elimina intrările duplicate. Să presupunem că aveți un T tabel cu acest conținut:

Tabelul este prezent trei albastru (cyan) și de două ori - verde (verde) și roșu (roșu). Pentru a elimina copii suplimentare din fiecare culoare, face:

mysql> DELETE FROM t WHERE color = 'blue' LIMIT 2;
mysql> DELETE FROM t WHERE color = 'verde' LIMIT 1;
mysql> DELETE FROM t WHERE color = 'red' LIMIT 1;
mysql> SELECT * FROM T;

Recepția funcționează în absența unui index unic și elimină duplicat valorile NULL. Acest lucru este util în cazul în care aveți nevoie pentru a elimina duplicatele la un anumit set de numai rânduri de masă. Cu toate acestea, dacă doriți să eliminați o mulțime de seturi diferite de duplicate, o astfel de procedură nu ar dori să efectueze manual. Procesul poate fi automatizat, folosind tehnici pentru a identifica duplicate. In reteta am creat make_dup_count_query () pentru a genera o interogare, numărând numărul de valori repetate în multitudinea menționată de coloane din tabel:

Sub make_dup_count_query
mea ($ tbl_name, @col_name) = @_;
întoarce (
"SELECT COUNT (*)". se alăture ( "", @col_name)
. "\ Ndin $ tbl_name"
. "\ NGROUP BY". se alăture ( "", @col_name)
. "COUNT \ NHAVING (*)> 1"
);
>

Este posibil să se scrie un alt delete_dups funcție (), care utilizează make_dup_count_query () pentru a determina care valorile de masă sunt repetate atât de des. puteți vedea cât de multe duplicate ar trebui să fie eliminate din aceste date folosind ȘTERGE ... LIMIT n, astfel încât numai o copie a înregistrării lăsat în tabel. Funcția delete_dups () arata ca acest lucru:

delete_dups sub
mea ($ DBH, $ tbl_name, @col_name) = @_;
# Crearea și executa o interogare care găsește duplicat
mi $ dup_info = $ dbh-> selectall_arrayref (
make_dup_count_query ($ tbl_name, @col_name)
);
reveni dacă nu este definit ($ dup_info);
# Pentru fiecare set de valori se repetă pentru a șterge toate aparițiile de siruri de caractere,
# Conțin valorile, ci unul
foreach meu $ row_ref (@)
mea ($ count, @col_val) = @;
următor cu excepția cazului în $ count> 1;
# Construi un șir de condiții pentru a compara valori, fără a uita este NULL
str meu $;
pentru ($ i meu = 0; $ i cităm ($ col_val [$ i])
. "$ COL_NAME [$ i] este nul";
>
$ Str = "DELETE FROM $ tbl_name UNDE $ str LIMIT". ($ Count - 1);
$ Dbh-> do ($ str);
>
>

Să presupunem că avem un tabel angajat cu următoarele intrări:

mysql> SELECT * FROM angajat;

Pentru a putea utiliza delete_dups () pentru a elimina coloane duplicat în numele și departamentul masa de angajat, suna-l astfel:

delete_dups ($ DBH, "angajat", "numele", "departament");

delete_dups () funcția de apeluri funcția make_dup_count_query () și efectuează aceasta a generat o interogare SELECT. Pentru masa de angajat, această interogare afișează următorul rezultat:

delete_dups () funcție utilizează aceste informații pentru a forma următoarele sugestii ȘTERGE:

DELETE FROM angajat
Unde nume = 'Boris' și Departamentul de IS NULL LIMIT 1
DELETE FROM angajat
Unde nume = „Fred“ și departamentul = „contabil“ LIMIT 3
DELETE FROM angajat
Unde nume = „Mary Ann“ și departamentul = limita „vânzări“ 5
DELETE FROM angajat
Unde nume = 'Mary Ann' și departamentul = 'transport maritim' LIMIT 1

In general, o metodă folosind DELETE. LIMIT n, probabil, mai lent decât eliminarea duplicatelor prin crearea unui al doilea tabel sau adăugarea unui index unic. Aceste metode de a stoca date pe server și permite să efectueze toate lucrările. DELETE. LIMIT n necesită o cantitate mare de interacțiuni client cu serverul, deoarece folosește interogare SELECT pentru a prelua informații despre duplicat și apoi un număr de propuneri DELETE pentru a șterge copii rânduri duplicat.