Este artigo mostra como encontrar linhas duplicadas em uma tabela de banco de dados. Esta é uma pergunta muito comum para iniciantes. A técnica básica é direta. Também mostrarei algumas variações, como como encontrar “duplicatas em duas colunas” (uma pergunta recente no canal IRC #mysql).
Como encontrar linhas duplicadas
A primeira etapa é definir o que exatamente torna uma linha uma duplicata de outra linha. Na maioria das vezes, isso é fácil: eles têm o mesmo valor em alguma coluna. Vou tomar isso como uma definição de trabalho para este artigo, mas você pode precisa alterar as consultas abaixo se sua noção de “duplicado” for mais complicada.
Para este artigo, usarei estes dados de exemplo:
As duas primeiras linhas têm o mesmo valor na coluna day, portanto, se eu considerá-los duplicados, aqui está uma consulta para localizá-los. A consulta usa uma cláusula GROUP BY para colocar todas as linhas com o mesmo day valor em um “grupo” e, em seguida, contar o tamanho do group:
As linhas duplicadas têm uma contagem maior que um. Se você deseja ver apenas as linhas duplicadas, você precisa usar um HAVING cláusula (não uma WHERE cláusula), como esta:
Esta é a técnica básica: agrupar pela coluna que contém duplicatas e mostrar apenas os grupos com mais de uma linha.
Por que você não pode usar uma cláusula WHERE?
A A cláusula WHERE filtra as linhas antes de serem agrupadas. Uma cláusula HAVING as filtra após o agrupamento. É por isso que você não pode usar um WHERE cláusula na consulta acima.
Como excluir linhas duplicadas
Uma questão relacionada é como excluir as linhas ‘duplicadas’ uma vez que você encontrá-los. Um t comum perguntar quando a limpeza de dados inválidos é excluir todas as duplicatas, exceto uma, para que você possa colocar os índices adequados e as chaves primárias na tabela e evitar que as duplicatas entrem na tabela novamente.
Novamente, o primeiro coisa a fazer é ter certeza de que sua definição está clara. Exatamente qual linha você deseja manter? O primeiro? Aquele com o maior valor de alguma coluna? Para este artigo, assumirei que você deseja manter a ‘primeira’ linha – aquela com o menor valor da coluna id. Isso significa que você deseja excluir todas as outras linhas.
Provavelmente, a maneira mais fácil de fazer isso é com uma tabela temporária. Especialmente no MySQL, existem algumas restrições sobre a seleção de uma tabela e atualizá-la na mesma consulta. Você pode contornar isso, conforme explico em meu artigo Como selecionar a partir de um destino de atualização no MySQL, mas vou apenas evitar essas complicações e usar uma tabela temporária.
A definição exata da tarefa é para excluir todas as linhas que possuem uma duplicata, exceto a linha com o valor mínimo de id para esse grupo. Portanto, você precisa encontrar não apenas as linhas onde há mais de um no grupo, você também precisa encontrar a linha que deseja manter. Você pode fazer isso com a função MIN(). Aqui estão algumas consultas para criar a tabela temporária e encontrar os dados de que você precisa para fazer o DELETE:
Agora que você tem esses dados, pode prosseguir com a exclusão as linhas ‘ruins’. Há muitas maneiras de fazer isso, e algumas são melhores do que outras (veja meu artigo sobre problemas muitos para um em SQL), mas, novamente, evitarei os pontos mais delicados e apenas mostrarei uma sintaxe padrão que deve funcionar em qualquer RDBMS que suporte subconsultas:
Se seu RDBMS não oferece suporte a subconsultas, ou se for mais eficiente, você pode desejar excluir várias tabelas. A sintaxe para isso varia entre os sistemas, então você precisa consultar a documentação do seu sistema. Você também pode precisar fazer tudo isso em uma transação para evitar que outros usuários alterem os dados enquanto você está trabalhando, se isso for uma preocupação.
Como encontrar duplicatas em várias colunas
Recentemente, alguém fez uma pergunta semelhante a esta no canal IRC #mysql:
Tenho uma tabela com colunas
becque vincula duas outras tabelasbec, e eu quero para localizar todas as linhas que possuem duplicatas embouc.
Era difícil entender exatamente o que isso significava, mas depois de alguma conversa eu entendi: a pessoa queria ser capaz de colocar índices exclusivos nas colunas b e c separadamente.
É muito fácil encontrar linhas com valores duplicados em uma ou outra coluna, como mostrei acima: basta agrupar por essa coluna mn e conte o tamanho do grupo. E é fácil encontrar linhas inteiras que são duplicatas exatas de outras linhas: basta agrupar por quantas colunas você precisar.Mas é mais difícil identificar linhas que tenham um valor b duplicado ou um valor c duplicado. Pegue a tabela de exemplo a seguir, que é aproximadamente o que a pessoa descreveu:
Agora, você pode ver facilmente que há algumas linhas ‘duplicadas’ nesta tabela, mas não há duas linhas realmente com a mesma tupla {b, c}. É por isso que isso é um pouco mais difícil de resolver.
Consultas que não funcionam
Se você agrupar por duas colunas, obterá vários resultados, dependendo de como agrupar e contar. É aqui que o usuário de IRC fica confuso. Às vezes, as consultas localizavam algumas duplicatas, mas não outras. Aqui estão algumas das coisas que esta pessoa tentou:
Esta consulta retorna todas as linhas da tabela, com um COUNT(*) de 1, o que parece ser um comportamento errado, mas na verdade não é. Porque? Porque o > 1 está dentro do COUNT(). É muito fácil passar despercebido, mas essa consulta é na verdade igual a
Por quê? Porque (b > 1) é uma expressão booleana. Não é isso que você quer. Você deseja
Isso retorna zero linhas, é claro, porque não há tuplas {b, c} duplicadas. A pessoa tentou muitas outras combinações de HAVING cláusulas e ORs e ANDs, agrupando por uma coluna e contando a outra, e assim por diante:
Porém, nada encontrou todas as duplicatas. O que eu acho que o deixou mais frustrante é que funcionou parcialmente, fazendo a pessoa pensar que era quase a consulta certa … talvez apenas outra variação conseguisse …
Na verdade, é impossível fazer com este tipo de consulta GROUP BY simples. Por que é isso? É porque quando você agrupa por uma coluna, você distribui valores semelhantes da outra coluna em vários grupos. Você pode ver isso visualmente ordenando por essas colunas, que é o que o agrupamento faz. Primeiro, ordene por coluna b e veja como eles são agrupados:
| a | b | c |
|---|---|---|
| 7 | 1 | 1 |
| 8 | 1 | 2 |
| 9 | 1 | 3 |
| 10 | 2 | 1 |
| 11 | 2 | 2 |
| 12 | 2 | 3 |
| 13 | 3 | 1 |
| 14 | 3 | 2 |
| 15 | 3 | 3 |
Quando você pede (agrupa) por coluna b, os valores duplicados na coluna c são distribuídos em grupos diferentes, então você não pode contá-los com COUNT(DISTINCT c) como a pessoa estava tentando fazer. Funções agregadas como COUNT() operam apenas dentro de um grupo e não têm acesso a linhas que são colocadas em outros grupos. Da mesma forma, ao fazer o pedido por c, os valores duplicados na coluna b são distribuídos em grupos diferentes. Não é possível fazer esta consulta fazer o que é desejado.
Algumas soluções corretas
Provavelmente a solução mais simples é encontrar as duplicatas para cada coluna separadamente e UNION eles juntos, desta forma:
A coluna what_col na saída indica em qual coluna o valor duplicado foi encontrado. Outra abordagem é usar subconsultas:
Isso é provavelmente muito menos eficiente do que a abordagem UNION e mostrará todas as linhas duplicadas, não apenas os valores que estão duplicados. Outra abordagem é fazer autojunções em subconsultas agrupadas na cláusula FROM. É mais complicado de escrever corretamente, mas pode ser necessário para alguns dados complexos ou para eficiência:
Qualquer uma dessas consultas servirá, e tenho certeza de que existem outras maneiras também. Se você pode usar UNION, provavelmente é o mais fácil.
