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

SebWeo.com

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

Чи можна це виправити? Так, і в сьогоднішньому практичному уроці ми розглянемо методи знаходження дубльованих даних та боротьби з ними.

Що вам знадобиться:

Доступ до бази даних. Можна через командний рядок, але краще використовувати інструмент адміністрування з графічним інтерфейсом: наприклад 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, яке має бути унікальним. Звертайте і на такі нюанси увагу.