W tym samouczku dowiemy się, jak usunąć zduplikowane wiersze w bazie danych MySQL, aby zmniejszyć rozmiar bazy danych i pomóc zwiększyć wydajność serwera.
Zanim przejdziemy dalej, zakładamy:
- Masz zainstalowany i uruchomiony MySQL w swoim systemie
- Masz uprawnienia administratora do bazy danych.
- Masz dostęp do bazy danych do eksperymentowania lub testowania
NOTATKA : Jeśli potrzebujesz przykładowej bazy danych, aby wypróbować koncepcje przedstawione w tym przewodniku, rozważ bazę danych Sakila lub pobierz kopię bazy danych użytej w tym przewodniku.
Zasoby są podane poniżej:
Podstawowe użycie
Zanim zaczniemy, celowo utworzymy tabelę zawierającą zduplikowane wartości do celów testowych. Poniżej znajdują się zapytania SQL do wykonania tej akcji:
POSŁUGIWAĆ SIĘ świat;UPUSZCZAĆ TABELA JEŚLI ISTNIEJE użytkownicy;
STWÓRZ TABELA użytkownicy(NS WEWN GŁÓWNY KLUCZ NIE ZERO AUTO_INCREMENT ,Nazwa Użytkownika VARCHAR (10) NIE ZERO ,pełna_nazwa VARCHAR (20),e-mail VARCHAR (255) NIE ZERO );
WSTAWIĆ DO użytkownicy(Nazwa Użytkownika,pełna_nazwa,e-mail) WARTOŚCI
('Panna', „Claude M. Mori”, ' [e-mail chroniony] '),
('naciskać', „Tiffany G. Bailey”, ' [e-mail chroniony] '),
('rakieta', „Christopher S. Payton”, ' [e-mail chroniony] '),
('Ciemna materia', „Patricia J. Fox”, ' [e-mail chroniony] '),
('Przedmiot', 'Faye H. Hartley', ' [e-mail chroniony] '),
('Ciemna materia', „Patricia J. Fox”, ' [e-mail chroniony] '),
('rakieta', „Christopher S. Payton”, ' [e-mail chroniony] '),
(„artemida”, Wesley C. Dillard, ' [e-mail chroniony] ');
Zachęcamy do modyfikowania powyższego zapytania, aby dopasować je do swoich potrzeb. Powinieneś również upewnić się, że masz stworzoną bazę danych (świat), aby uniknąć błędów.
Teraz, jeśli otrzymamy wszystkie dane w tabeli i uporządkowane według nazwy użytkownika, zobaczymy duplikaty, które mamy, jak pokazano:
mysql> posługiwać się świat;Baza danych zmieniony
mysql> WYBIERZ * Z użytkownicy ZAMÓW PRZEZ Nazwa Użytkownika;
+ ---- + ------------ + ------------ + ------- ---------------- +
|NS|Nazwa Użytkownika|pełna_nazwa|e-mail|
+ ---- + ------------ + ------------ + ------- ---------------- +
| 8 |artemida|Wesley C. Dillard|[e-mail chroniony]|
| 4 |Ciemna materia|Patricia J. Fox|[e-mail chroniony]|
| 6 |Ciemna materia|Patricia J. Fox|[e-mail chroniony]|
| 2 |naciskać|Tiffany G. Bailey|[e-mail chroniony]|
| 5 |Przedmiot|Faye H. Hartley|[e-mail chroniony]|
| 3 |rakieta|Christopher S. Payton|[e-mail chroniony]|
| 7 |rakieta|Christopher S. Payton|[e-mail chroniony]|
| 1 |panna|Claude M. Mori|[e-mail chroniony]|
+ ---- + ------------ + ------------ + ------- ---------------- +
Jak widać z powyższej tabeli, mamy dwie zduplikowane wartości, które bez powodu powiększają bazę danych i powodują powolne prędkości.
Nauczmy się teraz, jak możemy usunąć te wartości.
#1 – USUŃ DOŁĄCZ
Jednym ze sposobów usunięcia zduplikowanych wierszy w bazie danych jest użycie instrukcji MySQL DELETE JOIN. Zapytanie jednak używa identyfikatorów do usuwania zduplikowanych wartości.
Na przykład, aby usunąć zduplikowane wartości w powyższej tabeli użytkowników, możemy wprowadzić:
KASOWAĆ Tabela 1 Z tabela użytkowników1 WEWNĘTRZNY PRZYSTĄP tabela użytkowników2 GDZIE table1.id<table2.id ORAZ tabela1.e-mail=tabela2.e-mail;Po wykonaniu powyższego zapytania usuniesz zduplikowane wartości, jak pokazano na poniższym wyjściu:
mysql> KASOWAĆ Tabela 1 Z tabela użytkowników1 WEWNĘTRZNY PRZYSTĄP tabela użytkowników2 GDZIE table1.id<table2.id ORAZ tabela1.e-mail=tabela2.e-mail;Zapytanie OK, 2wiersze dotknięte(0,01sek)
mysql> WYBIERZ * Z użytkownicy ZAMÓW PRZEZ Nazwa Użytkownika;
+ ---- + ------------ + ------------ + ------- ---------------- +
|NS|Nazwa Użytkownika|pełna_nazwa|e-mail|
+ ---- + ------------ + ------------ + ------- ---------------- +
| 8 |artemida|Wesley C. Dillard|[e-mail chroniony]|
| 6 |Ciemna materia|Patricia J. Fox|[e-mail chroniony]|
| 2 |naciskać|Tiffany G. Bailey|[e-mail chroniony]|
| 5 |Przedmiot|Faye H. Hartley|[e-mail chroniony]|
| 7 |rakieta|Christopher S. Payton|[e-mail chroniony]|
| 1 |panna|Claude M. Mori|[e-mail chroniony]|
+ ---- + ------------ + ------------ + ------- ---------------- +
#2 – Funkcja Row_Number()
Drugą metodą, którą możemy zaimplementować, jest użycie funkcji MySQL row_number(). Ta funkcja jest obsługiwana w MySQL w wersji 8 i wyższych.
Działa poprzez przypisanie sekwencyjnej wartości int do każdego wiersza, przy czym wiersze zawierające zduplikowane wartości uzyskują wartość wyższą niż 1.
Aby dowiedzieć się więcej o tej funkcji, skorzystaj z poniższego zasobu:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
Rozważ poniższe zapytanie, które zwraca identyfikatory wierszy ze zduplikowanymi wartościami:
WYBIERZ NS Z ( WYBIERZ NS,NUMER WIERSZA()NAD( PRZEGRODA WG nazwy użytkownika ZAMÓW PRZEZ Nazwa Użytkownika) JAK wiersz_zmienna Z użytkownicy)t1 GDZIE wiersz_zmienna> 1;Po wykonaniu powyższego zapytania powinieneś otrzymać listę identyfikatorów, jak pokazano na poniższym wyjściu:
+ ---- +|NS|
+ ---- +
| 6 |
| 7 |
+ ---- +
2wydziwianie w ustawić (0,01sek)
Jeśli chcesz usunąć wartości, po prostu zastąp instrukcję SELECT instrukcją DELETE, jak pokazano poniżej:
KASOWAĆ Z użytkownicy GDZIE NS W ( WYBIERZ NS Z ( WYBIERZ NS,NUMER WIERSZA()NAD( PRZEGRODA WG nazwy użytkownika ZAMÓW PRZEZ Nazwa Użytkownika) JAK wiersz_zmienna Z użytkownicy)t1 GDZIE wiersz_zmienna> 1);Na koniec możesz sprawdzić, czy zduplikowane wartości zostały usunięte za pomocą instrukcji SELECT.
mysql> WYBIERZ * z użytkownicy ZAMÓW PRZEZ Nazwa Użytkownika;+ ---- + ------------ + ------------ + ------- ---------------- +
|NS|Nazwa Użytkownika|pełna_nazwa|e-mail|
+ ---- + ------------ + ------------ + ------- ---------------- +
| 8 |artemida|Wesley C. Dillard|[e-mail chroniony]|
| 4 |Ciemna materia|Patricia J. Fox|[e-mail chroniony]|
| 2 |naciskać|Tiffany G. Bailey|[e-mail chroniony]|
| 5 |Przedmiot|Faye H. Hartley|[e-mail chroniony]|
| 3 |rakieta|Christopher S. Payton|[e-mail chroniony]|
| 1 |panna|Claude M. Mori|[e-mail chroniony]|
+ ---- + ------------ + ------------ + ------- ---------------- +
Wniosek
W tym samouczku omówiliśmy dwie metody usuwania zduplikowanych wartości z bazy danych. Duże bazy danych, zwłaszcza te, które są powszechnie używane, mogą zawierać wiele zduplikowanych wartości pochodzących z importów zewnętrznych i innych błędów. Dlatego istnieje potrzeba usuwania zduplikowanych wartości, aby zapewnić optymalne działanie aplikacji.