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

mysql-find-duplicates.sql

# Поиск дубликатов
# Подсчет дублей
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`
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`;

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

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

# Удаление дубликатов
# Удаление дублирующихся записей, останутся только уникальные.
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;