Удаление повторяющихся записей из большой MySQL-таблицы

Ограничения уникальности дают уверенность в том, что данные, содержащиеся в поле или группе полей, уникальны по отношению к другим записям в той же таблице, но только в том случае, если таблица спроектирована корректно. Недавно мне пришлось столкнуться с таблицей размером около миллиона записей, в которой каждая вторая запись являлась дублем в связи с тем, что в уникальном индексе не было учтено одно из новых полей. Рассмотрел несколько решений и кое-что отлично сработало.

1. Трюк с ALTER IGNORE (устаревшей)

До версии 5.7 в MySQL при добавлении к таблице UNIQUE INDEX с помощью ALTER IGNORE одновременно удалялись дубликаты. Но это решение использовать в новых версиях MySQL невозможно.

2. Оставление записей с минимальным ID (медленно)

StackOverflow подсказывает, что можно сделать так:

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

Для небольших таблиц такой способ приемлем. В противном случае будут с проблемы с производительностью, поскольку в запросе используется декартово произведение (как оно проявится на миллионе записей?).

3. Решение — временная таблица и INSERT IGNORE

Вместо того, чтобы пытаться удалить текущую таблицу, можно использовать запрос с INSERT IGNORE:

  • создайте временную таблицу со схемой, аналогичной существующей таблице;
  • добавьте ограничение UNIQUE к столбцам, которые вас интересуют;
  • запустите INSERT IGNORE для копирования данных из исходной таблицы во временную таблицу; любые повторяющиеся строки не будут вставлены во временную таблицу, поскольку они нарушают ограничение UNIQUE (а ошибку мы игнорируем);
  • переименуйте исходную таблицу во что-то другое и дайте временной таблице имя исходной таблицы;
  • удалите таблицу с дубликатами.

Пример

-- Создаём временную таблицу
CREATE TABLE temp_table LIKE table1;

-- Добавляем ограничение целостности
ALTER TABLE temp_table ADD UNIQUE(category, image_set_id);

-- Копируем данные
INSERT IGNORE INTO temp_table SELECT * FROM table1;

-- Переименовываем обе таблицы и удаляем исходную
RENAME TABLE table1 TO old_table1, temp_table TO table1;  
DROP TABLE old_table1;