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

• Удаление всех неуникальных значений
• Удалить дубликаты оставив оригиналы
• Скорость исполнения

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

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

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

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

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

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

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

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

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

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

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

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):

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минут:

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

Результаты удаления неуникальных значений: