Supprimer les doublons dans une table MySQL

Si vous arrivez sur cet article, c’est que comme un grand nombre de développeurs, vous êtes confronté à une table MySQL qui contient des enregistrement en doublon.

Par défaut MySQL sait gérer les index uniques qui permettent d’éviter ce genre de situation mais il existe des cas spécifiques où cette option ne résout pas le problème.

Cela se produit par exemple, lorsque en cas de modification d’une valeur, vous souhaitez conserver la précédente, et donc vous insérez un nouvel enregistrement avec un horodatage actualisé. C’est le cas notamment dans une table de suivi des connexions d’un utilisateur de votre site.

C’est à partir de cet exemple que nous allons détailler la procédure pour identifier et supprimer les lignes en doublon. On travaille avec une table dans laquelle un enregistrement est inséré chaque fois qu’un utilisateur se connecte à notre application.

Avec la méthode de l’auto-jointure

Nous prendrons une table de suivi de connexion composé des colonnes suivantes :

• id : Clé primaire de la table – Numérique auto-incrémenté
• user : Code de l’utilisateur
• horodatage : Date et heure de la connexion

Dans cette table, nous avons ajouté une nouvelle ligne à chaque connexion d’un utilisateur. Au bout de quelques temps, nous retrouvons :

• 2 lignes pour toto
• 3 lignes pour polo
• 1 ligne pour mickey

Pour faire le ménage dans notre application, et respecter le droit à l’oubli, nous décidons donc de ne conserver uniquement la dernière connexion par utilisateur.

La méthode la plus rapide est de passer par une jointure de la table sur elle-même afin d’identifier les lignes en doublons. C’est à dire que pour chacune des lignes, on va relire la table pour déterminer s’il existe des enregistrement à supprimer : des lignes avec le même utilisateur mais avec un horodatage antérieur.

SELECT A.*
FROM `connexion` A, `connexion` B
WHERE A.`id` <> B.`id` AND A.`user` = B.`user`
AND A.`horodatage` < B.`horodatage`

Les lignes à supprimer sont rattachées à l’alias A, on sélectionne ainsi toutes les lignes ayant un code utilisateur existant sur une autre ligne mais avec un horodatage de connexion antérieur.

Dans cet exemple ce n’est pas forcément pertinent, mais pour éviter tout conflit, on précise bien que les id doivent être différents et donc une ligne ne peut pas satisfaire la condition.

Une fois les lignes identifiées, il ne reste plus qu’à inclure le SELECT ci-dessus dans un DELETE

DELETE FROM `connexion` WHERE `id` IN (
  SELECT A.`id`
  FROM `connexion` A, `connexion` B
  WHERE A.`id` <> B.`id` AND A.`user` = B.`user`
  AND A.`horodatage` < B.`horodatage`
)

Le cas des tables très volumineuses

Pour des tables ayant plusieurs centaines de milliers de lignes, la jointure de la table sur elle-même va réaliser un produit cartésien et donc travailler potentiellement avec plusieurs milliards de lignes. Ce n’est pas un problème en soi mais si vous lancez cette requête via un client web (phpMyAdmin ou une de vos pages) vous serez alors certainement limités par le timeout défini sur le serveur ou sur la mémoire disponible pour un script.

Dans ce cas, la meilleure solution est de passer par une table intermédiaire qui stockera les identifiants de lignes à supprimer. Pour cela, les requêtes sont englobées dans un script PHP. Cette méthode a donné de meilleurs résultats pour des tables volumineuses.

// Recherche des doublons et insertion dans la table Delete
$sql = "SELECT id FROM connexion ";
$sql.= "WHERE id IN ";
$sql.= '    (SELECT A.id FROM connexion A, connexion B ';
$sql.= '     WHERE A.id <> B.id and A.user=B.user AND A.horodatage < B.horodatage) '; 
$sth = $this->_dbh->prepare($sql);
$sth->execute();
while($result = $sth->fetch(PDO::FETCH_ASSOC)) {
  $sql_insert = 'INSERT INTO idPourSupprimer (`id`, `idConnexion`) ';
  $sql_insert.= "VALUES ('', :idConnexion) ";
  $sth_insert = $this->_dbh->prepare($sql_insert);
  $sth_insert->bindValue(':idConnexion', $result['id'], PDO::PARAM_INT);
  $sth_insert->execute();
}
echo 'Sélection des doublons à supprimer terminée<br/>';
// Suppression à partir de la table Delete
$sql = 'SELECT idConnexion FROM idPourSupprimer ';
$sth = $this->_dbh->prepare($sql);
$sth->execute();
while($result = $sth->fetch(PDO::FETCH_ASSOC)) {
  $sql_delete = 'DELETE FROM connexion ';
  $sql_delete.= 'WHERE id = :id ';
  $sth_delete = $this->_dbh->prepare($sql_delete);
  $sth_delete->bindValue(':id', $result['idConnexion'], PDO::PARAM_INT);
  $sth_delete->execute();
}
echo 'Lignes en double supprimées : ' . $sth->rowCount() . '<br/>';
// On vide la table des définitions à supprimer
$sql = 'TRUNCATE TABLE idPourSupprimer';
$sth = $this->_dbh->prepare($sql);
$sth->execute();