V tomto návode sa naučíme, ako odstrániť duplicitné riadky v databáze MySQL, aby sa zmenšila veľkosť databázy a pomohla zvýšiť výkon servera.
Predtým, ako budeme pokračovať, predpokladáme:
- Vo vašom systéme je nainštalovaný a spustený server MySQL
- K databáze máte prístup root.
- Máte prístup k databáze na experimentovanie alebo testovanie
POZNÁMKA : Ak potrebujete vzorovú databázu na vyskúšanie konceptov uvedených v tejto príručke, zvážte databázu Sakila alebo si stiahnite kópiu databázy použitej v tejto príručke.
Zdroje sú uvedené nižšie:
Základné použitie
Predtým, ako začneme, zámerne vytvoríme tabuľku obsahujúcu duplicitné hodnoty na testovacie účely. Dotazy SQL na vykonanie tejto akcie sú uvedené nižšie:
POUŽITIE svet;POKLES TABUĽKA AK EXISTUJE používateľov;
VYTVORIŤ TABUĽKA používateľov(id INT PRIMÁRNY KĽÚČ NIE NULOVÝ AUTOMATICKÝ PRÍRASTOK ,používateľské meno VARCHAR (10) NIE NULOVÝ ,celé meno VARCHAR (dvadsať),e -mail VARCHAR (255) NIE NULOVÝ );
VLOŽIŤ DO používateľov(používateľské meno,celé meno,e -mail) HODNOTY
('Panna', „Claude M. Mori“, '[email protected]'),
('stlač', „Tiffany G. Bailey“, '[email protected]'),
('raketa', „Christopher S. Payton“, '[email protected]'),
('temná hmota', 'Patricia J. Fox', '[email protected]'),
('predmet', 'Faye H. Hartley', '[email protected]'),
('temná hmota', 'Patricia J. Fox', '[email protected]'),
('raketa', „Christopher S. Payton“, '[email protected]'),
(„artemis“, „Wesley C. Dillard“, '[email protected]');
Neváhajte upraviť vyššie uvedený dopyt tak, aby vyhovoval vašim potrebám. Mali by ste tiež zaistiť, aby ste mali vytvorenú databázu (svet), aby ste sa vyhli chybám.
Ak teraz dostaneme všetky údaje do tabuľky a zoradené podľa používateľského mena, uvidíme duplikáty, ktoré máme, ako je uvedené na obrázku:
mysql> používať svet;Databáza zmenené
mysql> VYBERTE * OD používateľov ZORADIŤ PODĽA používateľské meno;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|id|používateľské meno|celé meno|e -mail|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |artemis|Wesley C. Dillard|[chránené e -mailom]|
| 4 |temná hmota|Patricia J. Fox|[chránené e -mailom]|
| 6 |temná hmota|Patricia J. Fox|[chránené e -mailom]|
| 2 |stlačte|Tiffany G. Bailey|[chránené e -mailom]|
| 5 |predmet|Faye H. Hartley|[chránené e -mailom]|
| 3 |raketa|Christopher S. Payton|[chránené e -mailom]|
| 7 |raketa|Christopher S. Payton|[chránené e -mailom]|
| 1 |Panna|Claude M. Mori|[chránené e -mailom]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
Ako vidíte z vyššie uvedenej tabuľky, máme dve duplicitné hodnoty, ktoré databázu bez dôvodu zväčšujú a spôsobujú nízke rýchlosti.
Teraz sa naučíme, ako môžeme tieto hodnoty odstrániť.
#1 - VYMAZAŤ PRIPOJENIE
Jeden zo spôsobov, ako odstrániť duplicitné riadky z databázy, je použiť príkaz MySQL DELETE JOIN. Dotaz však používa ID na odstránenie duplicitných hodnôt.
Ak napríklad chceme odstrániť duplicitné hodnoty v tabuľke používateľov vyššie, môžeme zadať:
VYMAZAŤ stôl 1 OD tabuľka užívateľov1 VNÚTORNÉ PRIPOJTE SA tabuľka užívateľov2 KDE tabuľka1.id<table2.id A stôl1.email=table2.email;Po vykonaní vyššie uvedeného dotazu odstránite duplicitné hodnoty, ako je uvedené v nižšie uvedenom výstupe:
mysql> VYMAZAŤ stôl 1 OD tabuľka užívateľov1 VNÚTORNÉ PRIPOJTE SA tabuľka užívateľov2 KDE tabuľka1.id<table2.id A stôl1.email=table2.email;Dotaz OK, 2ovplyvnené riadky(0,01sek)
mysql> VYBERTE * OD používateľov ZORADIŤ PODĽA používateľské meno;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|id|používateľské meno|celé meno|e -mail|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |artemis|Wesley C. Dillard|[chránené e -mailom]|
| 6 |temná hmota|Patricia J. Fox|[chránené e -mailom]|
| 2 |stlačte|Tiffany G. Bailey|[chránené e -mailom]|
| 5 |predmet|Faye H. Hartley|[chránené e -mailom]|
| 7 |raketa|Christopher S. Payton|[chránené e -mailom]|
| 1 |Panna|Claude M. Mori|[chránené e -mailom]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
#2 - Funkcia Row_Number ()
Druhou metódou, ktorú môžeme implementovať, je použiť funkciu MySQL row_number (). Táto funkcia je podporovaná v MySQL verzii 8 a vyššej.
Funguje to tak, že každému riadku priradíte sekvenčnú hodnotu int, pričom riadky obsahujúce duplicitné hodnoty získajú hodnotu vyššiu ako 1.
Ak sa chcete dozvedieť viac o tejto funkcii, použite nižšie uvedený zdroj:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
Zvážte nasledujúci dotaz, ktorý vracia id riadkov s duplicitnými hodnotami:
VYBERTE id OD ( VYBERTE id,ROW_NUMBER()NADŽIŤ( ROZDELENIE POD užívateľským menom ZORADIŤ PODĽA používateľské meno) AS riadok_var OD používateľov)t1 KDE riadok_var> 1;Po vykonaní vyššie uvedeného dotazu by ste mali dostať zoznam ID, ako je uvedené v nižšie uvedenom výstupe:
+ ---- +|id|
+ ---- +
| 6 |
| 7 |
+ ---- +
2riadky v nastaviť (0,01sek)
Ak chcete hodnoty odstrániť, jednoducho nahraďte príkaz SELECT príkazom DELETE, ako je uvedené nižšie:
VYMAZAŤ OD používateľov KDE id IN ( VYBERTE id OD ( VYBERTE id,ROW_NUMBER()NADŽIŤ( ROZDELENIE POD užívateľským menom ZORADIŤ PODĽA používateľské meno) AS riadok_var OD používateľov)t1 KDE riadok_var> 1);Nakoniec môžete pomocou príkazu SELECT overiť, či sú duplicitné hodnoty odstránené.
mysql> VYBERTE * od používateľov ZORADIŤ PODĽA používateľské meno;+ ---- + ------------ + ----------------------- + ------- ---------------- +
|id|používateľské meno|celé meno|e -mail|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |artemis|Wesley C. Dillard|[chránené e -mailom]|
| 4 |temná hmota|Patricia J. Fox|[chránené e -mailom]|
| 2 |stlačte|Tiffany G. Bailey|[chránené e -mailom]|
| 5 |predmet|Faye H. Hartley|[chránené e -mailom]|
| 3 |raketa|Christopher S. Payton|[chránené e -mailom]|
| 1 |Panna|Claude M. Mori|[chránené e -mailom]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
Záver
V tomto návode sme diskutovali o dvoch metódach odstraňovania duplicitných hodnôt z databázy. Veľké databázy, najmä tie, ktoré sa bežne používajú, môžu obsahovať mnoho duplicitných hodnôt z externých importov a ďalších chýb. Preto je potrebné neustále odstraňovať duplicitné hodnoty, aby sa zaistilo optimálne fungovanie aplikácií.