В този урок ще научим как да премахваме дублиращи се редове в база данни MySQL, за да намалим размера на базата данни и да помогнем за увеличаване на производителността на сървъра.
Преди да продължим, приемаме:
- Имате MySQL инсталиран и работещ на вашата система
- Имате root достъп до базата данни.
- Имате достъп до база данни за експериментиране или тестване
ЗАБЕЛЕЖКА : Ако имате нужда от примерна база данни, за да изпробвате концепциите, предоставени в това ръководство, моля, помислете за базата данни Sakila или изтеглете копие от базата данни, използвана в това ръководство.
Ресурсите са предоставени по -долу:
Основно използване
Преди да започнем, умишлено ще създадем таблица, съдържаща дублирани стойности за целите на тестването. SQL заявките за извършване на това действие са по -долу:
ИЗПОЛЗВАЙТЕ света;ИЗПУСКАЙТЕ ТАБЛИЦА АКО СЪЩЕСТВУВА потребители;
СЪЗДАВАЙТЕ ТАБЛИЦА потребители(документ за самоличност INT ОСНОВЕН КЛЮЧ НЕ НУЛА АВТОМАТИЧНО УВЕЛИЧАВАНЕ ,потребителско име ВАРЧАР (10) НЕ НУЛА ,пълно име ВАРЧАР (двайсет),електронна поща ВАРЧАР (255) НЕ НУЛА );
ИНСЕРТ ВЪВ потребители(потребителско име,пълно име,електронна поща) СТОЙНОСТИ
('Зодия Дева', „Клод М. Мори“, '[защитен имейл]'),
('Натиснете', „Тифани Г. Бейли“, '[защитен имейл]'),
('ракета', „Кристофър С. Пейтън“, '[защитен имейл]'),
('тъмна материя', 'Патриша Дж. Фокс', '[защитен имейл]'),
('предмет', „Фей Х. Хартли“, '[защитен имейл]'),
('тъмна материя', 'Патриша Дж. Фокс', '[защитен имейл]'),
('ракета', „Кристофър С. Пейтън“, '[защитен имейл]'),
('артемида', „Уесли К. Дилард“, '[защитен имейл]');
Чувствайте се свободни да промените заявката по -горе, за да отговаря на вашите нужди. Трябва също така да се уверите, че имате създадена база данни (свят), за да избегнете грешки.
Сега, ако получим всички данни в таблицата и подредени по потребителско име, ще видим дубликатите, които имаме, както е показано:
mysql> използвайте света;База данни променен
mysql> SELECT * ОТ потребители ПОДРЕДЕНИ ПО потребителско име;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|документ за самоличност|потребителско име|пълно име|електронна поща|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |артемида|Уесли С. Дилард|[защитен имейл]|
| 4 |тъмна материя|Патриша Дж. Фокс|[защитен имейл]|
| 6 |тъмна материя|Патриша Дж. Фокс|[защитен имейл]|
| 2 |Натиснете|Тифани Г. Бейли|[защитен имейл]|
| 5 |предмет|Фей Х. Хартли|[защитен имейл]|
| 3 |ракета|Кристофър С. Пейтън|[защитен имейл]|
| 7 |ракета|Кристофър С. Пейтън|[защитен имейл]|
| 1 |зодия Дева|Клод М. Мори|[защитен имейл]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
Както можете да видите от горната таблица, имаме две дублирани стойности, които правят базата данни по -голяма без причина и причиняват бавни скорости.
Нека сега научим как можем да премахнем тези стойности.
#1 - ИЗТРИВАНЕ НА ПРИСЪЕДИНЯВАНЕ
Един от начините за премахване на дублиращи се редове в база данни е използването на оператора MySQL DELETE JOIN. Заявката обаче използва идентификатори за премахване на дублиращи се стойности.
Например, за да премахнем дублиращите се стойности в таблицата с потребители по -горе, можем да въведем:
ИЗТРИЙ маса 1 ОТ потребителска таблица1 АТРЕШНА ПРИСЪЕДИНЯВАНЕ потребителска таблица2 КЪДЕТО table1.id<table2.id И table1.email=table2.email;След като изпълните заявката по -горе, ще премахнете дублиращите се стойности, както е показано в изхода по -долу:
mysql> ИЗТРИЙ маса 1 ОТ потребителска таблица1 АТРЕШНА ПРИСЪЕДИНЯВАНЕ потребителска таблица2 КЪДЕТО table1.id<table2.id И table1.email=table2.email;Заявка ОК, 2засегнати редове(0,01сек)
mysql> SELECT * ОТ потребители ПОДРЕДЕНИ ПО потребителско име;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|документ за самоличност|потребителско име|пълно име|електронна поща|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |артемида|Уесли С. Дилард|[защитен имейл]|
| 6 |тъмна материя|Патриша Дж. Фокс|[защитен имейл]|
| 2 |Натиснете|Тифани Г. Бейли|[защитен имейл]|
| 5 |предмет|Фей Х. Хартли|[защитен имейл]|
| 7 |ракета|Кристофър С. Пейтън|[защитен имейл]|
| 1 |зодия Дева|Клод М. Мори|[защитен имейл]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
#2 - Функция Row_Number ()
Вторият метод, който можем да приложим, е да използваме функцията MySQL row_number (). Тази функция се поддържа в MySQL версия 8 и по -нова.
Той работи, като присвоява последователна int стойност на всеки ред, като редовете, съдържащи дублирани стойности, получават стойност, по -висока от 1.
За да научите повече за тази функция, използвайте предоставения по -долу ресурс:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
Помислете за заявката по -долу, която връща идентификатора на редовете с дублирани стойности:
SELECT документ за самоличност ОТ ( SELECT документ за самоличност,ROW_NUMBER()НАД( ЧАСТ ПО потребителско име ПОДРЕДЕНИ ПО потребителско име) КАТО row_var ОТ потребители)t1 КЪДЕТО row_var> 1;След като изпълните горната заявка, трябва да получите списъка с идентификатори, както е показано в изхода по -долу:
+ ---- +|документ за самоличност|
+ ---- +
| 6 |
| 7 |
+ ---- +
2редове в комплект (0,01сек)
Ако искате да премахнете стойностите, просто заменете израза SELECT с инструкцията DELETE, както е показано по -долу:
ИЗТРИЙ ОТ потребители КЪДЕТО документ за самоличност IN ( SELECT документ за самоличност ОТ ( SELECT документ за самоличност,ROW_NUMBER()НАД( ЧАСТ ПО потребителско име ПОДРЕДЕНИ ПО потребителско име) КАТО row_var ОТ потребители)t1 КЪДЕТО row_var> 1);И накрая, можете да проверите дали дублиращите се стойности са премахнати с помощта на израза SELECT.
mysql> SELECT * от потребители ПОДРЕДЕНИ ПО потребителско име;+ ---- + ------------ + ----------------------- + ------- ---------------- +
|документ за самоличност|потребителско име|пълно име|електронна поща|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |артемида|Уесли С. Дилард|[защитен имейл]|
| 4 |тъмна материя|Патриша Дж. Фокс|[защитен имейл]|
| 2 |Натиснете|Тифани Г. Бейли|[защитен имейл]|
| 5 |предмет|Фей Х. Хартли|[защитен имейл]|
| 3 |ракета|Кристофър С. Пейтън|[защитен имейл]|
| 1 |зодия Дева|Клод М. Мори|[защитен имейл]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
Заключение
В този урок обсъдихме двата метода за премахване на дублиращи се стойности от база данни. Големите бази данни, особено тези в обща употреба, могат да съдържат много дублирани стойности от външен внос и други грешки. Следователно е необходимо да се поддържа изчистване на дублиращи се стойности, за да се гарантира, че приложенията работят оптимално.