Come rimuovere le righe duplicate in MySQL

Introduzione

Esistono diversi casi in cui potresti incontrare righe duplicate nel tuo database MySQL. Questa guida ti guiderà attraverso il processo di rimozione dei valori di riga duplicati in MySQL.

Prerequisiti

• Un sistema con MySQL installato
• Un account utente root MySQL
• Accesso a una finestra di terminale/riga di comando (Ctrl-Alt-T, Cerca> Terminale)

Impostazione del database di test

Se hai già un database MySQL su cui lavorare, vai alla sezione successiva.

Altrimenti, apri una finestra di terminale e digita quanto segue:

mysql –u root –p

Quando richiesto, inserisci la root password per l'installazione di MySQL. Se hai un account utente specifico, usa quelle credenziali invece di root.

Il prompt di sistema dovrebbe cambiare in:

mysql>

Crea database di test

È possibile creare una nuova tabella in un database esistente. Per fare ciò, trova il database appropriato elencando tutte le istanze esistenti con:

SHOW DATABASES;

In alternativa, puoi creare un nuovo database immettendo il seguente comando:

CREATE DATABASE IF NOT EXISTS testdata;

Per iniziare a lavorare con i tuoi nuovi testdata utilizzo del database:

USE testdata;

Aggiungi tabella e dati

Una volta nel database, aggiungi una tabella con i dati seguenti utilizzando il seguente comando:

CREATE TABLE dates (
id INT PRIMARY KEY AUTO_INCREMENT,
day VARCHAR(2) NOT NULL,
month VARCHAR(10) NOT NULL,
year VARCHAR(4) NOT NULL

);

INSERT INTO dates (day,month,year)
VALUES (’29’,’January’,’2011’),
(’30’,’January’,’2011’),
(’30’,’January’,’2011’),
(’14’,’February,’2017’),
(’14’,’February,’2018’),
(‘23’,’March’,’2018’),
(‘23’,’March’,’2018’),
(‘23’,’March’,’2019’),
(‘29’,’October’,’2019’),
(‘29’,’November’,’2019’),
(‘12’,’November’,’2017’),
(‘17’,’August’,’2018’),
(‘05’,’June’,’2016’);

Visualizza il contenuto della tabella delle date

Per visualizzare tutte le date inserite, ordinate per anno, digitare:

SELECT * FROM dates ORDER BY year;

L'output dovrebbe mostrare un elenco di date nell'ordine appropriato.

Visualizza righe duplicate

Per scoprire se sono presenti righe duplicate nel database di test, utilizzare il comando:

SELECT
     day, COUNT(day),
     month, COUNT(month),
     year, COUNT(year)
FROM 
     dates
GROUP BY
     day,
     month,
     year
HAVING 
     COUNT(day) > 1
     AND COUNT(month) > 1
     AND COUNT(year) > 1;

Il sistema visualizzerà tutti i valori duplicati. In questo caso, dovresti vedere:

Questo formato funziona per selezionare più colonne. Se hai una colonna con un identificatore univoco, come un indirizzo email in un elenco di contatti o una singola colonna di data, puoi semplicemente selezionare da quella colonna.

Rimozione delle righe duplicate

Prima di utilizzare uno dei metodi indicati di seguito, ricorda che devi lavorare in un database esistente. Useremo il nostro database di esempio:

USE testdata;

Opzione 1:rimuovi le righe duplicate utilizzando INNER JOIN

Per eliminare le righe duplicate nella nostra tabella MySQL di prova, usa MySQL JOINS e inserisci quanto segue:

delete t1 FROM dates t1
INNER  JOIN dates t2
WHERE
    t1.id < t2.id AND
    t1.day = t2.day AND
    t1.month = t2.month AND
    t1.year = t2.year;

Puoi anche utilizzare il comando da Visualizza duplicato Righe per verificare l'eliminazione.

Opzione 2:rimuovi le righe duplicate utilizzando una tabella intermedia

Puoi creare una tabella intermedia e usalo per rimuovere le righe duplicate. Questo viene fatto trasferendo solo le righe univoche nella tabella appena creata ed eliminando quella originale (con le restanti righe duplicate).

Per farlo, segui le istruzioni seguenti.

1. Crea una tabella intermedia che abbia la stessa struttura della tabella di origine e trasferisci le righe univoche trovate nell'origine:

CREATE TABLE [copy_of_source] SELECT DISTINCT [columns] FROM [source_table];

Ad esempio, per creare una copia della struttura della tabella di esempio dates il comando è:

CREATE TABLE copy_of_dates SELECT DISTINCT id, day, month, year FROM dates;

2. Fatto ciò, puoi eliminare la tabella di origine con il comando drop e rinominare quella nuova:

DROP TABLE [source_table];

ALTER TABLE [copy_of_source] RENAME TO [source_table];

Ad esempio:

DROP TABLE dates;

ALTER TABLE copy_of_dates RENAME TO dates;

Opzione 3:rimuovi le righe duplicate utilizzando ROW_NUMBER()

Importante: Questo metodo è disponibile solo per MySQL versione 8.02 e più tardi. Controlla la versione di MySQL prima di provare questo metodo.

Un altro modo per eliminare le righe duplicate è con il ROW_NUMBER() funzione.

SELECT *. ROW_NUMBER () Over (PARTITION BY [column] ORDER BY [column]) as [row_number_name];

Pertanto, il comando per la nostra tabella di esempio sarebbe:

SELECT *. ROW_NUMBER () Over (PARTITION BY id ORDER BY id) as row_number;

I risultati includono un numero_riga colonna. I dati vengono partizionati per id e all'interno di ogni partizione ci sono numeri di riga univoci. I valori univoci sono etichettati con il numero di riga 1 , mentre i duplicati sono 2 , 3 , e così via.

Pertanto, per rimuovere le righe duplicate, è necessario eliminare tutto tranne quelle contrassegnate con 1. Questo viene fatto eseguendo un DELETE interrogare con il row_number come filtro.

Per eliminare le righe duplicate esegui:

DELETE FROM [table_name] WHERE row_number > 1;

Nel nostro esempio date table, il comando sarebbe:

DELETE FROM dates WHERE row_number > 1;

L'output ti dirà quante righe sono state interessate, ovvero quante righe duplicate sono state eliminate.

Puoi verificare che non ci siano righe duplicate eseguendo:

SELECT * FROM [table_name];

Ad esempio:

SELECT * FROM dates;