En aquest tutorial, aprendrem com eliminar files duplicades en una base de dades MySQL per reduir la mida de la base de dades i ajudar a augmentar el rendiment del servidor.
Abans de continuar, assumim:
- Teniu MySQL instal·lat i funcionant al vostre sistema
- Teniu accés root a la base de dades.
- Teniu accés a una base de dades per experimentar o provar
NOTA : Si necessiteu una base de dades d'exemple per provar els conceptes proporcionats en aquesta guia, tingueu en compte la base de dades Sakila o descarregueu una còpia de la base de dades utilitzada en aquesta guia.
A continuació es proporcionen recursos:
Ús bàsic
Abans de començar, crearem intencionadament una taula que contingui valors duplicats a efectes de prova. A continuació es mostren les consultes SQL per realitzar aquesta acció:
ÚS món;TIRAR TAULA SI EXISTEIX usuaris;
CREAR TAULA usuaris(identificador INT CLAU PRIMÀRIA NO NUL AUTO_INCREMENT ,nom d'usuari VARCHAR (10) NO NUL ,nom complet VARCHAR (20),correu electrònic VARCHAR (255) NO NUL );
INSERTAR EN usuaris(nom d'usuari,nom complet,correu electrònic) VALORS
('Virgo', 'Claude M. Mori', '[email protected]'),
('Prem', 'Tiffany G. Bailey', '[email protected]'),
('coet', 'Christopher S. Payton', '[email protected]'),
('matèria fosca', 'Patricia J. Fox', '[email protected]'),
('assignatura', 'Faye H. Hartley', '[email protected]'),
('matèria fosca', 'Patricia J. Fox', '[email protected]'),
('coet', 'Christopher S. Payton', '[email protected]'),
('artemis', 'Wesley C. Dillard', '[email protected]');
No dubteu a modificar la consulta anterior per adaptar-la a les vostres necessitats. També heu d’assegurar-vos que teniu la base de dades (mundial) creada per evitar errors.
Ara, si obtenim totes les dades dins de la taula i en ordre per nom d’usuari, veurem els duplicats que tenim tal com es mostra:
mysql> ús món;Base de dades canviat
mysql> SELECCIONA * DE usuaris DEMANAT PER nom d'usuari;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|identificador|nom d'usuari|nom complet|correu electrònic|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |artemis|Wesley C. Dillard|[correu electrònic protegit]|
| 4 |matèria fosca|Patricia J. Fox|[correu electrònic protegit]|
| 6 |matèria fosca|Patricia J. Fox|[correu electrònic protegit]|
| 2 |prem|Tiffany G. Bailey|[correu electrònic protegit]|
| 5 |assignatura|Faye H. Hartley|[correu electrònic protegit]|
| 3 |coet|Christopher S. Payton|[correu electrònic protegit]|
| 7 |coet|Christopher S. Payton|[correu electrònic protegit]|
| 1 |virgo|Claude M. Mori|[correu electrònic protegit]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
Com podeu veure a la taula anterior, tenim dos valors duplicats que fan que la base de dades sigui més gran sense cap motiu i causi velocitats lentes.
Aprenem ara com podem eliminar aquests valors.
# 1 - ESBORRAR UNEIX-TE
Una manera d’eliminar files duplicades d’una base de dades és utilitzar la sentència MySQL DELETE JOIN. La consulta, però, utilitza identificadors per eliminar valors duplicats.
Per exemple, per eliminar els valors duplicats de la taula d’usuaris anterior, podem introduir:
ESBORRAR taula1 DE taula d'usuaris1 INTERIOR UNEIX-TE taula2 dels usuaris ON table1.id<table2.id I table1.email=table2.email;Un cop executada la consulta anterior, suprimireu els valors duplicats tal com es mostra a la sortida següent:
mysql> ESBORRAR taula1 DE taula d'usuaris1 INTERIOR UNEIX-TE taula2 dels usuaris ON table1.id<table2.id I table1.email=table2.email;Consulta bé, 2files afectades(0.01sec)
mysql> SELECCIONA * DE usuaris DEMANAT PER nom d'usuari;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|identificador|nom d'usuari|nom complet|correu electrònic|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |artemis|Wesley C. Dillard|[correu electrònic protegit]|
| 6 |matèria fosca|Patricia J. Fox|[correu electrònic protegit]|
| 2 |prem|Tiffany G. Bailey|[correu electrònic protegit]|
| 5 |assignatura|Faye H. Hartley|[correu electrònic protegit]|
| 7 |coet|Christopher S. Payton|[correu electrònic protegit]|
| 1 |virgo|Claude M. Mori|[correu electrònic protegit]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
# 2 - Funció Row_Number ()
El segon mètode que podem implementar és utilitzar la funció MySQL row_number (). Aquesta funció és compatible amb MySQL versió 8 i posteriors.
Funciona assignant un valor int seqüencial a cada fila, amb files que contenen valors duplicats obtenint un valor superior a 1.
Per obtenir més informació sobre aquesta funció, utilitzeu el recurs que es proporciona a continuació:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
Penseu en la consulta següent que retorna l'identificador de les files amb valors duplicats:
SELECCIONA identificador DE ( SELECCIONA identificador,ROW_NUMBER()FINALITZAT( PARTICIÓ BY nom d'usuari DEMANAT PER nom d'usuari) AS fila_var DE usuaris)t1 ON fila_var> 1;Un cop executada la consulta anterior, hauríeu d'obtenir la llista d'identificadors tal com es mostra a la sortida següent:
+ ---- +|identificador|
+ ---- +
| 6 |
| 7 |
+ ---- +
2files dins conjunt (0.01sec)
Si voleu eliminar els valors, simplement substituïu la sentència SELECT per la sentència DELETE tal com es mostra a continuació:
ESBORRAR DE usuaris ON identificador IN ( SELECCIONA identificador DE ( SELECCIONA identificador,ROW_NUMBER()FINALITZAT( PARTICIÓ BY nom d'usuari DEMANAT PER nom d'usuari) AS fila_var DE usuaris)t1 ON fila_var> 1);Finalment, podeu verificar que els valors duplicats s’eliminin mitjançant la instrucció SELECT.
mysql> SELECCIONA * des de usuaris DEMANAT PER nom d'usuari;+ ---- + ------------ + ----------------------- + ------- ---------------- +
|identificador|nom d'usuari|nom complet|correu electrònic|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |artemis|Wesley C. Dillard|[correu electrònic protegit]|
| 4 |matèria fosca|Patricia J. Fox|[correu electrònic protegit]|
| 2 |prem|Tiffany G. Bailey|[correu electrònic protegit]|
| 5 |assignatura|Faye H. Hartley|[correu electrònic protegit]|
| 3 |coet|Christopher S. Payton|[correu electrònic protegit]|
| 1 |virgo|Claude M. Mori|[correu electrònic protegit]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
Conclusió
En aquest tutorial, hem discutit els dos mètodes per eliminar valors duplicats d'una base de dades. Les grans bases de dades, especialment aquelles d’ús comú, poden contenir molts valors duplicats d’importacions externes i altres errors. Per tant, cal continuar eliminant valors duplicats per garantir que les aplicacions funcionin de manera òptima.