In deze zelfstudie leren we hoe u dubbele rijen in een MySQL-database kunt verwijderen om de databasegrootte te verkleinen en de prestaties van de server te verbeteren.
Voordat we verder gaan, gaan we uit van:
- Je hebt MySQL geïnstalleerd en draait op je systeem
- U heeft root-toegang tot de database.
- Je hebt toegang tot een database om te experimenteren of te testen
OPMERKING : Als u een voorbeelddatabase nodig heeft om de concepten in deze handleiding uit te proberen, overweeg dan de Sakila-database of download een kopie van de database die in deze handleiding wordt gebruikt.
Hieronder vindt u bronnen:
Basisgebruik
Voordat we beginnen, maken we opzettelijk een tabel met dubbele waarden voor testdoeleinden. Hieronder vindt u de SQL-query's om deze actie uit te voeren:
GEBRUIK MAKEN VAN wereld-;LATEN VALLEN TAFEL INDIEN BESTAAT gebruikers;
CREËREN TAFEL gebruikers(ID kaart INT HOOFDSLEUTEL NIET NUL AUTO_INCREMENT ,gebruikersnaam VARCHAR (10) NIET NUL ,voor-en achternaam VARCHAR (twintig),e-mail VARCHAR (255) NIET NUL );
INSERT NAAR BINNEN gebruikers(gebruikersnaam,voor-en achternaam,e-mail) WAARDEN
('Maagd', 'Claude M. Mori', ' [e-mail beveiligd]'),
('druk op', 'Tiffany G. Bailey', ' [e-mail beveiligd]'),
('raket', 'Christopher S. Payton', ' [e-mail beveiligd]'),
('donkere materie', 'Patricia J. Vos', ' [e-mail beveiligd]'),
('onderwerp', 'Faye H. Hartley', ' [e-mail beveiligd]'),
('donkere materie', 'Patricia J. Vos', ' [e-mail beveiligd]'),
('raket', 'Christopher S. Payton', ' [e-mail beveiligd]'),
('Artemis', 'Wesley C. Dillard', ' [e-mail beveiligd]');
Voel je vrij om de bovenstaande vraag aan te passen aan je behoeften. U moet er ook voor zorgen dat u de database (wereld) hebt gemaakt om fouten te voorkomen.
Als we nu alle gegevens in de tabel krijgen en in volgorde van gebruikersnaam, zien we de duplicaten die we hebben zoals weergegeven:
mysql> gebruik maken van wereld-;Database veranderd
mysql> KIES * VAN gebruikers BESTEL DOOR gebruikersnaam;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|ID kaart|gebruikersnaam|voor-en achternaam|e-mail|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |Artemis|Wesley C. Dillard|[e-mail beveiligd]|
| 4 |donkere materie|Patricia J. Fox|[e-mail beveiligd]|
| 6 |donkere materie|Patricia J. Fox|[e-mail beveiligd]|
| 2 |druk op|Tiffany G. Bailey|[e-mail beveiligd]|
| 5 |onderwerp|Faye H. Hartley|[e-mail beveiligd]|
| 3 |raket|Christopher S. Payton|[e-mail beveiligd]|
| 7 |raket|Christopher S. Payton|[e-mail beveiligd]|
| 1 |maagd|Claude M. Mori|[e-mail beveiligd]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
Zoals je in de bovenstaande tabel kunt zien, hebben we twee dubbele waarden die de database zonder reden groter maken en lage snelheden veroorzaken.
Laten we nu leren hoe we deze waarden kunnen verwijderen.
#1 – VERWIJDER DEELNEMEN
Een manier om dubbele rijen in een database te verwijderen, is door de instructie MySQL DELETE JOIN te gebruiken. De query gebruikt echter id's om dubbele waarden te verwijderen.
Als we bijvoorbeeld de dubbele waarden in de bovenstaande gebruikerstabel willen verwijderen, kunnen we het volgende invoeren:
VERWIJDEREN tafel 1 VAN gebruikerstabel1 BINNEN MEEDOEN gebruikerstabel2 WAAR tabel1.id<tabel2.id EN tabel1.e-mail=tabel2.e-mail;Nadat u de bovenstaande query hebt uitgevoerd, verwijdert u de dubbele waarden zoals weergegeven in de onderstaande uitvoer:
mysql> VERWIJDEREN tafel 1 VAN gebruikerstabel1 BINNEN MEEDOEN gebruikerstabel2 WAAR tabel1.id<tabel2.id EN tabel1.e-mail=tabel2.e-mail;Vraag OK, 2getroffen rijen(0,01sec)
mysql> KIES * VAN gebruikers BESTEL DOOR gebruikersnaam;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|ID kaart|gebruikersnaam|voor-en achternaam|e-mail|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |Artemis|Wesley C. Dillard|[e-mail beveiligd]|
| 6 |donkere materie|Patricia J. Fox|[e-mail beveiligd]|
| 2 |druk op|Tiffany G. Bailey|[e-mail beveiligd]|
| 5 |onderwerp|Faye H. Hartley|[e-mail beveiligd]|
| 7 |raket|Christopher S. Payton|[e-mail beveiligd]|
| 1 |maagd|Claude M. Mori|[e-mail beveiligd]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
#2 – Row_Number() Functie
De tweede methode die we kunnen implementeren, is door de functie MySQL row_number() te gebruiken. Deze functie wordt ondersteund in MySQL versie 8 en hoger.
Het werkt door een opeenvolgende int-waarde toe te wijzen aan elke rij, waarbij rijen met dubbele waarden een waarde hoger dan 1 krijgen.
Gebruik de onderstaande bron voor meer informatie over deze functie:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
Overweeg de onderstaande query die de id van de rijen met dubbele waarden retourneert:
KIES ID kaart VAN ( KIES ID kaart,RIJ NUMMER()OVER( PARTITIE BY gebruikersnaam BESTEL DOOR gebruikersnaam) ALS rij_var VAN gebruikers)t1 WAAR rij_var> 1;Nadat u de bovenstaande query hebt uitgevoerd, zou u de lijst met id's moeten krijgen, zoals weergegeven in de onderstaande uitvoer:
+ ---- +|ID kaart|
+ ---- +
| 6 |
| 7 |
+ ---- +
2rijen in set (0,01sec)
Als u de waarden wilt verwijderen, vervangt u eenvoudig de SELECT-instructie door de DELETE-instructie zoals hieronder weergegeven:
VERWIJDEREN VAN gebruikers WAAR ID kaart IN ( KIES ID kaart VAN ( KIES ID kaart,RIJ NUMMER()OVER( PARTITIE BY gebruikersnaam BESTEL DOOR gebruikersnaam) ALS rij_var VAN gebruikers)t1 WAAR rij_var> 1);Ten slotte kunt u controleren of de dubbele waarden zijn verwijderd met behulp van de SELECT-instructie.
mysql> KIES * van gebruikers BESTEL DOOR gebruikersnaam;+ ---- + ------------ + ----------------------- + ------- ---------------- +
|ID kaart|gebruikersnaam|voor-en achternaam|e-mail|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |Artemis|Wesley C. Dillard|[e-mail beveiligd]|
| 4 |donkere materie|Patricia J. Fox|[e-mail beveiligd]|
| 2 |druk op|Tiffany G. Bailey|[e-mail beveiligd]|
| 5 |onderwerp|Faye H. Hartley|[e-mail beveiligd]|
| 3 |raket|Christopher S. Payton|[e-mail beveiligd]|
| 1 |maagd|Claude M. Mori|[e-mail beveiligd]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
Conclusie
In deze zelfstudie hebben we de twee methoden besproken voor het verwijderen van dubbele waarden uit een database. Grote databases, vooral die welke veel worden gebruikt, kunnen veel dubbele waarden van externe import en andere fouten bevatten. Daarom is het nodig om dubbele waarden te blijven opschonen om ervoor te zorgen dat toepassingen optimaal presteren.