Удаление дубликатов из таблицы MySQL: с использованием DELETE

Быстрый ответ

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

DELETE dup FROM your_table dup
JOIN (
    SELECT MIN(id) as keepId FROM your_table GROUP BY unique_column
) AS original ON dup.id > original.keepId
WHERE dup.unique_column = original.unique_column;

Замените your_table на название вашей таблицы, unique_column — на поле, порождающее дубликаты, а id — на уникальный идентификатор. Данный запрос отбирает запись с минимальным id в каждой группе дублирующихся строк и удаляет все прочие, оставляя только одну уникальную запись для каждой группы дубликатов.

Какой подход выбрать для больших таблиц?

Если вы работаете с большими таблицами, то скорость является критическим фактором. Быстрым и безопасным методом является использование комбинации INSERT INTO для создания новой таблицы с уникальными записями, отобранными с помощью SELECT DISTINCT:

CREATE TABLE your_table_unique AS
SELECT DISTINCT * FROM your_table;

Затем произведите замену исходной таблицы новой, содержащей лишь уникальные записи:

RENAME TABLE your_table TO your_table_old, your_table_unique TO your_table;

Не забывайте проводить проверки на копиях данных, чтобы предотвратить потерю целостности данных.

Версия имеет значение: Вариации эффективности в MySQL

Версия MySQL влияет на эффективность удаления дубликатов. Проводите тесты в рабочем окружении, чтобы не столкнуться с неожиданным уменьшением производительности в рабочей системе.

Обработка ошибки MySQL 1093

При попытке удаления дубликатов может возникнуть ошибка 1093 в MySQL. В качестве решения следует применить дополнительный подзапрос с SELECT:

DELETE FROM your_table WHERE id NOT IN (
    SELECT * FROM (
        SELECT MIN(id) FROM your_table GROUP BY unique_column
    ) AS subquery
);

Не просто удаляйте! Всегда сначала проводите тесты

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

Визуализация

Простая иллюстрация процесса удаления дубликатов:

Карта сокровищ (🗺️): [🏴‍☠️🔍, 💎, 💎, 💎]

Оставляем один уникальный камень (💎):

DELETE t1 FROM treasures t1
INNER JOIN treasures t2 
WHERE 
    t1.gem = t2.gem AND 
    t1.id > t2.id;  -- "Чем больше добычи, тем больше проблем."

В результате сундук с сокровищами становится таким:

Сундук с сокровищами (🔒💰): [💎]

Теперь все камни уникальны, дубликаты исчезли.

До: [💎, 💎, 💎, 💎]
После:  [💎]

Достигнута абсолютная уникальность коллекции сокровищ.

Другие методы дедубликации

Забудьте про самосоединение, используйте GROUP BY и HAVING

Если вы не являетесь сторонником самосоединений, можно применить группировку и фильтрацию:

DELETE your_table FROM your_table
LEFT JOIN (
    SELECT MAX(id) as lastId FROM your_table GROUP BY unique_column
) AS filtered ON your_table.id = filtered.lastId
WHERE filtered.lastId IS NULL;

Эта команда удаляет все, кроме записей с максимальным id.

Подробнее об этом расскажет наш спикер на видео

Использование временных таблиц на промежуточной стадии

Применение временных таблиц может быть рискованным, но оно эффективно:

CREATE TEMPORARY TABLE temp_table AS
SELECT MIN(id) as minId, unique_column FROM your_table GROUP BY unique_column;

DELETE your_table FROM your_table
JOIN temp_table ON your_table.id > temp_table.minId
AND your_table.unique_column = temp_table.unique_column;

Пользовательские переменные – нестандартный подход

Используйте новаторские методы с пользовательскими переменными для удаления последовательных дубликатов:

DELETE your_table FROM (
    SELECT id, @rownum := IF(@prev = unique_column, @rownum + 1, 1) as rownum, @prev := unique_column
    FROM your_table, (SELECT @rownum := 0, @prev := NULL) r
    ORDER BY unique_column, id
) dup
WHERE dup.rownum > 1;

Полезные материалы

1. MySQL :: MySQL 8.0 Reference Manual :: 13.2.2 DELETE Syntax — подробное изучение инструкции DELETE в MySQL.
2. How to delete duplicate rows in SQL Server? – Stack Overflow — советы сообщества для пользователей SQL Server.
3. Removing Duplicates from a Table in SQL Server – Simple Talk — практические рекомендации по устранению дублирования записей в SQL Server.