Como deletar duplicatas em uma tabela MySQL?

Eu preciso DELETE linhas duplicadas para sid especificado em uma tabela MySQL .

Como posso fazer isso com uma consulta SQL?

 DELETE (DUPLICATED TITLES) FROM table WHERE SID = "1" 

Algo parecido com isso, mas não sei como fazer isso.

isso remove as duplicatas no lugar, sem criar uma nova tabela

 ALTER IGNORE TABLE `table_name` ADD UNIQUE (title, SID) 

note: só funciona bem se o índice se encheckbox na memory

Suponha que você tenha um employee tabela, com as seguintes colunas:

 employee (first_name, last_name, start_date) 

Para excluir as linhas com uma coluna duplicada first_name :

 delete from employee using employee, employee e1 where employee.id > e1.id and employee.first_name = e1.first_name 

Após remover duplicatas para todos os SID-s, não apenas um único.

Com mesa temporária

 CREATE TABLE table_temp AS SELECT * FROM table GROUP BY title, SID; DROP TABLE table; RENAME TABLE table_temp TO table; 

Como o temp_table é recém criado, não possui índices. Você precisará recriá-los depois de remover duplicatas. Você pode verificar quais índices você tem na tabela com a tabela SHOW INDEXES IN table

Sem mesa temporária:

 DELETE FROM `table` WHERE id IN ( SELECT all_duplicates.id FROM ( SELECT id FROM `table` WHERE (`title`, `SID`) IN ( SELECT `title`, `SID` FROM `table` GROUP BY `title`, `SID` having count(*) > 1 ) ) AS all_duplicates LEFT JOIN ( SELECT id FROM `table` GROUP BY `title`, `SID` having count(*) > 1 ) AS grouped_duplicates ON all_duplicates.id = grouped_duplicates.id WHERE grouped_duplicates.id IS NULL ) 

Excluindo linhas duplicadas no MySQL, passo a passo

Crie a tabela e insira algumas linhas:

 dev-db> create table penguins(foo int, bar varchar(15), baz datetime); Query OK, 0 rows affected (0.07 sec) dev-db> insert into penguins values(1, 'skipper', now()); dev-db> insert into penguins values(1, 'skipper', now()); dev-db> insert into penguins values(3, 'kowalski', now()); dev-db> insert into penguins values(3, 'kowalski', now()); dev-db> insert into penguins values(3, 'kowalski', now()); dev-db> insert into penguins values(4, 'rico', now()); Query OK, 6 rows affected (0.07 sec) dev-db> select * from penguins; +------+----------+---------------------+ | foo | bar | baz | +------+----------+---------------------+ | 1 | skipper | 2014-08-25 14:21:54 | | 1 | skipper | 2014-08-25 14:21:59 | | 3 | kowalski | 2014-08-25 14:22:09 | | 3 | kowalski | 2014-08-25 14:22:13 | | 3 | kowalski | 2014-08-25 14:22:15 | | 4 | rico | 2014-08-25 14:22:22 | +------+----------+---------------------+ 6 rows in set (0.00 sec) 

Em seguida, remova as duplicatas:

 dev-db> delete a -> from penguins a -> left join( -> select max(baz) maxtimestamp, foo, bar -> from penguins -> group by foo, bar) b -> on a.baz = maxtimestamp and -> a.foo = b.foo and -> a.bar = b.bar -> where b.maxtimestamp IS NULL; Query OK, 3 rows affected (0.01 sec) 

Resultado:

 dev-db> select * from penguins; +------+----------+---------------------+ | foo | bar | baz | +------+----------+---------------------+ | 1 | skipper | 2014-08-25 14:21:59 | | 3 | kowalski | 2014-08-25 14:22:15 | | 4 | rico | 2014-08-25 14:22:22 | +------+----------+---------------------+ 3 rows in set (0.00 sec) 

O que é essa declaração de exclusão fazendo

Pseudocódigo: agrupa as linhas pelas duas colunas das quais você deseja remover duplicatas. Escolha a linha de cada grupo para manter usando o agregado máximo. Uma junit à esquerda retorna todas as linhas da tabela à esquerda, com as linhas correspondentes na tabela à direita. Nesse caso, a tabela da esquerda contém todas as linhas da tabela e a direita contém apenas as linhas NULL (não a linha por grupo que você deseja manter). Excluindo essas linhas, você fica com apenas uma única por grupo.

Mais explicações técnicas, Como você deve ler a instrução sql delete:

Pingüins de mesa com alias ‘a’ são unidos em um subconjunto de pinguins de mesa chamado alias ‘b’. A tabela à direita ‘b’, que é um subconjunto, localiza o timestamp máximo agrupado por foo e bar. Isso corresponde à tabela à esquerda ‘a’. (foo, bar, baz) à esquerda tem todas as linhas na tabela. O subconjunto à direita ‘b’ tem um (maxtimestamp, foo, bar) que é combinado com o esquerdo apenas no que é o max.

Cada linha que não é esse máximo tem o valor maxtimestamp de NULL. Filtre as linhas NULL e você terá um conjunto de todas as linhas agrupadas por foo e bar que não é o último registro de data e hora baz. Exclua esses.

Faça um backup da tabela antes de executar isso.

Evite que esse problema volte a acontecer nesta tabela:

Se você conseguiu isso para funcionar, e colocar para fora o seu fogo “linhas duplicadas”. Ótimo. Seu trabalho ainda não acabou. Defina uma nova chave exclusiva composta em sua tabela (nessas duas colunas) para evitar que mais duplicatas sejam adicionadas. Como um bom sistema imunológico, as fileiras ruins nem deveriam ser permitidas na mesa no momento da inserção. Mais tarde, todos os programas que adicionam duplicados transmitirão seu protesto e, quando você os corrige, esse problema nunca mais aparece.

Isso sempre parece funcionar para mim:

 CREATE TABLE NoDupeTable LIKE DupeTable; INSERT NoDupeTable SELECT * FROM DupeTable group by CommonField1,CommonFieldN; 

Que mantém o ID mais baixo em cada um dos dupes e o resto dos registros não-dupe.

Eu também fiz o seguinte para que o problema do dupe não ocorra mais após a remoção:

 CREATE TABLE NoDupeTable LIKE DupeTable; Alter table NoDupeTable Add Unique `Unique` (CommonField1,CommonField2); INSERT IGNORE NoDupeTable SELECT * FROM DupeTable; 

Em outras palavras, eu crio uma duplicata da primeira tabela, adiciono um índice exclusivo nos campos dos quais eu não quero duplicatas e faço um Insert IGNORE que tem a vantagem de não falhar como um Insert normal na primeira vez tentou adicionar um registro duplicado com base nos dois campos e, em vez disso, ignora esses registros.

Movendo fwd, torna-se impossível criar registros duplicados com base nesses dois campos.

Aqui está uma resposta simples:

 delete a from target_table a left JOIN (select max(id_field) as id, field_being_repeated from target_table GROUP BY field_being_repeated) b on a.field_being_repeated = b.field_being_repeated and a.id_field = b.id_field where b.id_field is null; 

Depois de me deparar com essa questão, em um database enorme, não fiquei totalmente impressionado com o desempenho de nenhuma das outras respostas. Quero manter apenas a última linha duplicada e excluir o resto.

Em uma declaração de consulta única, sem uma tabela temporária, isso funcionou melhor para mim,

 DELETE e.* FROM employee e WHERE id IN (SELECT id FROM (SELECT MIN(id) as id FROM employee e2 GROUP BY first_name, last_name HAVING COUNT(*) > 1) x); 

A única ressalva é que eu tenho que executar a consulta várias vezes, mas mesmo com isso, eu achei que funcionou melhor para mim do que as outras opções.

Este procedimento irá remover todas as duplicatas (incl múltiplas) em uma tabela, mantendo a última duplicata. Esta é uma extensão de Recuperando o último registro em cada grupo

Espero que isso seja útil para alguém.

 DROP TABLE IF EXISTS UniqueIDs; CREATE Temporary table UniqueIDs (id Int(11)); INSERT INTO UniqueIDs (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields AND T1.ID < T2.ID) WHERE T2.ID IS NULL); DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs); 

Este trabalho para eu remover registros antigos:

 delete from table where id in (select min(e.id) from (select * from table) e group by column1, column2 having count(*) > 1 ); 

Você pode replace min (e.id) para max (e.id) para remover os registros mais novos.

Os seguintes trabalhos para todas as tabelas

 CREATE TABLE `noDup` LIKE `Dup` ; INSERT `noDup` SELECT DISTINCT * FROM `Dup` ; DROP TABLE `Dup` ; ALTER TABLE `noDup` RENAME `Dup` ; 
 delete p from product p inner join ( select max(id) as id, url from product group by url having count(*) > 1 ) unik on unik.url = p.url and unik.id != p.id; 

Outra maneira fácil … usando UPDATE IGNORE:

Você tem que usar um índice em uma ou mais colunas (tipo index). Crie uma nova coluna de referência temporária (não faz parte do índice). Nesta coluna, você marca os únicos em atualizando-os com a cláusula ignore. Passo a passo:

Adicione uma coluna de referência temporária para marcar os únicos:

 ALTER TABLE `yourtable` ADD `unique` VARCHAR(3) NOT NULL AFTER `lastcolname`; 

=> isso adicionará uma coluna à sua tabela.

Atualize a tabela, tente marcar tudo como único, mas ignore os possíveis erros devido à duplicação do problema de chave (os registros serão ignorados):

 UPDATE IGNORE `yourtable` SET `unique` = 'Yes' WHERE 1; 

=> você encontrará seus registros duplicados não serão marcados como únicos = ‘Sim’, em outras palavras, apenas um de cada conjunto de registros duplicados será marcado como único.

Exclua tudo o que não é exclusivo:

 DELETE * FROM `yourtable` WHERE `unique` <> 'Yes'; 

=> Isso removerá todos os registros duplicados.

Solte a coluna …

 ALTER TABLE `yourtable` DROP `unique`; 

A exclusão de duplicatas em tabelas do MySQL é um problema comum, que geralmente vem com necessidades específicas. Caso alguém esteja interessado, aqui ( Remover linhas duplicadas no MySQL ) eu explico como usar uma tabela temporária para excluir duplicatas do MySQL de uma maneira confiável e rápida, também válida para manipular fonts de big data (com exemplos para diferentes casos de uso).

Ali , no seu caso, você pode executar algo assim:

 -- create a new temporary table CREATE TABLE tmp_table1 LIKE table1; -- add a unique constraint ALTER TABLE tmp_table1 ADD UNIQUE(sid, title); -- scan over the table to insert entries INSERT IGNORE INTO tmp_table1 SELECT * FROM table1 ORDER BY sid; -- rename tables RENAME TABLE table1 TO backup_table1, tmp_table1 TO table1; 

Acho que a solução da Werner acima para ser a mais conveniente, porque funciona independentemente da presença de uma chave primária, não mexer com tabelas, usa sql simples à prova de futuro, é muito compreensível.

Como afirmei no meu comentário, essa solução não foi explicada corretamente. Então isso é meu, baseado nisso.

1) adicione uma nova coluna booleana

 alter table mytable add tokeep boolean; 

2) adicione uma restrição nas colunas duplicadas E na nova coluna

 alter table mytable add constraint preventdupe unique (mycol1, mycol2, tokeep); 

3) defina a coluna booleana como true. Isso só terá sucesso em uma das linhas duplicadas por causa da nova restrição

 update ignore mytable set tokeep = true; 

4) excluir linhas que não foram marcadas como tokeep

 delete from mytable where tokeep is null; 

5) solte a coluna adicionada

 alter table mytable drop tokeep; 

Eu sugiro que você mantenha a restrição que você adicionou, para que novas duplicatas sejam evitadas no futuro.

 delete from `table` where `table`.`SID` in ( select t.SID from table t join table t1 on t.title = t1.title where t.SID > t1.SID ) 

Resposta do amor @ eric, mas parece que não funciona se você tiver uma tabela realmente grande (estou recebendo The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay quando tento executá – lo). Então limitei a consulta de junit para considerar apenas as linhas duplicadas e acabei com:

 DELETE a FROM penguins a LEFT JOIN (SELECT COUNT(baz) AS num, MIN(baz) AS keepBaz, foo FROM penguins GROUP BY deviceId HAVING num > 1) b ON a.baz != b.keepBaz AND a.foo = b.foo WHERE b.foo IS NOT NULL 

A cláusula WHERE, neste caso, permite que o MySQL ignore qualquer linha que não tenha uma duplicata e também irá ignorar se esta for a primeira instância da duplicata, de forma que somente as duplicatas subseqüentes serão ignoradas. Mude MIN(baz) para MAX(baz) para manter a última instância em vez da primeira.

Isso funciona para tabelas grandes:

  CREATE Temporary table duplicates AS select max(id) as id, url from links group by url having count(*) > 1; DELETE l from links l inner join duplicates ld on ld.id = l.id WHERE ld.id IS NOT NULL; 

Para excluir a mudança mais antiga, max(id) para min(id)

Isso fará com que a coluna column_name uma chave primária e ignore todos os erros. Por isso, excluirá as linhas com um valor duplicado para column_name .

 ALTER IGNORE TABLE `table_name` ADD PRIMARY KEY (`column_name`); 

Eu acho que isso vai funcionar, basicamente, copiando a tabela e esvaziando-a, em seguida, colocando apenas os valores distintos de volta para ele, mas por favor, verifique novamente antes de fazê-lo em grandes quantidades de dados.

Cria uma cópia carbono da sua mesa

crie a tabela temp_table como oldtablename; insert temp_table select * from oldtablename;

Esvazia sua mesa original

DELETE * do oldtablename;

Copia todos os valores distintos da tabela copiada de volta para sua tabela original

INSERT oldtablename SELECT * do grupo temp_table por nome, sobrenome, dob

Exclui sua tabela temporária.

Tabela de queda temp_table

Você precisa agrupar por todos os campos que deseja manter distintos.

Você poderia simplesmente usar uma cláusula DISTINCT para selecionar a lista “limpa” (e aqui está um exemplo muito fácil de como fazer isso).

Poderia funcionar se você contá-los e, em seguida, adicionar um limite à sua consulta de exclusão, deixando apenas um?

Por exemplo, se você tiver dois ou mais, escreva sua consulta assim:

 DELETE FROM table WHERE SID = 1 LIMIT 1; 

Existem apenas alguns passos básicos ao remover dados duplicados da sua tabela:

  • Faça o backup da sua mesa!
  • Encontre as linhas duplicadas
  • Remova as linhas duplicadas

Aqui está o tutorial completo: https://blog.teamsql.io/deleting-duplicate-data-3541485b3473