Resumo : neste tutorial, você aprenderá várias maneiras de excluir linhas duplicadas no MySQL.
No tutorial anterior, mostramos como encontrar valores duplicados em uma tabela . Depois que as linhas duplicadas forem identificadas, você pode excluí-las para limpar seus dados.
Preparar dados de amostra
O script a seguir cria uma tabela contacts
e insere dados de amostra na contacts
tabela para demonstração.
DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL
);
INSERT INTO contacts (first_name,last_name,email)
VALUES ('Carine ','Schmitt','[email protected]'),
('Jean','King','[email protected]'),
('Peter','Ferguson','[email protected]'),
('Janine ','Labrune','[email protected]'),
('Jonas ','Bergulfsen','[email protected]'),
('Janine ','Labrune','[email protected]'),
('Susan','Nelson','[email protected]'),
('Zbyszek ','Piestrzeniewicz','[email protected]'),
('Roland','Keitel','[email protected]'),
('Julie','Murphy','[email protected]'),
('Kwai','Lee','[email protected]'),
('Jean','King','[email protected]'),
('Susan','Nelson','[email protected]'),
('Roland','Keitel','[email protected]');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Observe que você pode executar este script para recriar dados de teste após executar uma DELETE
instrução.
Esta consulta retorna dados da contacts
tabela:
SELECT * FROM contacts
ORDER BY email;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A consulta a seguir retorna os emails duplicados na contacts
tabela:
SELECT
email, COUNT(email)
FROM
contacts
GROUP BY
email
HAVING
COUNT(email) > 1;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Como você pode ver, temos quatro linhas com emails duplicados.
1) Exclua linhas duplicadas usando a instrução DELETE JOIN
O MySQL fornece a DELETE JOIN
instrução que permite remover linhas duplicadas rapidamente.
A instrução a seguir exclui linhas duplicadas e mantém o ID mais alto:
DELETE t1 FROM contacts t1
INNER JOIN contacts t2
WHERE
t1.id < t2.id AND
t1.email = t2.email;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
Query OK, 4 rows affected (0.10 sec)
Linguagem de código: CSS ( css )
Como fazemos referência à contacts
tabela duas vezes, precisamos usar os aliases da tabela t1
e t2
.
A saída indica que quatro linhas foram excluídas. Você pode executar novamente a consulta que encontra e-mails duplicados para verificar a exclusão:
SELECT
email,
COUNT(email)
FROM
contacts
GROUP BY
email
HAVING
COUNT(email) > 1;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
Empty set (0.00 sec)
Linguagem de código: JavaScript ( javascript )
A consulta retorna um conjunto vazio, o que significa que as linhas duplicadas foram excluídas.
Vamos verificar os dados da contacts
tabela:
SELECT * FROM contacts;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
As linhas com id 2, 4, 7 e 9 foram excluídas.
Caso queira excluir linhas duplicadas e manter o ID mais baixo, você pode usar a seguinte instrução:
DELETE c1 FROM contacts c1
INNER JOIN contacts c2
WHERE
c1.id > c2.id AND
c1.email = c2.email;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Observe que você pode executar o script de criação da contacts
tabela novamente e testar esta consulta. A saída a seguir mostra os dados da contacts
tabela após a remoção de linhas duplicadas.
2) Exclua linhas duplicadas usando uma tabela intermediária
Veja a seguir as etapas para remover linhas duplicadas usando uma tabela intermediária:
- Crie uma nova tabela com a mesma estrutura da tabela original da qual deseja excluir as linhas duplicadas.
- Insira linhas distintas da tabela original na tabela imediata.
- Elimine a tabela original e renomeie a tabela imediata para a tabela original.
As consultas a seguir ilustram as etapas:
Passo 1. Crie uma nova tabela cuja estrutura seja igual à tabela original:
CREATE TABLE source_copy LIKE source;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Passo 2. Insira linhas distintas da tabela original na nova tabela:
INSERT INTO source_copy
SELECT * FROM source
GROUP BY col; -- column that has duplicate values
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Etapa 3. Elimine a tabela original e renomeie a tabela imediata para a original
DROP TABLE source;
ALTER TABLE source_copy RENAME TO source;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Por exemplo, as instruções a seguir excluem linhas com emails duplicados da contacts
tabela:
-- step 1
CREATE TABLE contacts_temp
LIKE contacts;
-- step 2
INSERT INTO contacts_temp
SELECT *
FROM contacts
GROUP BY email;
-- step 3
DROP TABLE contacts;
ALTER TABLE contacts_temp
RENAME TO contacts;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
3) Exclua linhas duplicadas usando a função ROW_NUMBER()
Observe que o MySQL suporta a ROW_NUMBER()
função a partir da versão 8.02, portanto você deve verificar sua versão do MySQL antes de usar a função.
A instrução a seguir usa a ROW_NUMBER()
função para atribuir um número inteiro sequencial a cada linha. Se o email for duplicado, o número da linha será maior que um.
SELECT
id,
email,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY
email
) AS row_num
FROM
contacts;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A instrução a seguir retorna a lista de IDs das linhas duplicadas:
SELECT
id
FROM
(
SELECT
id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY
email
) AS row_num
FROM
contacts
) t
WHERE
row_num > 1;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Você precisa excluir as linhas duplicadas da contacts
tabela usando a DELETE
instrução com uma subconsulta na cláusula WHERE
:
DELETE FROM
contacts
WHERE
id IN (
SELECT
id
FROM
(
SELECT
id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY
email
) AS row_num
FROM
contacts
) t
WHERE
row_num > 1
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
MySQL emitiu a seguinte mensagem:
4 row(s) affected
Neste tutorial, você aprendeu como excluir linhas duplicadas no MySQL.