Урок 12.5 · Время чтения: ~7 мин

Дубликаты встречаются почти в любой реальной базе: импорт запустили дважды, форму отправили два раза, уникальное ограничение так и не добавили. «Найти дубликаты» и «удалить дубликаты, оставив одну строку» — классические задачи на собеседовании по SQL. В этом уроке вы найдёте повторяющиеся значения через GROUP BY ... HAVING, покажете каждую копию оконной функцией, безопасно удалите лишние копии в MySQL и не допустите новых дублей с помощью ограничения.

Поиск и удаление дубликатов в SQL

Сначала решите, что считать дубликатом. Строки редко совпадают во всех столбцах: обычно у них разные id и время создания. Дубликат — это одинаковый бизнес-ключ: тот же email, те же имя и дата рождения, тот же номер заказа. Все запросы ниже группируют именно по такому ключу.

Примеры используют синтаксис MySQL 8.0 и создают собственную таблицу, поэтому запускайте их в песочнице на базе без учебных данных.

Учебные данные

CREATE TABLE contact (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    full_name VARCHAR(50) NOT NULL
);

INSERT INTO contact VALUES
    (1, 'anna@mail.com', 'Anna Lee'),
    (2, 'bob@mail.com', 'Bob Stone'),
    (3, 'anna@mail.com', 'Anna Lee'),
    (4, 'carl@mail.com', 'Carl Diaz'),
    (5, 'bob@mail.com', 'Bob Stone'),
    (6, 'Anna@Mail.com', 'Anna Lee'),
    (7, 'dina@mail.com', 'Dina Park');

Шаг 1. Найти повторяющиеся значения

Сгруппируйте по ключу и оставьте группы, в которых больше одной строки:

SELECT email, COUNT(*) AS copies
FROM contact
GROUP BY email
HAVING COUNT(*) > 1;

Результат: anna@mail.com — 3 копии, bob@mail.com — 2.

Обратите внимание: Anna@Mail.com попал в одну группу с anna@mail.com. Сопоставление (collation) MySQL по умолчанию сравнивает строки без учёта регистра, поэтому это одно и то же значение. PostgreSQL учитывает регистр и нашёл бы только 2 копии Anna; там для такого же ответа группируйте по LOWER(email). Если ключ состоит из нескольких столбцов, перечислите их все: GROUP BY first_name, last_name.

Шаг 2. Посмотреть каждую строку-дубликат

GROUP BY показывает одну строку на значение. Чтобы увидеть сами дубли с их id, посчитайте копии оконной функцией и оставьте строки, где их больше одной:

WITH counted AS (
    SELECT id, email, full_name,
           COUNT(*) OVER (PARTITION BY email) AS copies
    FROM contact
)
SELECT id, email, full_name, copies
FROM counted
WHERE copies > 1
ORDER BY email, id;

Результат: 5 строк: id 1, 3, 6 для Anna и 2, 5 для Bob.

Прежде чем что-то удалять, посмотрите на эти строки: действительно ли копии означают одно и то же и какую из них нужно оставить?

Шаг 3. Пометить копии для удаления

Пронумеруйте строки внутри каждой группы с помощью ROW_NUMBER(). Строка с номером 1 — та, что останется; здесь это самая старая, с наименьшим id:

SELECT id, email,
       ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM contact
ORDER BY email, id;

Результат: id 1, 2, 4, 7 получают rn = 1; id 3, 5, 6 получают 2 или 3 — это лишние копии.

ORDER BY внутри окна определяет, какая строка останется: ORDER BY id DESC сохранит самую новую, ORDER BY updated_at DESC — последнюю обновлённую.

Шаг 4. Удалить лишние копии

В MySQL подзапрос в DELETE не может читать таблицу, из которой идёт удаление (ошибка 1093). Оберните его в производную таблицу, которую MySQL сначала материализует:

DELETE FROM contact
WHERE id IN (
    SELECT id
    FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
        FROM contact
    ) AS numbered
    WHERE rn > 1
);

Результат: удалено 3 строки; остались id 1, 2, 4 и 7 — по одной на каждый email.

Более старый вариант, специфичный для MySQL, использует самосоединение: удалить каждую строку, для которой существует строка с тем же email и меньшим id:

DELETE c
FROM contact c
JOIN contact k ON k.email = c.email AND k.id < c.id;

Результат: удалены те же 3 строки.

В PostgreSQL обычно пишут DELETE FROM contact c USING contact k WHERE k.email = c.email AND k.id < c.id;, а в SQL Server можно удалять прямо из CTE с ROW_NUMBER().

Перед DELETE выполните SELECT из шага 3, сравните число затронутых строк, а в рабочей базе выполняйте удаление в транзакции.

Шаг 5. Не допустить новых дубликатов

Чистка помогает только до следующего импорта. Ограничение уникальности заставляет базу данных отклонять дубликаты:

ALTER TABLE contact ADD CONSTRAINT uq_contact_email UNIQUE (email);

Добавляйте его после чистки: пока дубликаты есть, создать ограничение не получится. Импорт затем может использовать INSERT IGNORE или INSERT ... ON DUPLICATE KEY UPDATE (урок 8.1), чтобы пропускать или обновлять существующие строки.

Практика

• Повторяющиеся имена актёров
• Однофамильцы среди актёров
• Повторяющиеся email

Ключевые выводы:

• Дубликат — это повторяющийся бизнес-ключ, а не обязательно полностью одинаковая строка; группируйте по этому ключу.
• GROUP BY key HAVING COUNT(*) > 1 находит повторяющиеся значения; COUNT(*) OVER (PARTITION BY key) показывает каждую строку-дубль.
• ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) помечает строку, которую нужно оставить (rn = 1), и копии для удаления.
• В MySQL удаляйте через производную таблицу или самосоединение, чтобы избежать ошибки 1093.
• Регистр и сопоставление определяют, что считается равным; в конце добавьте ограничение UNIQUE.

Часто задаваемые вопросы

Как найти дубликаты в SQL?

Сгруппируйте по столбцам, которые должны быть уникальными, и отфильтруйте группы: SELECT email, COUNT(*) FROM contact GROUP BY email HAVING COUNT(*) > 1;. Чтобы увидеть строки целиком, используйте COUNT(*) OVER (PARTITION BY email) и оставьте строки, где значение больше 1.

Как удалить дубликаты, оставив одну строку?

Пронумеруйте строки каждой группы с помощью ROW_NUMBER() OVER (PARTITION BY key ORDER BY id) и удалите строки с номером больше 1. В MySQL поместите нумерацию в производную таблицу внутри DELETE или используйте самосоединение: DELETE c FROM t c JOIN t k ON k.key = c.key AND k.id < c.id.

Почему MySQL выдаёт ошибку 1093 при удалении дубликатов?

MySQL не позволяет подзапросу в DELETE или UPDATE напрямую читать ту же таблицу. Если обернуть подзапрос в производную таблицу (SELECT id FROM (...) AS d), MySQL сначала её материализует, и ошибка исчезает.

Как найти дубликаты по нескольким столбцам?

Перечислите их все в GROUP BY и в PARTITION BY: GROUP BY first_name, last_name HAVING COUNT(*) > 1. Строки считаются дубликатами, только если совпадают все перечисленные столбцы.