Поиск и удаление неуникальных строк в таблице, SQL only

01.05.2020
Для того чтобы найти все неуникальные значения (либо комбинации значний, например из нескольких столбцов) понадобится группировка данных и оператор HAVING с агрегатной функцией COUNT. Ничего сложного :)

Если запросы к таблице будут по соответствующим индексам, то это будет намного быстрее

•••
sql
SELECT `column` FROM `table` GROUP BY `column` HAVING COUNT(*) > 1;

Больше всего интересует то что после GROUP BY, что дословно можно прочитать как: группировать значения по столбцу column где размер группы более 1 записи.

В итоге будут получены все неуникальные значения стоблца column.

Удаление всех неуникальных значений

Не долго думая можно попробовать удалить так:

•••
sql
DELETE FROM `table` WHERE `column` IN( SELECT `column` FROM `table` GROUP BY `column` HAVING COUNT(*) > 1 )

И конечно же так не получится, потому что:

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

Но можно создать временную таблицу, занести в нее идентифицирующие значения (в данном случае `column`) и используя эту временную таблицу удалить данные из целевой таблицы:

•••
sql
START TRANSACTION; # создание временной таблицы с одним столбцом CREATE TEMPORARY TABLE temp ( `column` INT ); # вставка значений во временную таблицу из результатов выборки INSERT INTO `temp` SELECT `column` FROM `table` GROUP BY `column` HAVING COUNT(*) > 1; # удаление строк из целевой таблицы на основании выборки из временной таблицы DELETE FROM `table` WHERE `column` IN (SELECT `column` FROM `temp`); # удаление временной таблицы DROP TEMPORARY TABLE `temp`; COMMIT;

Удалить дубликаты оставив оригиналы

Теперь попробуем удалить все неуникальные комбинации значений из нескольких столбцов, но оставить оригиналы (те что имеют меньший id):

•••
sql
START TRANSACTION; CREATE TEMPORARY TABLE temp ( `id` INT, `column1` INT, `column2` INT, `c` INT ); INSERT INTO temp SELECT MIN(`id`) as `id`, `column1`, `column2`, COUNT(*) FROM `table` GROUP BY `column1`, `column2` HAVING COUNT(*) > 1; DELETE `table` FROM `table`, `temp` WHERE `table`.`column1` = `temp`.`column1` AND `table`.`column2` = `temp`.`column2` AND `table`.`id` != `temp`.`id`; DROP TEMPORARY TABLE `temp`; COMMIT;

Теперь в таблице останутся строки только с меньшим id. Для того чтобы оставить самые последние дубликаты, а все остальные дубликаты удалить вместо MIN(id) надо использовать MAX(id).

Скорость исполнения

На практике была задача применить этот код к таблице в которой около 70 000 000 записей естественно был индекс на нужных столбцах), неуникальных строк было >5 000 000. Код из второй главы выполнялся ~9минут:

Результаты запроса выборки и вставки данных во временную таблицу
Результаты запроса выборки и вставки данных во временную таблицу
Результаты удаления неуникальных значений
Результаты удаления неуникальных значений