MySQLi - Tratamento de duplicados

As tabelas ou conjuntos de resultados às vezes contêm registros duplicados. Às vezes, é permitido, mas às vezes é necessário para impedir registros duplicados. Às vezes, é necessário identificar registros duplicados e removê-los da tabela. Este capítulo descreverá como evitar a ocorrência de registros duplicados em uma tabela e como remover registros duplicados já existentes.

Evitando que ocorram duplicatas em uma tabela

Você pode usar um PRIMARY KEY ou UNIQUEÍndice em uma tabela com campos apropriados para impedir registros duplicados. Vejamos um exemplo: a tabela a seguir não contém esse índice ou chave primária, portanto, permitiria registros duplicados para first_name e last_name.

CREATE TABLE person_tbl (
   first_name CHAR(20),
   last_name CHAR(20),
   sex CHAR(10)
);

Para evitar que vários registros com os mesmos valores de nome e sobrenome sejam criados nesta tabela, adicione uma PRIMARY KEY à sua definição. Ao fazer isso, também é necessário declarar as colunas indexadas como NOT NULL, porque uma PRIMARY KEY não permite valores NULL -

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

A presença de um índice exclusivo em uma tabela normalmente causa a ocorrência de um erro se você inserir um registro na tabela que duplica um registro existente na coluna ou colunas que definem o índice.

Usar INSERT IGNORE ao invés de INSERT. Se um registro não duplica um registro existente, o MySQLi o insere normalmente. Se o registro for uma duplicata, a palavra-chave IGNORE diz ao MySQLi para descartá-lo silenciosamente sem gerar um erro.

O exemplo a seguir não apresenta erros e, ao mesmo tempo, não insere registros duplicados.

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

Usar REPLACEem vez de INSERT. Se o registro for novo, ele é inserido da mesma forma que INSERT. Se for uma duplicata, o novo registro substitui o antigo -

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 2 rows affected (0.00 sec)

INSERT IGNORE e REPLACE devem ser escolhidos de acordo com o comportamento de tratamento de duplicatas que você deseja efetuar. INSERT IGNORE mantém o primeiro de um conjunto de registros duplicados e descarta o restante. REPLACE mantém o último de um conjunto de duplicatas e apaga as anteriores.

Outra maneira de impor exclusividade é adicionar um índice UNIQUE em vez de uma PRIMARY KEY a uma tabela.

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

Contando e Identificando Duplicados

A seguir está a consulta para contar registros duplicados com first_name e last_name em uma tabela.

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
   -> FROM person_tbl
   -> GROUP BY last_name, first_name
   -> HAVING repetitions > 1;

Esta consulta retornará uma lista de todos os registros duplicados na tabela person_tbl. Em geral, para identificar conjuntos de valores que estão duplicados, faça o seguinte -

Determine quais colunas contêm os valores que podem ser duplicados.
Liste essas colunas na lista de seleção de coluna, junto com COUNT (*).
Liste também as colunas na cláusula GROUP BY.
Adicione uma cláusula HAVING que elimina valores exclusivos, exigindo que as contagens de grupo sejam maiores que um.

Eliminando duplicatas de um resultado de consulta:

Você pode usar DISTINCT junto com a instrução SELECT para descobrir os registros exclusivos disponíveis em uma tabela.

mysql> SELECT DISTINCT last_name, first_name
   -> FROM person_tbl
   -> ORDER BY last_name;

Uma alternativa para DISTINCT é adicionar uma cláusula GROUP BY que nomeia as colunas que você está selecionando. Isso tem o efeito de remover duplicatas e selecionar apenas as combinações exclusivas de valores nas colunas especificadas -

mysql> SELECT last_name, first_name
   -> FROM person_tbl
   -> GROUP BY (last_name, first_name);

Removendo Duplicados Usando Substituição de Mesa

Se você tiver registros duplicados em uma tabela e quiser remover todos os registros duplicados dessa tabela, então aqui está o procedimento -

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
   -> FROM person_tbl;
   -> GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

Uma maneira fácil de remover registros duplicados de uma tabela é adicionar um INDEX ou PRIMAY KEY a essa tabela. Mesmo se esta tabela já estiver disponível, você pode usar esta técnica para remover registros duplicados e você estará seguro no futuro também.

mysql> ALTER IGNORE TABLE person_tbl 
   -> ADD PRIMARY KEY (last_name, first_name);