Уроки 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, которое должно быть уникальным. Обращайте и на такие нюансы внимание.

 

Recent Posts

Что такое поддомен (субдомен) и когда его использовать | SEO и примеры

Представьте, что ваш основной сайт – это большой дом, стоящий на вашем земельном участке (example.com).…

7 часов ago

Френк МакКинни Кин Хаббард

Есть два способа командовать женщиной, но никто их не знает Френк МакКинни Кин Хаббард  

2 дня ago

Роберт Шекли

Самое обидное, что в информационной войне всегда проигрывает тот, кто говорит правду, ведь он ограничен…

3 дня ago

Ричард Бах

Если тебе когда-нибудь захочется найти такого человека, который сможет одолеть любую, даже самую тяжелую беду…

4 дня ago

Что такое CDN и почему он нужен вашему сайту | обзор в 2025

Представьте себе, что вы открыли пиццерию в Киеве. Ваша пицца настолько вкусна, что ее хотят…

1 неделя ago

Что такое Varnish Cache и как он ускорит ваш сайт (начинающим)

Представьте, что ваш сайт – это большая библиотека, а ваш сервер – это главный библиотекарь.…

1 неделя ago