Поиск дубликатов MySQL

Сборник запросов для поиска, изменения и удаления дублей в таблице MySQL по одному и нескольким полям. В примерах все запросы будут применятся к следующий таблице:

В примерах все запросы будут применятся к следующий таблице

Подсчет дублей

Запрос подсчитает количество всех записей с одинаковыми значениями в поле `col1`.

SELECT
	`col1`,
	COUNT(`col1`) AS `count`
FROM
	`table`
GROUP BY
	`col1`
HAVING 
	`count` > 1
SQL

Подсчет дубликатов по нескольким полям:

SELECT
	`col1`,
	`col2`,
	COUNT(*) AS `count`
FROM
	`table`
GROUP BY
	`col1`,`col2`
HAVING 
	`count` > 1
SQL

Все записи с одинаковыми значениями

Запрос найдет все записи с одинаковыми значениями в `col1`.

SELECT 
	*
FROM 
	`table`
WHERE 
	`col1` IN (SELECT `col1` FROM `table` GROUP BY `col1` HAVING COUNT(*) > 1)
ORDER BY
	`col1`
SQL

Для одинаковых значений в `col1` и `col2`:

SELECT 
	*
FROM 
	`table`
WHERE 
	`col1` IN (SELECT `col1` FROM `table` GROUP BY `col1` HAVING COUNT(*) > 1)
	AND `col2` IN (SELECT `col2` FROM `table` GROUP BY `col2` HAVING COUNT(*) > 1)
ORDER BY
	`col1`
SQL

Получить только дубликаты

Запрос получит только дубликаты, в результат не попадают записи с самым ранним `id`.

SELECT 
	`table`.*
FROM
	`table`
LEFT OUTER JOIN 
	(SELECT MIN(`id`) AS `id`, `col1` FROM `table` GROUP BY `col1`) AS `tmp` 
ON 
	`table`.`id` = `tmp`.`id`  
WHERE
	`tmp`.`id` IS NULL
SQL

Для нескольких полей:

SELECT 
	`table`.*
FROM
	`table`
LEFT OUTER JOIN 
	(SELECT MIN(`id`) AS `id`, `col1`, `col2` FROM `table` GROUP BY `col1`, `col2`) AS `tmp` 
ON 
	`a`.`id` = `tmp`.`id`  
WHERE
	`tmp`.`id` IS NULL
SQL

Запрос сделает уникальные названия только у дублей, дописав `id` в конец `col1`.

UPDATE  
	`table`
LEFT OUTER JOIN 
	(SELECT MIN(`id`) AS `id`, `col1` FROM `table` GROUP BY `col1`) AS `tmp` 
ON 
	`table`.`id` = `tmp`.`id`
SET
	`table`.`col1` = CONCAT(`table`.`col1`, '-', `table`.`id`)    
WHERE
	`tmp`.`id` IS NULL
SQL

По нескольким полям:

UPDATE  
	`table`
LEFT OUTER JOIN 
	(SELECT MIN(`id`) AS `id`, `col1`, `col2` FROM `table` GROUP BY `col1`, `col2`) AS `tmp` 
ON 
	`table`.`id` = `tmp`.`id`
SET
	`table`.`col1` = CONCAT(`table`.`col1`, '-', `table`.`id`)    
WHERE
	`tmp`.`id` IS NULL
SQL

Удаление дублирующихся записей, останутся только уникальные.

DELETE
	`table`
FROM  
	`table`
LEFT OUTER JOIN 
	(SELECT MIN(`id`) AS `id`, `col1` FROM `table` GROUP BY `col1`) AS `tmp` 
ON 
	`table`.`id` = `tmp`.`id`  
WHERE
	`tmp`.`id` IS NULL
SQL

По нескольким полям:

DELETE
	`table`
FROM  
	`table`
LEFT OUTER JOIN 
	(SELECT MIN(`id`) AS `id`, `col1`, `col2` FROM `table` GROUP BY `col1`, `col2`) AS `tmp` 
ON 
	`table`.`id` = `tmp`.`id`  
WHERE
	`tmp`.`id` IS NULL
SQL
20.08.2019, обновлено 26.02.2021
109957
Следующая запись Шифрование полей в MySQL

Комментарии

, чтобы добавить комментарий.

Другие публикации

Сортировка в MySQL
В этой статье приведены примеры использования оператора ORDER BY для сортировки записей в MySQL.
27051
+4
Временные таблицы в MySQL
Временные таблицы используются для хранения промежуточных данных при сложных выборках из БД, например при большом количестве JOIN и UNION запросов.
35230
+2
PHP-класс обертка для PDO
Класс значительно упрощает работу с PDO, сокращает код. Реализован на статических классах и не требует создание экземпляра класса.
17640
+10
Поиск похожих статей на PHP + MySQL
Один из вариантов поиска похожих статей в базе данных основан на схождении слов в двух текстах.
3632
+3
Поиск ближайших объектов в БД по координатам
Рассмотрим пример как найти в базе данных соседние объекты по координатам и вывести их на карте Яндекс.
6952
+1
Запросы для заполнения таблицы данными из других таблиц
При переносе сайта на другую CMS возникают проблемы c миграцией дынных в БД, структура как правило разная,...
8237
+1