Como excluir linhas duplicadas no MySQL

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 contactstabela 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 DELETEinstrução.

Esta consulta retorna dados da contactstabela:

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 contactstabela:

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 JOINinstruçã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 à contactstabela 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 contactstabela:

SELECT * FROM contacts;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

MySQL exclui linhas duplicadas - DELETE JOIN mantém o ID mais alto

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 contactstabela novamente e testar esta consulta. A saída a seguir mostra os dados da contactstabela após a remoção de linhas duplicadas.

MySQL exclui linhas duplicadas - DELETE JOIN mantém o ID mais baixo

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 valuesLinguagem 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 contactstabela:

-- 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 )

MySQL Excluir linhas duplicadas - função ROW_NUMBER com subconsulta

Você precisa excluir as linhas duplicadas da contactstabela usando a DELETEinstruçã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 )