V tej vadnici se bomo naučili, kako odstraniti podvojene vrstice v bazi podatkov MySQL, da zmanjšamo velikost baze podatkov in pomagamo povečati zmogljivost strežnika.
Preden nadaljujemo, predvidevamo:
- V sistemu imate nameščen in delujoč MySQL
- Do baze podatkov imate korenski dostop.
- Imate dostop do baze podatkov za eksperimentiranje ali testiranje
OPOMBA : Če za preizkušanje konceptov v tem priročniku potrebujete vzorčno zbirko podatkov, razmislite o zbirki podatkov Sakila ali prenesite kopijo zbirke podatkov, uporabljene v tem priročniku.
Viri so navedeni spodaj:
Osnovna uporaba
Preden začnemo, bomo za namene testiranja namerno ustvarili tabelo, ki vsebuje podvojene vrednosti. Spodaj so poizvedbe SQL za izvedbo tega dejanja:
UPORABA svet;DROP TABELA ČE OBSTAJA uporabniki;
Ustvari TABELA uporabniki(id INT PRIMARNI KLJUČ NE NIČ AUTO_INCREMENT ,uporabniško ime VARCHAR (10) NE NIČ ,polno ime VARCHAR (dvajset),E-naslov VARCHAR (255) NE NIČ );
VSTAVI INTO uporabniki(uporabniško ime,polno ime,E-naslov) VREDNOTE
('Devica', 'Claude M. Mori', '[zaščiteno po e -pošti]'),
('pritisni', 'Tiffany G. Bailey', '[zaščiteno po e -pošti]'),
('raketa', 'Christopher S. Payton', '[zaščiteno po e -pošti]'),
('temna snov', 'Patricia J. Fox', '[zaščiteno po e -pošti]'),
('predmet', 'Faye H. Hartley', '[zaščiteno po e -pošti]'),
('temna snov', 'Patricia J. Fox', '[zaščiteno po e -pošti]'),
('raketa', 'Christopher S. Payton', '[zaščiteno po e -pošti]'),
('artemida', 'Wesley C. Dillard', '[zaščiteno po e -pošti]');
Zgornjo poizvedbo lahko prilagodite svojim potrebam. Prav tako morate zagotoviti, da imate bazo podatkov (svet) ustvarjeno, da se izognete napakam.
Zdaj, če dobimo vse podatke v tabeli in po vrstnem redu po uporabniškem imenu, bomo videli podvojene podatke, kot je prikazano:
mysql> uporaba svet;Baza podatkov spremenil
mysql> IZBERI * IZ uporabniki NAROČI PO uporabniško ime;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|id|uporabniško ime|polno ime|E-naslov|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |artemida|Wesley C. Dillard|[zaščiteno po e -pošti]|
| 4 |temna snov|Patricia J. Fox|[zaščiteno po e -pošti]|
| 6 |temna snov|Patricia J. Fox|[zaščiteno po e -pošti]|
| 2 |pritisnite|Tiffany G. Bailey|[zaščiteno po e -pošti]|
| 5 |predmet|Faye H. Hartley|[zaščiteno po e -pošti]|
| 3 |raketo|Christopher S. Payton|[zaščiteno po e -pošti]|
| 7 |raketo|Christopher S. Payton|[zaščiteno po e -pošti]|
| 1 |Devica|Claude M. Mori|[zaščiteno po e -pošti]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
Kot lahko vidite iz zgornje tabele, imamo dve podvojeni vrednosti, ki povečujeta bazo podatkov brez razloga in povzročata počasne hitrosti.
Zdaj se naučimo, kako lahko te vrednosti odstranimo.
#1 - IZBRIŠI JOIN
Eden od načinov za odstranitev podvojenih vrstic v zbirki podatkov je uporaba stavka MySQL DELETE JOIN. Poizvedba pa uporablja ID -je za odstranjevanje podvojenih vrednosti.
Če želite na primer odstraniti podvojene vrednosti v zgornji tabeli uporabnikov, lahko vnesemo:
IZBRIŠI miza 1 IZ tabela uporabnikov1 NOTRANJA PRIDRUŽITE SE uporabniška miza2 KJE table1.id<table2.id IN table1.email=table2.email;Ko izvedete zgornjo poizvedbo, boste odstranili podvojene vrednosti, kot je prikazano v spodnjem izhodu:
mysql> IZBRIŠI miza 1 IZ tabela uporabnikov1 NOTRANJA PRIDRUŽITE SE uporabniška miza2 KJE table1.id<table2.id IN table1.email=table2.email;Poizvedba v redu, 2prizadete vrstice(0,01sek)
mysql> IZBERI * IZ uporabniki NAROČI PO uporabniško ime;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|id|uporabniško ime|polno ime|E-naslov|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |artemida|Wesley C. Dillard|[zaščiteno po e -pošti]|
| 6 |temna snov|Patricia J. Fox|[zaščiteno po e -pošti]|
| 2 |pritisnite|Tiffany G. Bailey|[zaščiteno po e -pošti]|
| 5 |predmet|Faye H. Hartley|[zaščiteno po e -pošti]|
| 7 |raketo|Christopher S. Payton|[zaščiteno po e -pošti]|
| 1 |Devica|Claude M. Mori|[zaščiteno po e -pošti]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
#2 - Funkcija Row_Number ()
Druga metoda, ki jo lahko izvedemo, je uporaba funkcije MySQL row_number (). Ta funkcija je podprta v različici MySQL 8 in novejših.
Deluje tako, da vsaki vrstici dodeli zaporedno vrednost int, pri čemer vrstice, ki vsebujejo podvojene vrednosti, dobijo vrednost, višjo od 1.
Če želite izvedeti več o tej funkciji, uporabite spodnji vir:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
Razmislite o spodnji poizvedbi, ki vrne id vrstic z podvojenimi vrednostmi:
IZBERI id IZ ( IZBERI id,ROW_NUMBER()PREKO( DELA PO uporabniškem imenu NAROČI PO uporabniško ime) AS row_var IZ uporabniki)t1 KJE row_var> 1;Ko izvedete zgornjo poizvedbo, bi morali dobiti seznam ID -jev, kot je prikazano v spodnjem izhodu:
+ ---- +|id|
+ ---- +
| 6 |
| 7 |
+ ---- +
2vrstice v nastavljeno (0,01sek)
Če želite odstraniti vrednosti, preprosto zamenjajte stavek SELECT z stavkom DELETE, kot je prikazano spodaj:
IZBRIŠI IZ uporabniki KJE id IN ( IZBERI id IZ ( IZBERI id,ROW_NUMBER()PREKO( DELA PO uporabniškem imenu NAROČI PO uporabniško ime) AS row_var IZ uporabniki)t1 KJE row_var> 1);Nazadnje lahko s stavkom SELECT preverite, ali so podvojene vrednosti odstranjene.
mysql> IZBERI * od uporabniki NAROČI PO uporabniško ime;+ ---- + ------------ + ----------------------- + ------- ---------------- +
|id|uporabniško ime|polno ime|E-naslov|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |artemida|Wesley C. Dillard|[zaščiteno po e -pošti]|
| 4 |temna snov|Patricia J. Fox|[zaščiteno po e -pošti]|
| 2 |pritisnite|Tiffany G. Bailey|[zaščiteno po e -pošti]|
| 5 |predmet|Faye H. Hartley|[zaščiteno po e -pošti]|
| 3 |raketo|Christopher S. Payton|[zaščiteno po e -pošti]|
| 1 |Devica|Claude M. Mori|[zaščiteno po e -pošti]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
Zaključek
V tej vadnici smo razpravljali o dveh metodah odstranjevanja podvojenih vrednosti iz baze podatkov. Velike zbirke podatkov, zlasti tiste v skupni rabi, lahko vsebujejo veliko podvojenih vrednosti iz zunanjega uvoza in drugih napak. Zato je treba še naprej čistiti podvojene vrednosti, da zagotovimo optimalno delovanje aplikacij.