In questo tutorial, impareremo come rimuovere le righe duplicate in un database MySQL per ridurre le dimensioni del database e contribuire ad aumentare le prestazioni del server.
Prima di continuare, assumiamo:
- Hai MySQL installato e in esecuzione sul tuo sistema
- Hai accesso root al database.
- Hai accesso a un database per sperimentare o testare
NOTA : Se hai bisogno di un database di esempio per provare i concetti forniti in questa guida, prendi in considerazione il database Sakila o scarica una copia del database utilizzato in questa guida.
Le risorse sono fornite di seguito:
Utilizzo di base
Prima di iniziare, creeremo intenzionalmente una tabella contenente valori duplicati a scopo di test. Le query SQL per eseguire questa azione sono le seguenti:
UTILIZZO mondo;GOCCIOLARE TAVOLO SE ESISTE utenti;
CREARE TAVOLO utenti(ID INT CHIAVE PRIMARIA NON NULLO INCREMENTO AUTOMATICO ,nome utente VARCHAR (10) NON NULLO ,nome e cognome VARCHAR (venti),e-mail VARCHAR (255) NON NULLO );
INSERIRE IN utenti(nome utente,nome e cognome,e-mail) VALORI
('Vergine', 'Claude M. Mori', '[e-mail protetta]'),
('premere', 'Tiffany G. Bailey', '[e-mail protetta]'),
('razzo', 'Christopher S. Payton', '[e-mail protetta]'),
('materia oscura', 'Patricia J. Fox', '[e-mail protetta]'),
('soggetto', 'Faye H. Hartley', '[e-mail protetta]'),
('materia oscura', 'Patricia J. Fox', '[e-mail protetta]'),
('razzo', 'Christopher S. Payton', '[e-mail protetta]'),
('artemide', 'Wesley C. Dillard', '[e-mail protetta]');
Sentiti libero di modificare la query qui sopra per adattarla alle tue esigenze. Dovresti anche assicurarti di avere il database (mondo) creato per evitare errori.
Ora, se otteniamo tutti i dati all'interno della tabella e in ordine per nome utente, vedremo i duplicati che abbiamo come mostrato:
mysql> utilizzo mondo;Banca dati cambiato
mysql> SELEZIONARE * A PARTIRE DAL utenti ORDINATO DA nome utente;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|ID|nome utente|nome e cognome|e-mail|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |artemide|Wesley C. Dillard|[e-mail protetta]|
| 4 |materia oscura|Patricia J. Fox|[e-mail protetta]|
| 6 |materia oscura|Patricia J. Fox|[e-mail protetta]|
| 2 |premere|Tiffany G. Bailey|[e-mail protetta]|
| 5 |soggetto|Faye H. Hartley|[e-mail protetta]|
| 3 |razzo|Christopher S. Payton|[e-mail protetta]|
| 7 |razzo|Christopher S. Payton|[e-mail protetta]|
| 1 |Vergine|Claude M. Mori|[e-mail protetta]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
Come puoi vedere dalla tabella sopra, abbiamo due valori duplicati che rendono il database più grande senza motivo e causano basse velocità.
Impariamo ora come rimuovere questi valori.
#1 – CANCELLA ISCRIVITI
Un modo per rimuovere le righe duplicate in un database è utilizzare l'istruzione MySQL DELETE JOIN. La query, tuttavia, utilizza gli ID per rimuovere i valori duplicati.
Ad esempio, per rimuovere i valori duplicati nella tabella utenti sopra, possiamo inserire:
ELIMINA Tabella 1 A PARTIRE DAL tabella utenti1 INTERNO ADERIRE utenti tabella2 DOVE table1.id<table2.id E table1.email=table2.email;Una volta eseguita la query sopra, rimuoverai i valori duplicati come mostrato nell'output di seguito:
mysql> ELIMINA Tabella 1 A PARTIRE DAL tabella utenti1 INTERNO ADERIRE utenti tabella2 DOVE table1.id<table2.id E table1.email=table2.email;Domanda OK, 2righe interessate(0.01secondo)
mysql> SELEZIONARE * A PARTIRE DAL utenti ORDINATO DA nome utente;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|ID|nome utente|nome e cognome|e-mail|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |artemide|Wesley C. Dillard|[e-mail protetta]|
| 6 |materia oscura|Patricia J. Fox|[e-mail protetta]|
| 2 |premere|Tiffany G. Bailey|[e-mail protetta]|
| 5 |soggetto|Faye H. Hartley|[e-mail protetta]|
| 7 |razzo|Christopher S. Payton|[e-mail protetta]|
| 1 |Vergine|Claude M. Mori|[e-mail protetta]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
#2 – Funzione Riga_Numero()
Il secondo metodo che possiamo implementare è usare la funzione row_number() di MySQL. Questa funzione è supportata in MySQL versione 8 e successive.
Funziona assegnando un valore int sequenziale a ciascuna riga, con righe contenenti valori duplicati che ottengono un valore maggiore di 1.
Per saperne di più su questa funzione, utilizzare la risorsa fornita di seguito:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
Considera la query di seguito che restituisce l'id delle righe con valori duplicati:
SELEZIONARE ID A PARTIRE DAL ( SELEZIONARE ID,RIGA_NUMERO()TERMINATO( PARTIZIONE PER nome utente ORDINATO DA nome utente) COME riga_var A PARTIRE DAL utenti)t1 DOVE riga_var> 1;Una volta eseguita la query sopra, dovresti ottenere l'elenco degli ID come mostrato nell'output seguente:
+ ---- +|ID|
+ ---- +
| 6 |
| 7 |
+ ---- +
2righe in set (0.01secondo)
Se si desidera rimuovere i valori, sostituire semplicemente l'istruzione SELECT con l'istruzione DELETE come mostrato di seguito:
ELIMINA A PARTIRE DAL utenti DOVE ID IN ( SELEZIONARE ID A PARTIRE DAL ( SELEZIONARE ID,RIGA_NUMERO()TERMINATO( PARTIZIONE PER nome utente ORDINATO DA nome utente) COME riga_var A PARTIRE DAL utenti)t1 DOVE riga_var> 1);Infine, puoi verificare che i valori duplicati vengano rimossi utilizzando l'istruzione SELECT.
mysql> SELEZIONARE * a partire dal utenti ORDINATO DA nome utente;+ ---- + ------------ + ----------------------- + ------- ---------------- +
|ID|nome utente|nome e cognome|e-mail|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |artemide|Wesley C. Dillard|[e-mail protetta]|
| 4 |materia oscura|Patricia J. Fox|[e-mail protetta]|
| 2 |premere|Tiffany G. Bailey|[e-mail protetta]|
| 5 |soggetto|Faye H. Hartley|[e-mail protetta]|
| 3 |razzo|Christopher S. Payton|[e-mail protetta]|
| 1 |Vergine|Claude M. Mori|[e-mail protetta]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
Conclusione
In questo tutorial, abbiamo discusso i due metodi per rimuovere i valori duplicati da un database. I database di grandi dimensioni, in particolare quelli di uso comune, possono contenere molti valori duplicati da importazioni esterne e altri errori. Pertanto, è necessario continuare a eliminare i valori duplicati per garantire che le applicazioni funzionino in modo ottimale.