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

Для того чтобы найти все неуникальные значения (либо комбинации значний, например из нескольких столбцов) понадобится группировка данных и оператор 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минут: