Comment supprimer les données en double d'une base de données MySQL

pengenalan

Apabila bekerja dengan pangkalan data, terutamanya dalam persekitaran di mana set data besar sentiasa diubah suai atau ditambah, adalah perkara biasa untuk menemui data pendua. Ini boleh menyebabkan ketidakcekapan dalam prestasi pertanyaan, ketidakkonsistenan data dan laporan yang tidak tepat. Nasib baik, MySQL menyediakan alatan yang teguh untuk mengenal pasti dan mengalih keluar pendua ini.

Dalam blog ini, saya akan membimbing anda melalui pendekatan yang cekap dan komprehensif untuk mengesan dan mengalih keluar rekod pendua dalam pangkalan data MySQL. Kaedah di sini boleh digunakan untuk kebanyakan sistem pangkalan data hubungan, tetapi kami akan menumpukan pada MySQL untuk tutorial ini.

Apa yang Menjadi Pendua?

Sebelum menyelami kod, adalah penting untuk menentukan perkara yang layak sebagai pendua. Dalam kebanyakan kes, pendua bukan hanya rekod yang semua lajur mempunyai nilai yang sama. Selalunya, pendua mungkin mempunyai nilai yang sama dalam subset lajur utama. Contohnya, dalam jadual pengguna, dua rekod mungkin mempunyai e-mel yang sama tetapi berbeza dalam medan lain seperti nama pengguna atau tarikh_daftar.

Untuk kesederhanaan, dalam tutorial ini, kami akan menganggap pendua ialah baris yang semua lajur (atau subset lajur) sepadan.

Pertimbangkan contoh pekerja meja berikut:

id	first_name	last_name	email	salary
1	John	Doe	john@example.com	60000
2	Jane	Smith	jane@example.com	65000
3	John	Doe	john@example.com	60000
4	Alex	Johnson	alex@example.com	72000
5	John	Doe	john@example.com	60000

Ici, les lignes avec id = 1, id = 3 et id = 5 sont des doublons. Notre objectif est de les supprimer en n’en gardant qu’une seule copie.

Guide étape par étape pour supprimer les doublons

1. Identifier les enregistrements en double

La première étape consiste à identifier quels enregistrements sont dupliqués. Pour ce faire, nous devons regrouper les enregistrements par colonnes qui doivent être uniques. Dans ce cas, supposons que la combinaison prénom, nom et adresse e-mail doit être unique.

Vous pouvez utiliser la requête suivante pour rechercher des doublons :

SELECT first_name, last_name, email, COUNT(*)
FROM employees
GROUP BY first_name, last_name, email
HAVING COUNT(*) > 1;

Cette requête regroupe les enregistrements en fonction des colonnes first_name, last_name et email, et affiche uniquement les groupes avec plus d'une occurrence, c'est-à-dire les doublons.

2. Sélectionnez les lignes en double à conserver ou à supprimer

Après avoir identifié les doublons, nous avons besoin d'une méthode pour les supprimer. Une approche courante consiste à conserver l’enregistrement portant l’identifiant le plus petit ou le plus grand et à supprimer les autres. Nous pouvons le faire en utilisant une auto-jointure pour faire correspondre chaque enregistrement en double avec celui que nous souhaitons conserver.

Exemple:

DELETE e1
FROM employees e1
JOIN employees e2
ON e1.first_name = e2.first_name
AND e1.last_name = e2.last_name
AND e1.email = e2.email
AND e1.id > e2.id;

Cette requête fonctionne comme suit :

• Il effectue une auto-jointure sur la table des employés où les enregistrements ont le même prénom, nom et adresse e-mail.
• Cela garantit que seules les lignes avec un identifiant plus élevé (c'est-à-dire qu'elles ont été insérées plus tard) seront supprimées, tout en conservant l'enregistrement avec le plus petit identifiant.

3. Utiliser un tableau temporaire pour des scénarios plus complexes

Dans certaines situations, vous pouvez avoir des conditions plus complexes pour déterminer quel duplicata conserver, comme la conservation du dossier avec la dernière date d'inscription ou le salaire le plus élevé.

Dans de tels cas, vous pouvez utiliser une table temporaire pour stocker les lignes que vous souhaitez conserver, puis supprimer tout le reste.

Exemple:

CREATE TEMPORARY TABLE temp_employees AS
SELECT * FROM employees e1
WHERE e1.id IN (
    SELECT MIN(id)
    FROM employees
    GROUP BY first_name, last_name, email
);

DELETE FROM employees
WHERE id NOT IN (SELECT id FROM temp_employees);

Voici ce que fait cette requête :

1. Crée une table temporaire temp_employees contenant un seul enregistrement unique pour chaque combinaison de prénom, nom et e-mail (dans ce cas, l'enregistrement avec le plus petit identifiant).
2. Supprime tous les enregistrements de la table des employés d'origine qui ne sont pas présents dans la table temp_employees.

Cette méthode est utile lorsque les critères permettant de décider quel doublon conserver sont plus sophistiqués que la simple utilisation de l'identifiant.

4. Ajouter des contraintes uniques pour éviter les futurs doublons

Une fois que vous avez nettoyé les doublons, c'est une bonne idée d'éviter qu'ils ne se reproduisent. Vous pouvez y parvenir en ajoutant une contrainte unique aux colonnes concernées.

Par exemple, pour éviter toute ligne future contenant les mêmes prénom, nom et adresse e-mail :

ALTER TABLE employees
ADD CONSTRAINT unique_employee
UNIQUE (first_name, last_name, email);

Cela garantit que la base de données générera une erreur si une tentative est faite d'insérer un enregistrement en double, préservant ainsi l'intégrité des données.

Considérations relatives aux performances

Lorsque vous travaillez avec de grands ensembles de données, la suppression des doublons peut être lente et gourmande en ressources. Voici quelques conseils pour optimiser les performances :

• Indexation : Assurez-vous que les colonnes impliquées dans le filtrage (comme prénom, nom, email) sont indexées. Cela peut considérablement accélérer le processus.
• Suppression par lots : Si vous supprimez un grand nombre de lignes, pensez à le faire par lots pour éviter les longs verrous et réduire la charge sur le serveur.

Exemple de suppression par lots :

DELETE e1
FROM employees e1
JOIN employees e2
ON e1.first_name = e2.first_name
AND e1.last_name = e2.last_name
AND e1.email = e2.email
AND e1.id > e2.id
LIMIT 1000;

Vous pouvez exécuter cette requête plusieurs fois jusqu'à ce que tous les doublons soient supprimés.

Conclusion

Le traitement des enregistrements en double dans une base de données MySQL est une tâche courante qui, si elle est mal gérée, peut entraîner une perte de données ou des résultats incohérents. En suivant les étapes décrites dans ce blog, vous pouvez supprimer les doublons en toute confiance et efficacement tout en préservant l'intégrité de vos données. De plus, en ajoutant des contraintes uniques, vous pouvez garantir que les futurs doublons seront évités, contribuant ainsi à maintenir des ensembles de données propres et fiables.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!