Уроки SQL — как найти повторяющиеся записи (дубли) в базе данных

 

 

Во-первых, чтобы не допустить подобных ситуаций, вам заранее нужно присваивать уникальные значения в таблицах. Так вы сможете избежать дублирования записей (строк). Однако, не все в этом мире идеально, и рано или поздно вы можете обнаружить дублирующие строки в своей базе данных: из-за банальной человеческой ошибки, ошибок в коде приложений, неверно обработанных данных, нашествия инопланетян.

Это можно исправить? Да, и в сегодняшнем практическом уроке мы рассмотрим методы нахождения дублирующих данных и борьбы с ними.

 

Что вам потребуется:

• Доступ к базе данных. Можно через командную строку, но лучше использовать инструмент администрирования с графическим интерфейсом: например, HeidiSQL, phpMyAdmin, Adminer и т.п.
• Уметь писать SQL-запросы. Ну, или уметь копировать/вставлять код.

 

Что будем искать (критерий нежелательной копии записи)

Сначала определимся, что имеется ввиду под понятием дублирующей строки. Например, в своей практике я столкнулся с тем, что при работе с одним популярным плагином для SEO продвижения в WordPress создается много повторяющихся записей в базе данных. Речь идет о записях мета-данных title, description и keywords для публикаций в блоге. Они записываются в таблицу wp_postmeta и еще отдельно в таблицу плагина wp_aioseo_posts. Когда мне нужно было сделать общую выборку из этих двух таблиц, я обратил внимание на то, что для некоторых постов данные содержатся в разных строках. То есть, для одного поста с уникальным значением post_id было несколько различных (нежелательных) строк. Возможно, это произошло после обновления плагина, и разработчики сменили название ключа для поля мета-данных с _aioseo_ на _aioseop_ (фактически добавив одну букву)? Скорее всего. И в результате база данных обогатилась на дубликаты. У вас может быть и другая ситуация, но решение в данном уроке может быть вполне применимо и к ней.

Итак, теперь перейдем от теории к практике.

 

Соединяем данные из двух SQL таблиц

Для начала напишем SQL-запрос, который делает выборку из двух таблиц базы данных и записывает результаты в новую (существующую) таблицу new_table:

INSERT INTO `new_table`(`post_id`, `title`, `description`, `keywords`, `seo_title`)
SELECT aio.post_id, title, description, keywords, wp.meta_value
FROM wp_aioseo_posts AS aio
LEFT JOIN wp_postmeta AS wp
ON wp.post_id = aio.post_id
AND wp.meta_key = '_aioseop_title'
ORDER BY post_id;

 

Если у вас возникают трудности на этапе выборки из двух и более таблиц, рекомендую ознакомиться с тематическим уроком Знакомство с операторами INNER, LEFT, RIGHT, SELF и CROSS JOIN.

 

Этот запрос работает, и мы получаем данные из двух таблиц. В примерно таком виде:

+---------+----------------+------------------+----------+--------------------+
| post_id | title          | description      | keywords | meta_value         |
+---------+----------------+------------------+----------+--------------------+
|      1  | Blah-blah-blah | Blah, blah, blah | blah     | blah – blah – blah |
|      2  | Blah-blah-blah | Blah, blah, blah | blah     | blah – blah – blah |
|      3  | Blah-blah-blah | Blah, blah, blah | blah     | blah – blah – blah |
+---------+----------------+------------------+----------+--------------------+

 

Но что, если расширить выборку? Ведь meta_key в таблице wp_postmeta может иметь значение и _aioseo_title, и _aioseop_title? Тогда в новую таблицу запишутся дубликаты. И теперь пришла пора отыскать эти дублирующиеся данные.

 

Как с помощью SQL запроса найти все строки, которые содержат дубликаты?

Для этого вы можете использовать функцию COUNT(column_name) и выражение GROUP BY.

В моем примере: я хочу найти и сгруппировать данные по столбцам post_id и seo_title, а также получить количество полученных дублей.

Вот такой будет SQL запрос:

SELECT post_id, seo_title, COUNT(*)
FROM new_table
GROUP BY post_id, seo_title
HAVING COUNT(*) > 1

 

Результатом будет нечто подобное:

+---------+------------------------------------------+----------+
| post_id | seo_title                                | COUNT(*) |
+---------+------------------------------------------+----------+
|     514 | Легендарному сериалу Друзья — 20 лет!    |        2 |
|     514 | NULL                                     |        2 |
|     903 | Как добавить свой сайт в Google Maps     |        2 |
|     903 | NULL                                     |        2 |
=================================================================
+---------+------------------------------------------+----------+
44 rows in set (0.01 sec)

 

Как видно из результата, в таблице есть дубликаты (и их может быть немало), а в отдельной колонке COUNT(*) указано их количество. Я показал только часть результатов, но что делать, если таких повторяющихся данных тысячи?

 

Что можно сделать с повторяющимися данными в SQL таблице?

Простейший способ — это игнорировать дубликаты с помощью ключевого слова DISTINCT, которое нужно добавить в функцию COUNT(). Например, так:

SELECT post_id, seo_title, COUNT(DISTINCT seo_title)
FROM new_table
GROUP BY post_id

 

Ключевое слово DISTINCT позволяет игнорировать повторяющиеся данные, и выбирать только уникальные. В результате я получил список уникальных данных, без повторяющихся строк. Примечательно, что вполне вероятно и возможны дубликаты, которые не являются ошибочными. Например, в моей базе данных есть повторяющиеся заголовки у постов с типом Цитата, ведь у одного автора может быть и не одна цитата, а seo_title при этом может быть одинаков. И это не будет ошибкой. Поэтому, я и добавил группировку по полю post_id, которое должно быть уникальным. Обращайте и на такие нюансы внимание.