Содержание
HAVING
с агрегатной функцией COUNT
. Ничего сложного :)sqlSELECT `column` FROM `table` GROUP BY `column` HAVING COUNT(*) > 1;
Больше всего интересует то что после GROUP BY
, что дословно можно прочитать как: группировать значения по столбцу column где размер группы более 1 записи.
В итоге будут получены все неуникальные значения стоблца column.
Не долго думая можно попробовать удалить так:
sqlDELETE FROM `table` WHERE `column` IN( SELECT `column` FROM `table` GROUP BY `column` HAVING COUNT(*) > 1 )
И конечно же так не получится, потому что:
Но можно создать временную таблицу, занести в нее идентифицирующие значения (в данном случае `column`) и используя эту временную таблицу удалить данные из целевой таблицы:
sqlSTART 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):
sqlSTART 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минут: