Как найти и удалить дубликаты в таблице MySQL? - оказывается это довольно частый вопрос, возникающий на практике, особенно когда надо установить
UNIQUE constraint.
Задачу поиска дубликатов в SQL-таблице всегда можно решить таким простым запросом:
SELECT login, COUNT(login) AS cnt
FROM users GROUP BY login
HAVING ( COUNT(login)>1 );
Этот запрос вернёт все дубликаты
login из таблицы пользователей. Ситуация несколько искусственна, на практике врядли будут дубликаты логинов в таблице пользователей, но для примера запроса сгодится.
Другой вариант, возможно более прост для понимания, при SELECT-е сделать простой JOIN таблицы самой на себя. К примеру, есть таблица
scriptsettings с настройками какого-то скрипта, в ней нужно найти все дубликаты, где
param (имя параметра) и
group (логическое название группы параметров) дублируются. Запрос через JOIN примет вид:
SELECT s1.id AS id1, s1.group, s1.param, s2.id AS id2 FROM scriptsettings s1
INNER JOIN scriptsettings s2 ON s2.id>s1.id AND s1.param=s2.param AND s1.group=s2.group;
Удаление дубликатов из таблицы так же можно сделать простым JOIN таблицы самой на себя:
DELETE s1 FROM scriptsettings s1, scriptsettings s2 WHERE s1.param=s2.param AND s1.id>s2.id;
Пока писал, в голову пришёл более лаконичный и элегантный способ удаления дубликатов при помощи MySQL:
ALTER IGNORE TABLE scriptsettings ADD UNIQUE INDEX(param);