MySQL выявление, подсчет и удаление записей с повторяющимися полями

Выявить и подсчитать записи с дублирующимися полями в субд MySQL можно двумя способами: используя группировку по полю и оператор HAVING либо используя SELECT подзапрос. Рассмотрим оба способа, но сразу скажу, GROUP BY + HAVING отработает на порядок эффективнее и именно её будем использовать при выявлении дублей, да и вообще лучше использовать именно этот способ. В обеих случаях проверяемому полю добавим простой индекс, если его ещё нет.

1.  Вариант нахождения дублирующихся записей с подзапросом SELECT выглядит так:

SELECT * FROM table_name AS t1 WHERE (SELECT COUNT(*) FROM table_name AS t2 WHERE t1.field_name = t2.field_name) > 1;

На таблицах миллионниках такой способ будет мало эффективен, поскольку в подзапросе перебирается каждая запись.

2.  Более эффективный способ выявить повторяющиеся поля в MySQL выглядит так:

SELECT *, COUNT(*) AS c FROM table_name GROUP BY field_name HAVING c > 1;

/* а чтобы посчитать чистые дубли так */
SELECT *, COUNT(*) - 1 AS c FROM table_name GROUP BY field_name HAVING c > 0;

Предположим у нас есть поле date_created, тогда мы можем сгруппировать данные по времени, выявив в какие периоды вносилось наибольшее число дублей, тут уже без подзапросов тоже не обойтись:

/* группировка по месяцам */
SELECT month AS 'месяц', SUM(c) AS 'дублей' FROM (SELECT CONCAT(YEAR(date_created), ' ', MONTHNAME(date_created)) AS month, COUNT(*) - 1 AS c FROM table_name GROUP BY field_name HAVING c > 0) AS t GROUP BY month;

/* группировка по дням */
SELECT d AS 'дата', SUM(c) AS 'дублей' FROM (SELECT DATE(date_created) AS d, COUNT(*) - 1 AS c FROM table_name GROUP BY field_name HAVING c > 0) AS t GROUP BY d;

Здесь мы уже в подзапросе отнимаем единичку, чтобы получить не просто кол-во повторяющихся записей, а именно записей — чистых дублей указанного поля field_name

Удалять повторяющиеся записи из бд будем при помощи ALTER IGNORE

ALTER IGNORE TABLE table_name ADD UNIQUE INDEX(field_name);

Одновременно будет добавлен уникальный индекс целевому полю.

Тоже самое можно сделать и по нескольким столбцам:

ALTER IGNORE TABLE table_name ADD UNIQUE INDEX(field_name1, field_name2);

Более гибкий вариант удалить повторяющиеся записи mysql — использование временной таблицы. Тут мы уже сможем поиграть с условиями как нам нужно:

CREATE TEMPORARY TABLE tmp_tab AS SELECT DISTINCT * FROM table_name;
DELETE FROM table_name;
INSERT INTO table_name SELECT * FROM tmp_tab;
DROP TABLE tmp_tab;

Однако в данном примере DISTINCT указывает на все поля, т.е. выбираются уникальные записи на основе всех полей(по факту группирует по ним). Но вполне вероятно, что все поля в сумме и так у нас уникальны, поскольку имеют например, уникальный id и/или время. В таком случае использование DISTINCT неуместно. Мы просто сгруппируем по нужному полю, и согласно логике работы GROUP BY занесутся лишь первые попавшиеся под группировку записи.

CREATE TEMPORARY TABLE tmp_tab AS SELECT * FROM table_name GROUP BY field_name;
DELETE FROM table_name;
INSERT INTO table_name SELECT * FROM tmp_tab;
DROP TABLE tmp_tab;