Como Encontrar e Remover Dados Duplicados no MySQL: Guia Completo com Exemplos SQL

1. Introdução

Ao operar um banco de dados, não é incomum encontrar problemas como “registros duplicados sendo inseridos” ou “dados que deveriam ser únicos aparecendo várias vezes”. Em ambientes onde bancos de dados relacionais como o MySQL são usados, extrair e gerenciar dados duplicados é uma tarefa essencial para manter a precisão e a qualidade dos dados.

Por exemplo, em tabelas centrais de negócios, como informações de membros, dados de produtos e histórico de pedidos, registros duplicados podem ser inseridos devido a erros de usuário ou falhas do sistema. Se não forem tratados, isso pode reduzir a precisão de agregações e análises, além de gerar bugs inesperados ou problemas operacionais.

Para resolver esse “problema de dados duplicados”, você deve primeiro identificar quais registros estão duplicados e, em seguida, organizar ou remover esses registros duplicados conforme a situação. Contudo, usar apenas uma instrução SELECT padrão no MySQL geralmente não é suficiente para detectar duplicatas de forma eficiente. Técnicas SQL um pouco mais avançadas e abordagens práticas são necessárias.

Neste artigo, focamos em “Como Extrair Dados Duplicados no MySQL”, cobrindo tudo, desde instruções SQL básicas até aplicações práticas, considerações de desempenho e tratamento comum de erros. Seja você um iniciante em bancos de dados ou um engenheiro que escreve SQL diariamente, este guia tem como objetivo fornecer conhecimento prático e orientado ao campo.

2. Conceitos Básicos: Detectando Duplicatas Usando uma Coluna‑Chave

A forma mais básica de extrair dados duplicados no MySQL é identificar casos em que “vários registros compartilham o mesmo valor em uma coluna específica (coluna‑chave)”. Nesta seção, explicamos consultas SQL representativas usadas para detectar valores de chave duplicados e como elas funcionam.

2-1. Detectando Duplicatas com GROUP BY e HAVING

A técnica fundamental para detecção de duplicatas é agrupar registros por uma coluna específica usando a cláusula GROUP BY e, em seguida, filtrar os grupos que contêm dois ou mais registros usando a cláusula HAVING. Aqui está um exemplo típico:

SELECT key_column, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY key_column
HAVING COUNT(*) > 1;

Exemplo: Extraindo Endereços de E‑mail de Membros Duplicados

SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Quando essa consulta é executada, se o mesmo endereço de e‑mail foi registrado várias vezes, o endereço de e‑mail e o número de duplicatas (count) serão exibidos nos resultados.

2-2. Detectando Duplicatas em Múltiplas Colunas

Se for necessário detectar duplicatas com base em uma combinação de duas ou mais colunas, você pode especificar várias colunas na cláusula GROUP BY usando a mesma lógica.

SELECT col1, col2, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY col1, col2
HAVING COUNT(*) > 1;

Com esse método, é possível detectar duplicatas onde múltiplas condições coincidem totalmente, como “mesmo nome completo e data de nascimento” ou “mesmo ID de produto e data do pedido”.

2-3. Calculando o Número Total de Registros Duplicados

Caso queira entender a escala geral da duplicação, pode usar uma subconsulta para calcular o total de entradas duplicadas.

SELECT SUM(duplicate_count) AS total_duplicates
FROM (
  SELECT COUNT(*) AS duplicate_count
  FROM table_name
  GROUP BY key_column
  HAVING COUNT(*) > 1
) AS duplicates;

Essa consulta soma o número de entradas duplicadas em todos os grupos de duplicatas.

Combinando GROUP BY e HAVING, você pode extrair dados duplicados no MySQL de maneira simples e eficiente.

3. Extraindo Todos os Registros que Compartilham Chaves Duplicadas

Na seção anterior, apresentamos como listar apenas os “valores de chave duplicados”. Contudo, no trabalho real, costuma‑se precisar confirmar “quais registros exatos estão duplicados e inspecionar todos os seus detalhes”. Por exemplo, pode ser necessário revisar os perfis de usuário totalmente duplicados ou analisar linha a linha os dados de produtos duplicados.

Nesta seção, explicamos padrões práticos de SQL para extrair todos os registros que compartilham chaves duplicadas.

3-1. Extraindo Registros Duplicados Usando uma Subconsulta

A abordagem mais básica é recuperar a lista de valores de chave duplicados em uma subconsulta e, em seguida, buscar todos os registros que correspondem a essas chaves.

SELECT *
FROM table_name
WHERE key_column IN (
  SELECT key_column
  FROM table_name
  GROUP BY key_column
  HAVING COUNT(*) > 1
);

Exemplo: Extraindo Todos os Registros com Endereços de Email Duplicados

SELECT *
FROM users
WHERE email IN (
  SELECT email
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
);

Ao executar esta consulta, ela extrai todas as linhas da tabela “users” onde o endereço de email está duplicado (incluindo colunas como ID, data de registro, etc.).

3-2. Extração Eficiente Usando EXISTS

Se você precisar lidar com grandes conjuntos de dados ou se preocupar com desempenho, usar EXISTS também pode ser eficaz. IN e EXISTS são semelhantes, mas dependendo do volume de dados e da indexação, um pode ser mais rápido que o outro.

SELECT *
FROM table_name t1
WHERE EXISTS (
  SELECT 1
  FROM table_name t2
  WHERE t1.key_column = t2.key_column
  GROUP BY t2.key_column
  HAVING COUNT(*) > 1
);

Exemplo: Registros de Email Duplicados (Usando EXISTS)

SELECT *
FROM users u1
WHERE EXISTS (
  SELECT 1
  FROM users u2
  WHERE u1.email = u2.email
  GROUP BY u2.email
  HAVING COUNT(*) > 1
);

3-3. Observações e Considerações de Desempenho

• O desempenho de subconsultas pode ser significativamente afetado quando o conjunto de dados é grande. Com indexação adequada, tanto IN quanto EXISTS podem ter desempenho em nível prático.
• Contudo, se você precisar de condições complexas ou quiser determinar duplicatas em várias colunas, as consultas podem se tornar pesadas. Sempre valide o comportamento em um ambiente de teste primeiro.

Dessa forma, extrair todos os registros que correspondem a chaves duplicadas pode ser feito usando subconsultas ou a cláusula EXISTS.

4. Detectando Duplicatas em Múltiplas Colunas

As condições de detecção de duplicatas nem sempre se baseiam em uma única coluna. Na prática, é comum exigir unicidade em uma combinação de várias colunas. Por exemplo, você pode considerar registros como duplicados quando “nome completo + data de nascimento” coincidem, ou quando “ID do produto + cor + tamanho” são todos idênticos.

Nesta seção, explicamos em detalhes como extrair duplicatas usando múltiplas colunas.

4-1. Detectando Duplicatas com GROUP BY Usando Múltiplas Colunas

Para detectar duplicatas em várias colunas, liste as colunas separadas por vírgulas na cláusula GROUP BY. Com HAVING COUNT(*) > 1, você pode extrair apenas as combinações que aparecem duas ou mais vezes.

SELECT col1, col2, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY col1, col2
HAVING COUNT(*) > 1;

Exemplo: Detectando Duplicatas por “first_name” e “birthday”

SELECT first_name, birthday, COUNT(*) AS count
FROM users
GROUP BY first_name, birthday
HAVING COUNT(*) > 1;

Esta consulta ajuda a identificar casos em que a combinação de “mesmo nome” e “mesma data de nascimento” foi registrada várias vezes.

4-2. Extraindo Todos os Registros para Chaves Duplicadas de Múltiplas Colunas

Se você precisar de todos os detalhes dos registros para combinações de chaves duplicadas, pode extrair os pares duplicados em uma subconsulta e, em seguida, buscar todas as linhas que correspondem a esses pares.

SELECT *
FROM table_name t1
WHERE (col1, col2) IN (
  SELECT col1, col2
  FROM table_name
  GROUP BY col1, col2
  HAVING COUNT(*) > 1
);

Exemplo: Registros Completos para Duplicatas em “first_name” e “birthday”

SELECT *
FROM users u1
WHERE (first_name, birthday) IN (
  SELECT first_name, birthday
  FROM users
  GROUP BY first_name, birthday
  HAVING COUNT(*) > 1
);

Usando esta consulta, por exemplo, se a combinação “Taro Tanaka / 1990-01-01” estiver registrada várias vezes, você pode recuperar todas as linhas detalhadas relacionadas.

4-3. Detectando Duplicatas Exatas (COUNT DISTINCT)

Se você quiser estimar “quantas linhas são duplicatas exatas em várias colunas”, também pode usar agregação com COUNT(DISTINCT ...).

SELECT COUNT(*) - COUNT(DISTINCT col1, col2) AS duplicate_count
FROM table_name;

Este SQL fornece uma contagem aproximada de linhas totalmente duplicadas dentro da tabela.

4-4. Notas

• Mesmo para detecção de duplicatas em múltiplas colunas, a indexação adequada pode melhorar significativamente a velocidade da consulta .
• Se houver muitas colunas envolvidas ou valores NULL presentes, você pode obter resultados de duplicatas inesperados. Projete suas condições cuidadosamente.

Dessa forma, detectar e extrair duplicatas em várias colunas pode ser tratado de forma flexível com SQL bem projetado.

5. Removendo Registros Duplicados (DELETE)

Depois de conseguir extrair dados duplicados, o próximo passo é excluir duplicatas desnecessárias. Na prática, uma abordagem comum é manter apenas um registro entre as duplicatas e excluir o resto. No entanto, ao excluir duplicatas automaticamente no MySQL, você deve delimitar cuidadosamente o alvo da exclusão para evitar perda de dados não intencional.

Nesta seção, explicamos métodos seguros comuns para excluir dados duplicados e precauções importantes.

5-1. Excluindo Duplicatas com uma Subconsulta + DELETE

Se você quiser manter apenas o registro “mais antigo” ou “mais recente” e excluir os demais, uma instrução DELETE com subconsulta pode ser útil.

Exemplo: Manter o registro com o menor (mais antigo) ID e excluir os demais

DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id)
  FROM users
  GROUP BY email
);

Esta consulta mantém apenas o menor id (o primeiro registro inserido) para cada email, e exclui todas as outras linhas que compartilham o mesmo email.

5-2. Como Evitar o Erro Específico do MySQL (Erro 1093)

No MySQL, você pode encontrar o Erro 1093 ao tentar DELETE de uma tabela enquanto também referencia a mesma tabela em uma subconsulta. Nesse caso, você pode evitar o erro envolvendo o resultado da subconsulta como uma tabela derivada (conjunto de resultados temporário).

DELETE FROM users
WHERE id NOT IN (
  SELECT * FROM (
    SELECT MIN(id)
    FROM users
    GROUP BY email
  ) AS temp_ids
);

Ao envolver a subconsulta com SELECT * FROM (...) AS alias, você pode prevenir o erro e excluir com segurança.

5-3. Excluindo Duplicatas para Chaves de Múltiplas Colunas

Se você quiser excluir duplicatas com base em uma combinação de várias colunas, use GROUP BY com múltiplas colunas e exclua tudo exceto o registro representativo.

Exemplo: Para duplicatas por “first_name” e “birthday”, excluir tudo exceto o primeiro registro

DELETE FROM users
WHERE id NOT IN (
  SELECT * FROM (
    SELECT MIN(id)
    FROM users
    GROUP BY first_name, birthday
  ) AS temp_ids
);

5-4. Medidas de Segurança e Melhores Práticas para Exclusão

Excluir duplicatas é uma operação de alto risco que pode remover dados permanentemente. Certifique‑se de seguir estas melhores práticas:

• Faça backups : Sempre salve um backup de toda a tabela ou dos registros alvo antes de excluir.
• Use transações : Se possível, envolva a operação em uma transação para que você possa reverter imediatamente se algo der errado.
• Confirme contagens com SELECT primeiro : Crie o hábito de verificar “O alvo da exclusão está correto?” executando primeiro uma consulta SELECT.
• Verifique os índices : Adicionar índices às colunas usadas para detecção de duplicatas melhora tanto o desempenho quanto a precisão.

No MySQL, você pode excluir dados duplicados com segurança usando subconsultas e tabelas derivadas. Sempre proceda com cautela, com testes suficientes e uma estratégia de backup sólida.

6. Considerações de Desempenho e Estratégia de Índices

Ao extrair ou deletar dados duplicados no MySQL, o tempo de execução da consulta e a carga do servidor tornam-se mais problemáticos à medida que a tabela cresce. Especialmente em sistemas de grande escala ou jobs em lote, o design SQL consciente de desempenho e a otimização de índices são essenciais. Nesta seção, explicamos dicas para melhorar o desempenho e pontos chave para o design de índices no processamento de dados duplicados.

6-1. Escolhendo Entre EXISTS, IN e JOIN

Construtos SQL como IN, EXISTS e JOIN são comumente usados para extrair dados duplicados, mas cada um tem características diferentes e tendências de desempenho.

• IN – Rápido quando o conjunto de resultados da subconsulta é pequeno, mas o desempenho tende a degradar à medida que o conjunto de resultados cresce.
• EXISTS – Para de pesquisar assim que um registro correspondente é encontrado, por isso é frequentemente eficaz para tabelas grandes ou quando as correspondências são relativamente raras.
• JOIN – Útil para recuperar muitas informações de uma vez, mas pode ficar mais lento se você juntar dados desnecessários ou faltar indexação adequada.

Exemplo de Comparação de Desempenho

É importante escolher a sintaxe ótima com base no seu sistema real e volume de dados.

6-2. Por Que o Design de Índices Importa

Para colunas usadas em verificações de duplicatas ou filtros de exclusão, sempre crie índices. Sem índices, varreduras completas da tabela podem ocorrer e o desempenho pode se tornar extremamente lento.

Exemplo: Criando um Índice

CREATE INDEX idx_email ON users(email);

Se você detectar duplicatas em várias colunas, um índice composto também é eficaz.

CREATE INDEX idx_name_birthday ON users(first_name, birthday);

O design de índices pode mudar dramaticamente o desempenho de leitura e a eficiência de pesquisa.
Nota: Adicionar muitos índices pode desacelerar as gravações e aumentar o uso de armazenamento, por isso o equilíbrio é importante.

6-3. Processamento em Lotes para Conjuntos de Dados Grandes

• Se o conjunto de dados estiver na ordem de dezenas de milhares a milhões de linhas, é mais seguro executar o processamento em lotes menores em vez de lidar com tudo de uma vez.
• Para exclusões e atualizações, limite o número de linhas processadas por execução (por exemplo, LIMIT 1000 ) e execute várias vezes para reduzir contendas de bloqueio e degradação de desempenho. DELETE FROM users WHERE id IN ( -- The first 1000 duplicate record IDs extracted by a subquery ) LIMIT 1000;

6-4. Usando Planos de Execução (EXPLAIN)

Use EXPLAIN para analisar como uma consulta é executada. Isso ajuda você a verificar se os índices estão sendo usados de forma eficaz e se uma varredura completa (ALL) está ocorrendo.

EXPLAIN SELECT * FROM users WHERE email IN (...);

Ao manter o desempenho e a estratégia de índices em mente, você pode lidar com o processamento de duplicatas de forma segura e eficiente, mesmo para conjuntos de dados grandes.

7. Casos de Uso Avançados: Lidando com Cenários Complexos

Em ambientes do mundo real, a detecção e exclusão de duplicatas são frequentemente mais complexas do que uma correspondência simples. Você pode precisar adicionar condições adicionais, executar operações de forma segura em etapas ou atender a requisitos operacionais mais rigorosos. Nesta seção, introduzimos técnicas práticas avançadas para lidar com dados duplicados de forma segura e flexível.

7-1. Exclusão Condicional de Duplicatas

Se você quiser deletar apenas duplicatas que atendam a condições específicas, use a cláusula WHERE de forma estratégica.

Exemplo: Deletar apenas registros duplicados com o mesmo email e status = 'withdrawn'

DELETE FROM users
WHERE id NOT IN (
  SELECT * FROM (
    SELECT MIN(id)
    FROM users
    WHERE status = 'withdrawn'
    GROUP BY email
  ) AS temp_ids
)
AND status = 'withdrawn';

Ao adicionar condições ao WHERE e GROUP BY, você pode controlar precisamente quais registros manter e quais remover.

7-2. Recomendado: Processamento em Lotes e Execução Dividida

Se o conjunto de dados for muito grande ou você quiser evitar contendas de bloqueio e degradação de desempenho, use o processamento em lotes.

• Não processe todos os alvos de exclusão de uma vez — use LIMIT para execução em lotes
• Use controle de transação e faça rollback em caso de erros inesperados
• Gerencie o risco com backups e registro DELETE FROM users WHERE id IN ( SELECT id FROM ( -- Extrair IDs de registros duplicados filtrados por condições ) AS temp_ids ) LIMIT 500;

Esta abordagem reduz significativamente a carga do sistema.

7-3. Lidando com Definições Complexas de Duplicatas

Em diferentes contextos de negócios, a definição de “duplicata” varia. Você pode combinar subconsultas, expressões CASE e funções de agregação para um tratamento flexível.

Exemplo: Considere duplicatas apenas quando product_id, order_date e price forem todos idênticos

SELECT product_id, order_date, price, COUNT(*)
FROM orders
GROUP BY product_id, order_date, price
HAVING COUNT(*) > 1;

Para requisitos mais avançados, como “manter apenas o registro mais recente entre duplicatas”, você pode usar subconsultas ou ROW_NUMBER() (disponível no MySQL 8.0 e posteriores).

7-4. Melhores Práticas para Transações e Backups

• Sempre envolva operações DELETE ou UPDATE em transações para que você possa restaurar os dados com ROLLBACK se ocorrerem problemas.
• Se estiver trabalhando com tabelas importantes ou grandes volumes de dados, sempre crie um backup antecipadamente .

Ao dominar essas técnicas avançadas, você pode lidar com o processamento de dados duplicados de forma segura e flexível em qualquer ambiente.

8. Resumo

Neste artigo, explicamos sistematicamente como extrair e excluir dados duplicados no MySQL, desde os fundamentos até aplicações avançadas. Vamos revisar os pontos principais.

8-1. Principais Conclusões

• Detectando Dados Duplicados Você pode detectar duplicatas não apenas em uma única coluna, mas também em várias colunas. A combinação de GROUP BY e HAVING COUNT(*) > 1 é o padrão fundamental para detecção de duplicatas.
• Extraindo Todos os Registros Duplicados Usando subconsultas e a cláusula EXISTS, você pode recuperar todos os registros correspondentes a valores de chave duplicados.
• Excluindo Registros Duplicados Ao usar MIN(id) ou MAX(id) para manter linhas representativas e combinar subconsultas com instruções DELETE, você pode remover duplicatas desnecessárias com segurança. Evitar o Erro 1093 do MySQL também é importante.
• Desempenho e Indexação Para grandes volumes de dados ou condições complexas, indexação adequada, processamento em lotes e verificação do plano de execução usando EXPLAIN são essenciais.
• Técnicas Práticas Exclusão condicional, execução em lotes, gerenciamento de transações e backups são práticas essenciais para evitar erros em ambientes de produção.

8-2. Referência Rápida por Caso de Uso

8-3. Prevenindo Problemas Futuramente Duplicados

Prevenir duplicatas no momento da inserção é igualmente importante.

• Considere usar restrições UNIQUE durante o design da tabela.
• Limpeza regular de dados e auditoria ajudam a detectar problemas operacionais cedo.

Extrair e excluir dados duplicados no MySQL requer conhecimento que vai do SQL básico a técnicas avançadas. Esperamos que este guia apoie a manutenção do seu banco de dados e as operações do sistema.
Se você tem casos específicos ou dúvidas adicionais, considere consultar as FAQs ou procurar um especialista em bancos de dados.

9. FAQ: Perguntas Frequentes Sobre Extrair e Excluir Dados Duplicados no MySQL

Q1. Por que usar GROUP BY + HAVING em vez de DISTINCT?

DISTINCT remove duplicatas no conjunto de resultados, mas não pode dizer quantas vezes um valor aparece. Ao combinar GROUP BY e HAVING COUNT(*) > 1, você pode determinar quais valores aparecem múltiplas vezes e quantas duplicatas existem.

Especifique várias colunas em GROUP BY e use HAVING COUNT(*) > 1 para detectar combinações em que todas as colunas especificadas correspondam. Exemplo: GROUP BY first_name, birthday

Q4. Recebo o Erro 1093 ao executar DELETE. O que devo fazer?

O MySQL gera o Erro 1093 quando você referencia a mesma tabela em uma subconsulta dentro de uma instrução DELETE. Envolva o resultado da subconsulta em uma tabela derivada usando SELECT * FROM (...) AS alias para evitar o erro.

Q5. Como posso excluir dados duplicados com segurança?

Sempre crie um backup antes da exclusão, verifique os alvos com uma instrução SELECT e use transações quando possível. A exclusão em lote também pode ser mais segura para grandes conjuntos de dados.

Q6. O que devo fazer se as consultas estiverem lentas com grandes volumes de dados?

Crie índices nas colunas usadas para detecção de duplicatas. Use processamento em lote com LIMIT e verifique os planos de execução usando EXPLAIN para evitar varreduras completas de tabela desnecessárias.

Q7. Como posso prevenir fundamentalmente inserções duplicadas?

Defina restrições UNIQUE ou chaves únicas durante o design da tabela para impedir que valores duplicados sejam inseridos. Além disso, execute verificações periódicas de duplicatas e limpeza de dados após a implantação.

Q8. Os mesmos métodos podem ser usados no MariaDB ou em outros SGBDs?

Construtos SQL básicos como GROUP BY, HAVING e subconsultas também são suportados no MariaDB e no PostgreSQL. Contudo, as restrições de subconsultas em DELETE e as características de desempenho podem variar entre os produtos, portanto, sempre teste antecipadamente.