Rimuovi le righe duplicate in MySQL

Ho una tabella con i seguenti campi:

id (Unique) url (Unique) title company site_id 

Ora, ho bisogno di rimuovere le righe con lo stesso title, company and site_id . Un modo per farlo sarà utilizzare il seguente SQL insieme a uno script ( PHP ):

 SELECT title, site_id, location, id, count( * ) FROM jobs GROUP BY site_id, company, title, location HAVING count( * ) >1 

Dopo aver eseguito questa query, posso rimuovere i duplicati utilizzando uno script lato server.

Ma, voglio sapere se questo può essere fatto solo usando la query SQL.

Un modo davvero semplice per farlo è aggiungere un indice UNIQUE sulle 3 colonne. Quando scrivi l’istruzione ALTER , includi la parola chiave IGNORE . Così:

 ALTER IGNORE TABLE jobs ADD UNIQUE INDEX idx_name (site_id, title, company); 

Questo farà cadere tutte le righe duplicate. Come ulteriore vantaggio, i futuri INSERTs duplicati verranno ignorati. Come sempre, potresti voler fare un backup prima di eseguire qualcosa del genere …

Se non si desidera modificare le proprietà della colonna, è ansible utilizzare la query di seguito.

Dato che hai una colonna con ID univoci (ad es. Colonne auto_increment ), puoi usarla per rimuovere i duplicati:

 DELETE `a` FROM `jobs` AS `a`, `jobs` AS `b` WHERE -- IMPORTANT: Ensures one version remains -- Change "ID" to your unique column's name `a`.`ID` < `b`.`ID` -- Any duplicates you want to check for AND (`a`.`title` = `b`.`title` OR `a`.`title` IS NULL AND `b`.`title` IS NULL) AND (`a`.`company` = `b`.`company` OR `a`.`company` IS NULL AND `b`.`company` IS NULL) AND (`a`.`site_id` = `b`.`site_id` OR `a`.`site_id` IS NULL AND `b`.`site_id` IS NULL); 

In MySQL, puoi semplificarlo ancora di più con l' operatore uguale NULL-safe (alias "operator spaceship" ):

 DELETE `a` FROM `jobs` AS `a`, `jobs` AS `b` WHERE -- IMPORTANT: Ensures one version remains -- Change "ID" to your unique column's name `a`.`ID` < `b`.`ID` -- Any duplicates you want to check for AND `a`.`title` <=> `b`.`title` AND `a`.`company` < => `b`.`company` AND `a`.`site_id` < => `b`.`site_id`; 

MySQL ha delle restrizioni sul riferimento alla tabella da cui si sta eliminando. Puoi aggirare il problema con una tabella temporanea, ad esempio:

 create temporary table tmpTable (id int); insert tmpTable (id) select id from YourTable yt where exists ( select * from YourTabe yt2 where yt2.title = yt.title and yt2.company = yt.company and yt2.site_id = yt.site_id and yt2.id > yt.id ); delete from YourTable where ID in (select id from tmpTable); 

Dal suggerimento di Kostanos nei commenti:
L’unica query lenta sopra è DELETE, per i casi in cui si dispone di un database molto grande. Questa query potrebbe essere più veloce:

 DELETE FROM YourTable USING YourTable, tmpTable WHERE YourTable.id=tmpTable.id 

Se la dichiarazione IGNORE non funziona come nel mio caso, puoi usare la seguente dichiarazione:

 CREATE TABLE your_table_deduped like your_table; INSERT your_table_deduped SELECT * FROM your_table GROUP BY index1_id, index2_id; RENAME TABLE your_table TO your_table_with_dupes; RENAME TABLE your_table_deduped TO your_table; #OPTIONAL ALTER TABLE `your_table` ADD UNIQUE `unique_index` (`index1_id`, `index2_id`); #OPTIONAL DROP TABLE your_table_with_dupes; 

C’è un’altra soluzione:

 DELETE t1 FROM my_table t1, my_table t2 WHERE t1.id < t2.id AND t1.my_field = t2.my_field AND t1.my_field_2 = t2.my_field_2 AND ... 

Cancellare duplicati su tabelle MySQL è un problema comune, che in genere è il risultato di un vincolo mancante per evitare questi duplicati in anticipo. Ma questo problema comune di solito viene fornito con esigenze specifiche … che richiedono approcci specifici. L’approccio dovrebbe essere diverso a seconda, ad esempio, della dimensione dei dati, della voce duplicata che deve essere conservata (generalmente la prima o l’ultima), se ci sono degli indici da conservare o se vogliamo eseguire ulteriori azione sui dati duplicati.

Ci sono anche alcune specificità su MySQL stesso, come non essere in grado di fare riferimento alla stessa tabella su una causa FROM durante l’esecuzione di una tabella UPDATE (aumenterà l’errore MySQL # 1093). Questa limitazione può essere superata utilizzando una query interna con una tabella temporanea (come suggerito su alcuni approcci sopra). Ma questa query interna non funzionerà particolarmente bene quando si gestiscono fonti di dati di grandi dimensioni.

Tuttavia, esiste un approccio migliore per rimuovere i duplicati, che è sia efficiente che affidabile e che può essere facilmente adattato alle diverse esigenze.

L’idea generale è di creare una nuova tabella temporanea, di solito aggiungendo un vincolo univoco per evitare ulteriori duplicati, e di INSERIRE i dati dalla tabella precedente in quella nuova, pur avendo cura dei duplicati. Questo approccio si basa su semplici query MySQL INSERT, crea un nuovo vincolo per evitare ulteriori duplicati e ignora la necessità di utilizzare una query interna per cercare duplicati e una tabella temporanea che deve essere conservata in memoria (quindi adattandosi anche alle fonti di big data).

Questo è come può essere raggiunto. Dato che abbiamo un impiegato della tabella, con le seguenti colonne:

 employee (id, first_name, last_name, start_date, ssn) 

Per eliminare le righe con una colonna ssn duplicata e mantenendo solo la prima voce trovata, è ansible seguire la seguente procedura:

 -- create a new tmp_eployee table CREATE TABLE tmp_employee LIKE employee; -- add a unique constraint ALTER TABLE tmp_employee ADD UNIQUE(ssn); -- scan over the employee table to insert employee entries INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id; -- rename tables RENAME TABLE employee TO backup_employee, tmp_employee TO employee; 

Spiegazione tecnica

  • La riga 1 crea una nuova tabella tmp_eployee con esattamente la stessa struttura della tabella dei dipendenti
  • La riga n. 2 aggiunge un vincolo UNIQUE alla nuova tabella tmp_eployee per evitare ulteriori duplicati
  • La riga n. 3 esegue la scansione della tabella dei dipendenti originale per id, inserendo nuove voci dei dipendenti nella nuova tabella tmp_eployee , ignorando le voci duplicate
  • La riga n. 4 rinomina le tabelle, in modo che la nuova tabella dei dipendenti contenga tutte le voci senza i duplicati e una copia di backup dei dati precedenti venga mantenuta nella tabella backup_employee

Utilizzando questo approccio, i registri 1.6M sono stati convertiti in 6k in meno di 200 secondi.

Chetan , seguendo questo processo, puoi rimuovere velocemente e facilmente tutti i tuoi duplicati e creare un vincolo UNIQUE eseguendo:

 CREATE TABLE tmp_jobs LIKE jobs; ALTER TABLE tmp_jobs ADD UNIQUE(site_id, title, company); INSERT IGNORE INTO tmp_jobs SELECT * FROM jobs ORDER BY id; RENAME TABLE jobs TO backup_jobs, tmp_jobs TO jobs; 

Naturalmente, questo processo può essere ulteriormente modificato per adattarlo alle diverse esigenze quando si eliminano i duplicati. Seguono alcuni esempi.

✔ Variazione per mantenere l’ultima voce al posto della prima

A volte è necessario mantenere l’ultima voce duplicata anziché la prima.

 CREATE TABLE tmp_employee LIKE employee; ALTER TABLE tmp_employee ADD UNIQUE(ssn); INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id DESC; RENAME TABLE employee TO backup_employee, tmp_employee TO employee; 
  • Alla riga n. 3, la clausola DESC di ORDER BY id rende gli ultimi ID prioritari rispetto agli altri

✔ Variazione per eseguire alcune attività sui duplicati, ad esempio mantenendo un conteggio sui duplicati trovati

A volte è necessario eseguire ulteriori elaborazioni sulle voci duplicate trovate (come il mantenimento del conteggio dei duplicati).

 CREATE TABLE tmp_employee LIKE employee; ALTER TABLE tmp_employee ADD UNIQUE(ssn); ALTER TABLE tmp_employee ADD COLUMN n_duplicates INT DEFAULT 0; INSERT INTO tmp_employee SELECT * FROM employee ORDER BY id ON DUPLICATE KEY UPDATE n_duplicates=n_duplicates+1; RENAME TABLE employee TO backup_employee, tmp_employee TO employee; 
  • Alla riga 3, viene creata una nuova colonna n_duplicates
  • Alla riga 4, la query INSERT INTO … ON DUPLICATE KEY UPDATE viene utilizzata per eseguire un aggiornamento aggiuntivo quando viene trovato un duplicato (in questo caso, l’aumento di un contatore) La query INSERT INTO … ON DUPLICATE KEY UPDATE può essere utilizzato per eseguire diversi tipi di aggiornamenti per i duplicati trovati.

✔ Variazione per rigenerare l’ID del campo auto-incrementale

A volte utilizziamo un campo auto-incrementale e, per mantenere l’indice il più compatto ansible, possiamo approfittare dell’eliminazione dei duplicati per rigenerare il campo auto-incrementale nella nuova tabella temporanea.

 CREATE TABLE tmp_employee LIKE employee; ALTER TABLE tmp_employee ADD UNIQUE(ssn); INSERT IGNORE INTO tmp_employee SELECT (first_name, last_name, start_date, ssn) FROM employee ORDER BY id; RENAME TABLE employee TO backup_employee, tmp_employee TO employee; 
  • Alla riga n. 3, invece di selezionare tutti i campi sulla tabella, il campo id viene saltato in modo che il motore DB ne generi automaticamente uno nuovo

✔ Altre varianti

Molte altre modifiche sono anche fattibili a seconda del comportamento desiderato. Ad esempio, le seguenti query useranno una seconda tabella temporanea per, oltre a 1) mantenere l’ultima voce al posto della prima; e 2) aumentare un contatore sui duplicati trovati; anche 3) rigenera l’id di campo auto-incrementale mantenendo l’ordine di immissione come era sui dati precedenti.

 CREATE TABLE tmp_employee LIKE employee; ALTER TABLE tmp_employee ADD UNIQUE(ssn); ALTER TABLE tmp_employee ADD COLUMN n_duplicates INT DEFAULT 0; INSERT INTO tmp_employee SELECT * FROM employee ORDER BY id DESC ON DUPLICATE KEY UPDATE n_duplicates=n_duplicates+1; CREATE TABLE tmp_employee2 LIKE tmp_employee; INSERT INTO tmp_employee2 SELECT (first_name, last_name, start_date, ssn) FROM tmp_employee ORDER BY id; DROP TABLE tmp_employee; RENAME TABLE employee TO backup_employee, tmp_employee2 TO employee; 

Ho questo snipet di query per SQLServer ma penso che possa essere utilizzato in altri DBMS con piccole modifiche:

 DELETE FROM Table WHERE Table.idTable IN ( SELECT MAX(idTable) FROM idTable GROUP BY field1, field2, field3 HAVING COUNT(*) > 1) 

Ho dimenticato di dirti che questa query non rimuove la riga con l’id più basso delle righe duplicate. Se questo funziona per te prova questa query:

 DELETE FROM jobs WHERE jobs.id IN ( SELECT MAX(id) FROM jobs GROUP BY site_id, company, title, location HAVING COUNT(*) > 1) 

Semplice e veloce per tutti i casi:

 CREATE TEMPORARY TABLE IF NOT EXISTS _temp_duplicates AS (SELECT dub.id FROM table_with_duplications dub GROUP BY dub.field_must_be_uniq_1, dub.field_must_be_uniq_2 HAVING COUNT(*) > 1); DELETE FROM table_with_duplications WHERE id IN (SELECT id FROM _temp_duplicates); 

Il modo più veloce è inserire righe distinte in una tabella temporanea. Usando delete, mi ci sono volute alcune ore per rimuovere i duplicati da una tabella di 8 milioni di righe. Usando insert e distinti, ci sono voluti solo 13 minuti.

 CREATE TABLE tempTableName LIKE tableName; CREATE INDEX ix_all_id ON tableName(cellId,attributeId,entityRowId,value); INSERT INTO tempTableName(cellId,attributeId,entityRowId,value) SELECT DISTINCT cellId,attributeId,entityRowId,value FROM tableName; TRUNCATE TABLE tableName; INSERT INTO tableName SELECT * FROM tempTableName; DROP TABLE tempTableName; 

Continuo a visitare questa pagina ogni volta che google “rimuove i duplicati di forma mysql” ma per le mie soluzioni theIGNORE non funzionano perché ho una tabella mysql InnoDB

questo codice funziona meglio in qualsiasi momento

 CREATE TABLE tableToclean_temp LIKE tableToclean; ALTER TABLE tableToclean_temp ADD UNIQUE INDEX (fontsinuse_id); INSERT IGNORE INTO tableToclean_temp SELECT * FROM tableToclean; DROP TABLE tableToclean; RENAME TABLE tableToclean_temp TO tableToclean; 

tableToclean = il nome della tabella che devi pulire

tableToclean_temp = una tabella temporanea creata ed eliminata

Questa soluzione sposterà i duplicati in una tabella e gli uniques in un altro .

 -- speed up creating uniques table if dealing with many rows CREATE INDEX temp_idx ON jobs(site_id, company, title, location); -- create the table with unique rows INSERT jobs_uniques SELECT * FROM ( SELECT * FROM jobs GROUP BY site_id, company, title, location HAVING count(1) > 1 UNION SELECT * FROM jobs GROUP BY site_id, company, title, location HAVING count(1) = 1 ) x -- create the table with duplicate rows INSERT jobs_dupes SELECT * FROM jobs WHERE id NOT IN (SELECT id FROM jobs_uniques) -- confirm the difference between uniques and dupes tables SELECT COUNT(1) AS jobs, (SELECT COUNT(1) FROM jobs_dupes) + (SELECT COUNT(1) FROM jobs_uniques) AS sum FROM jobs 

Una soluzione che è semplice da capire e funziona senza chiave primaria:

1) aggiungi una nuova colonna booleana

 alter table mytable add tokeep boolean; 

2) aggiungi un vincolo sulle colonne duplicate E sulla nuova colonna

 alter table mytable add constraint preventdupe unique (mycol1, mycol2, tokeep); 

3) imposta la colonna booleana su true. Ciò riuscirà solo su una delle righe duplicate a causa del nuovo vincolo

 update ignore mytable set tokeep = true; 

4) eliminare le righe che non sono state contrassegnate come mantenimento

 delete from mytable where tokeep is null; 

5) rilascia la colonna aggiunta

 alter table mytable drop tokeep; 

Ti suggerisco di mantenere il vincolo che hai aggiunto, in modo che i nuovi duplicati vengano prevenuti in futuro.

se si dispone di una tabella di grandi dimensioni con un numero enorme di record, le soluzioni di cui sopra non funzionano o richiedono troppo tempo. Quindi abbiamo una soluzione diversa

 -- Create temporary table CREATE TABLE temp_table LIKE table1; -- Add constraint ALTER TABLE temp_table ADD UNIQUE(title, company,site_id); -- Copy data INSERT IGNORE INTO temp_table SELECT * FROM table1; -- Rename and drop RENAME TABLE table1 TO old_table1, temp_table TO table1; DROP TABLE old_table1; 

Elimina le righe duplicate usando l’istruzione DELETE JOIN MySQL ti fornisce l’istruzione DELETE JOIN che puoi usare per rimuovere rapidamente le righe duplicate.

La seguente dichiarazione cancella le righe duplicate e mantiene l’id più alto:

 DELETE t1 FROM contacts t1 INNER JOIN contacts t2 WHERE t1.id < t2.id AND t1.email = t2.email; 

Mi piace essere un po ‘più specifico su quali record eliminare così qui è la mia soluzione:

 delete from jobs c1 where not c1.location = 'Paris' and c1.site_id > 64218 and exists ( select * from jobs c2 where c2.site_id = c1.site_id and c2.company = c1.company and c2.location = c1.location and c2.title = c1.title and c2.site_id > 63412 and c2.site_id < 64219 ) 

Puoi facilmente eliminare i record duplicati da questo codice ..

 $qry = mysql_query("SELECT * from cities"); while($qry_row = mysql_fetch_array($qry)) { $qry2 = mysql_query("SELECT * from cities2 where city = '".$qry_row['city']."'"); if(mysql_num_rows($qry2) > 1){ while($row = mysql_fetch_array($qry2)){ $city_arry[] = $row; } $total = sizeof($city_arry) - 1; for($i=1; $i< =$total; $i++){ mysql_query( "delete from cities2 where town_id = '".$city_arry[$i][0]."'"); } } //exit; } 

Ho dovuto farlo con i campi di testo e ho trovato il limite di 100 byte sull’indice.

Ho risolto questo problema aggiungendo una colonna, facendo un hash MD5 dei campi e facendo l’alter.

 ALTER TABLE table ADD `merged` VARCHAR( 40 ) NOT NULL ; UPDATE TABLE SET merged` = MD5(CONCAT(`col1`, `col2`, `col3`)) ALTER IGNORE TABLE table ADD UNIQUE INDEX idx_name (`merged`);