Удаление дубликатов из таблицы 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.