Gestion des données dupliquées MySQL
Certaines tables de données MySQL peuvent contenir des enregistrements en double. Dans certains cas, nous autorisons l'existence de données en double, mais parfois nous devons également supprimer ces données en double.
Dans ce chapitre, nous allons présenter comment empêcher l'apparition de données en double dans les tables de données et comment supprimer les données en double des tables de données.
Prévention des données en double dans les tables
Vous pouvez définir des champs spécifiés dans une table de données MySQL commePRIMARY KEYouUNIQUEindex pour garantir l'unicité des données.Essayons un exemple : la table suivante n'a ni index ni clé primaire, donc la table autorise plusieurs enregistrements en double.
CREATE TABLE person_tbl
(
first_name CHAR(20),
last_name CHAR(20),
sex CHAR(10)
);
Si vous souhaitez définir les champs first_name et last_name dans la table afin que les données ne puissent pas être dupliquées, vous pouvez utiliser le mode à double clé primaire pour définir l'unicité des données. Si vous définissez des clés primaires doubles, la valeur par défaut de cette clé ne peut pas être NULL ; elle peut être définie sur NOT NULL. Comme indiqué ci-dessous :
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) );
Si nous définissons un index unique, lors de l'insertion de données en double, l'instruction SQL échouera à s'exécuter et générera une erreur.
La différence entre INSERT IGNORE INTO et INSERT INTO est que INSERT IGNORE INTO ignore les données qui existent déjà dans la base de données. Si la base de données ne contient pas ces données, il insère de nouvelles données ; si elle contient des données, il ignore cet enregistrement. Cela préserve les données existantes dans la base de données et atteint l'objectif d'insérer des données dans les lacunes.
L'exemple suivant utilise INSERT IGNORE INTO. Après exécution, aucune erreur ne se produit et aucune donnée en double n'est insérée dans la table de données :
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)
INSERT IGNORE INTO : lors de l'insertion de données, après que l'unicité de l'enregistrement est définie, si des données en double sont insérées, aucune erreur n'est renvoyée ; seul un avertissement est renvoyé. REPLACE INTO : si un enregistrement avec la même clé primaire ou clé unique existe, il est d'abord supprimé, puis le nouvel enregistrement est inséré.
Une autre façon de définir l'unicité des données consiste à ajouter un index UNIQUE, comme indiqué ci-dessous :
CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), UNIQUE (last_name, first_name) );
Compter les données en double
Ci-dessous, nous allons compter le nombre d'enregistrements en double de first_name et last_name dans la table :
mysql> SELECT COUNT(*) as repetitions, last_name, first_name
-> FROM person_tbl
-> GROUP BY last_name, first_name
-> HAVING repetitions > 1;
L'instruction de requête ci-dessus renverra le nombre d'enregistrements en double dans la table person_tbl. En général, pour rechercher des valeurs en double, procédez comme suit :
- Déterminez quelle colonne contient des valeurs susceptibles d'être dupliquées.
- Listez ces colonnes en utilisant COUNT(*) dans la liste de sélection des colonnes.
- Listez les colonnes dans la clause GROUP BY.
- Utilisez la clause HAVING pour définir le nombre de doublons à une valeur supérieure à 1.
Filtrage des données en double
Si vous devez lire des données non dupliquées, vous pouvez utiliser le mot-clé DISTINCT dans l'instruction SELECT pour filtrer les données en double.
mysql> SELECT DISTINCT last_name, first_name
-> FROM person_tbl;
Vous pouvez également utiliser GROUP BY pour lire des données non dupliquées à partir de la table de données :
mysql> SELECT last_name, first_name
-> FROM person_tbl
-> GROUP BY (last_name, first_name);
Suppression des données en double
Si vous souhaitez supprimer les données en double de la table de données, vous pouvez utiliser l'instruction SQL suivante :
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex); mysql> DROP TABLE person_tbl; mysql> ALTER TABLE tmp RENAME TO person_tbl;
Bien sûr, vous pouvez également utiliser la méthode simple consistant à ajouter INDEX (index) et PRIMARY KEY (clé primaire) à la table de données pour supprimer les enregistrements en double dans la table. La méthode est la suivante :
mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name);
Autres extensions