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

mysql search dublicates.md

@source https://snipp.ru/mysql/sql-duplicate-search

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

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

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

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

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

Результат:

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

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

Результат:

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

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

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

Результат:

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

Альтернативный вариант, работает лучше чем вариант №2

SELECT * FROM `table` WHERE id NOT IN
(SELECT MIN(id) id FROM `table` GROUP BY `col1`, `col2`, `col3` having count(*)=1)

№2

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`

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

№2 Запрос получит только дубликаты, в результат не попадают записи с самым ранним 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

Результат:

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

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

2.Уникализация записей

Запрос сделает уникальные названия только у дублей, дописав 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

Результат:

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

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

3.Удаление дубликатов

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

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

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

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