Supprimer les lignes dupliquées dans MySQL

Si vous ne voulez pas modifier les propriétés d'une colonne, vous pouvez utiliser la requête ci-dessous.

puisque vous avez une colonne qui a des id uniques (par exemple, auto_increment colonnes), vous pouvez l'utiliser pour enlever les doublons:

DELETE `a`
FROM
    `jobs` AS `a`,
    `jobs` AS `b`
WHERE
    -- IMPORTANT: Ensures one version remains
    -- Change "ID" to your unique column's name
    `a`.`ID` < `b`.`ID`

    -- Any duplicates you want to check for
    AND (`a`.`title` = `b`.`title` OR `a`.`title` IS NULL AND `b`.`title` IS NULL)
    AND (`a`.`company` = `b`.`company` OR `a`.`company` IS NULL AND `b`.`company` IS NULL)
    AND (`a`.`site_id` = `b`.`site_id` OR `a`.`site_id` IS NULL AND `b`.`site_id` IS NULL);

dans MySQL, vous pouvez le simplifier encore plus avec le opérateur NULL - safe égal (alias "opérateur de vaisseau spatial " ):

DELETE `a`
FROM
    `jobs` AS `a`,
    `jobs` AS `b`
WHERE
    -- IMPORTANT: Ensures one version remains
    -- Change "ID" to your unique column's name
    `a`.`ID` < `b`.`ID`

    -- Any duplicates you want to check for
    AND `a`.`title` <=> `b`.`title`
    AND `a`.`company` <=> `b`.`company`
    AND `a`.`site_id` <=> `b`.`site_id`;

MySQL a des restrictions sur la référence à la table que vous supprimez. Vous pouvez contourner cela avec une table temporaire, comme:

create temporary table tmpTable (id int);

insert  tmpTable
        (id)
select  id
from    YourTable yt
where   exists
        (
        select  *
        from    YourTabe yt2
        where   yt2.title = yt.title
                and yt2.company = yt.company
                and yt2.site_id = yt.site_id
                and yt2.id > yt.id
        );

delete  
from    YourTable
where   ID in (select id from tmpTable);

de la suggestion de Kostanos dans les commentaires:

La seule requête lente ci-dessus est SUPPRIMER, pour les cas où vous avez une très grande base de données. Cette requête pourrait être plus rapide:

DELETE FROM YourTable USING YourTable, tmpTable WHERE YourTable.id=tmpTable.id

si la déclaration IGNORE ne fonctionne pas comme dans mon cas, vous pouvez utiliser la déclaration suivante:

CREATE TABLE your_table_deduped like your_table;
INSERT your_table_deduped SELECT * FROM your_table GROUP BY index1_id, index2_id;
RENAME TABLE your_table TO your_table_with_dupes;
RENAME TABLE your_table_deduped TO your_table;
#OPTIONAL
ALTER TABLE `your_table` ADD UNIQUE `unique_index` (`index1_id`, `index2_id`);
#OPTIONAL
DROP TABLE your_table_with_dupes;

il y a une autre solution:

DELETE t1 FROM my_table t1, my_table t2 WHERE t1.id < t2.id AND t1.my_field = t2.my_field AND t1.my_field_2 = t2.my_field_2 AND ...

supprimer les doublons sur les tables MySQL est un problème courant, qui est généralement le résultat d'une contrainte manquante pour éviter ces doublons avant main. Mais ce problème commun s'accompagne généralement de besoins spécifiques... qui nécessitent des approches spécifiques. L'approche devrait être différente selon, par exemple, la taille des données, l'entrée dupliquée qui devrait être conservée (généralement la première ou la dernière), s'il y a des index à conserver, ou si nous voulons effectuer n'importe quel des mesures supplémentaires sur les données dupliquées.

il y a aussi quelques spécificités sur MySQL lui-même, comme le fait de ne pas pouvoir référencer la même table sur un FROM lors d'une mise à jour de table (cela soulèvera L'erreur MySQL #1093). Cette limitation peut être surmontée en utilisant une requête interne avec une table temporaire (comme suggéré sur certaines approches ci-dessus). Mais cette requête interne ne fonctionnera pas particulièrement bien lorsqu'il s'agit de sources de données massives.

toutefois, un il existe une meilleure approche pour éliminer les doublons, qui est à la fois efficace et fiable, et qui peut être facilement adaptée à différents besoins.

l'idée générale est de créer une nouvelle table temporaire, généralement en ajoutant une contrainte unique pour éviter d'autres doublons, et D'insérer les données de votre ancienne table dans la nouvelle, tout en prenant soin des doublons. Cette approche repose sur des requêtes D'insertion MySQL simples, crée une nouvelle contrainte pour éviter d'autres doublons, et saute la nécessité d'utiliser une requête interne pour rechercher des doublons et une table temporaire qui doit être conservée en mémoire (s'adaptant ainsi aux sources de big data aussi).

C'est ainsi que cela peut être réalisé. Étant donné que nous avons un tableau employé , avec les colonnes suivantes:

employee (id, first_name, last_name, start_date, ssn)

afin de supprimer les lignes avec un double ssn colonne, et en gardant seulement la première entrée trouvée, le processus suivant peut être suivi:

-- create a new tmp_eployee table
CREATE TABLE tmp_employee LIKE employee;

-- add a unique constraint
ALTER TABLE tmp_employee ADD UNIQUE(ssn);

-- scan over the employee table to insert employee entries
INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id;

-- rename tables
RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

explication technique

• la ligne #1 crée une nouvelle table tmp_employee avec exactement la même structure que la employee table
• ligne #2 ajoute une contrainte UNIQUE à la nouvelle table tmp_eployee pour éviter d'autres doublons
• ligne #3 scans sur l'original employé table par id, insérant de nouvelles entrées d'employé dans le nouveau tmp_eployee table, tout en ignorant entrées dupliquées
• la ligne #4 renomme les tables, de sorte que la nouvelle table employee contient toutes les entrées sans les doublons, et une copie de sauvegarde des anciennes données est conservée sur la backup_employee table

⇒ utilisation de cette approche, Des registres de 1,6 M ont été convertis en 6K en moins de 200s.

Chetan , en suivant ce processus, vous pouvez rapidement et facilement supprimer tous vos doublons et créer une contrainte UNIQUE en exécutant:

CREATE TABLE tmp_jobs LIKE jobs;

ALTER TABLE tmp_jobs ADD UNIQUE(site_id, title, company);

INSERT IGNORE INTO tmp_jobs SELECT * FROM jobs ORDER BY id;

RENAME TABLE jobs TO backup_jobs, tmp_jobs TO jobs;

bien sûr, ce processus peut être modifié pour l'adapter aux différents besoins lors de la suppression des doublons. Quelques exemples à suivre.

✔ Variation pour conserver la dernière entrée à la place du premier

Parfois, nous avons besoin de garder la dernière entrée dupliquée au lieu de la première.

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id DESC;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

• Sur la ligne n ° 3, le ORDER BY id DESC la clause qui rend la dernière carte d'identité pour obtenir la priorité sur le reste

✔ Variation pour effectuer certaines tâches sur les doublons, par exemple en comptant sur les doublons trouvés

parfois nous devons effectuer d'autres traitements sur les entrées dupliquées qui sont trouvées (comme garder un compte des doublons).

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

ALTER TABLE tmp_employee ADD COLUMN n_duplicates INT DEFAULT 0;

INSERT INTO tmp_employee SELECT * FROM employee ORDER BY id ON DUPLICATE KEY UPDATE n_duplicates=n_duplicates+1;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

• à la ligne 3, une nouvelle colonne n_duplicates est créée
• Sur la ligne n ° 4, la INSERT INTO ... La requête est utilisée pour effectuer une mise à jour supplémentaire lorsqu'un duplicata est trouvé (dans ce cas, l'augmentation d'un compteur) Le insérer DANS. .. Sur la clé dupliquer mise à jour requête peut être utilisé pour effectuer différents types de mises à jour pour les doublons trouvés.

✔ Variation de la régénération de l'auto-incrémental champ id

Parfois, nous utilisons un auto incrément champ et, afin de garder l'index aussi compact que possible, nous pouvons profiter de la suppression des doublons de régénérer l'auto-incrémental champ dans la nouvelle table temporaire.

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

INSERT IGNORE INTO tmp_employee SELECT (first_name, last_name, start_date, ssn) FROM employee ORDER BY id;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

• sur la ligne #3, au lieu de sélectionner tous les champs sur la table, le champ id est sauté de sorte que le moteur DB génère un nouveau automatiquement

✔ autres variations

de nombreuses autres modifications sont également réalisables en fonction du comportement souhaité. Par exemple, les requêtes suivantes utilisera une deuxième table temporaire, en outre 1) garder la dernière entrée au lieu de la première; et 2) augmenter un compteur sur les doublons trouvés; aussi 3) régénérer le champ auto-incrémentiel id tout en gardant l'ordre d'entrée comme il était sur les données précédentes.

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

ALTER TABLE tmp_employee ADD COLUMN n_duplicates INT DEFAULT 0;

INSERT INTO tmp_employee SELECT * FROM employee ORDER BY id DESC ON DUPLICATE KEY UPDATE n_duplicates=n_duplicates+1;

CREATE TABLE tmp_employee2 LIKE tmp_employee;

INSERT INTO tmp_employee2 SELECT (first_name, last_name, start_date, ssn) FROM tmp_employee ORDER BY id;

DROP TABLE tmp_employee;

RENAME TABLE employee TO backup_employee, tmp_employee2 TO employee;

j'ai cette requête snipet pour SQLServer mais je pense Qu'il peut être utilisé dans D'autres SGBD avec peu de changements:

DELETE
FROM Table
WHERE Table.idTable IN  (  
    SELECT MAX(idTable)
    FROM idTable
    GROUP BY field1, field2, field3
    HAVING COUNT(*) > 1)

j'ai oublié de vous dire que cette requête ne supprime pas la ligne avec l'id le plus bas des lignes dupliquées. Si cela fonctionne pour vous essayez cette requête:

DELETE
FROM jobs
WHERE jobs.id IN  (  
    SELECT MAX(id)
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING COUNT(*) > 1)

simple et rapide pour tous les cas:

CREATE TEMPORARY TABLE IF NOT EXISTS _temp_duplicates AS (SELECT dub.id FROM table_with_duplications dub GROUP BY dub.field_must_be_uniq_1, dub.field_must_be_uniq_2 HAVING COUNT(*)  > 1);

DELETE FROM table_with_duplications WHERE id IN (SELECT id FROM _temp_duplicates);

le moyen le plus rapide est d'insérer des lignes distinctes dans une table temporaire. En utilisant delete, il m'a fallu quelques heures pour supprimer les doublons d'une table de 8 millions de lignes. En utilisant insert et distinct, ça n'a pris que 13 minutes.

CREATE TABLE tempTableName LIKE tableName;  
CREATE INDEX ix_all_id ON tableName(cellId,attributeId,entityRowId,value);  
INSERT INTO tempTableName(cellId,attributeId,entityRowId,value) SELECT DISTINCT cellId,attributeId,entityRowId,value FROM tableName;  
TRUNCATE TABLE tableName;
INSERT INTO tableName SELECT * FROM tempTableName; 
DROP TABLE tempTableName;  

je continue à visiter cette page à tout moment je google "remove duplicates form mysql" mais pour mes solutions de theIGNORE ne fonctionnent pas parce que j'ai une InnoDB tables mysql

ce code fonctionne mieux à tout moment

CREATE TABLE tableToclean_temp LIKE tableToclean;
ALTER TABLE tableToclean_temp ADD UNIQUE INDEX (fontsinuse_id);
INSERT IGNORE INTO tableToclean_temp SELECT * FROM tableToclean;
DROP TABLE tableToclean;
RENAME TABLE tableToclean_temp TO tableToclean;

tableToclean = le nom de la table que vous devez nettoyer""

tableToclean_temp = une table temporaire créée et supprimée

cette solution déplacera les doublons dans un tableau et les uniques dans un autre .

-- speed up creating uniques table if dealing with many rows
CREATE INDEX temp_idx ON jobs(site_id, company, title, location);

-- create the table with unique rows
INSERT jobs_uniques SELECT * FROM
    (
    SELECT * 
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING count(1) > 1
    UNION
    SELECT *
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING count(1) = 1
) x

-- create the table with duplicate rows
INSERT jobs_dupes 
SELECT * 
FROM jobs
WHERE id NOT IN
(SELECT id FROM jobs_uniques)

-- confirm the difference between uniques and dupes tables
SELECT COUNT(1)
AS jobs, 
(SELECT COUNT(1) FROM jobs_dupes) + (SELECT COUNT(1) FROM jobs_uniques)
AS sum
FROM jobs

une solution qui est simple à comprendre et fonctionne sans clé primaire:

1) ajouter une nouvelle colonne booléenne

alter table mytable add tokeep boolean;

2) Ajouter une contrainte sur les colonnes dupliquées et la nouvelle colonne

alter table mytable add constraint preventdupe unique (mycol1, mycol2, tokeep);

3) Mettez la colonne booléenne à true. Cela ne réussira que sur une des lignes dupliquées à cause de la nouvelle contrainte

update ignore mytable set tokeep = true;

4) supprimer les lignes qui n'ont pas été marqués comme tokeep

delete from mytable where tokeep is null;

5) supprimer la colonne ajoutée

alter table mytable drop tokeep;

je vous suggère de garder la contrainte que vous avez ajoutée, afin que de nouveaux doublons soient évités à l'avenir.

supprimer les lignes en double en utilisant supprimer la déclaration jointure MySQL vous fournit la déclaration DELETE JOIN que vous pouvez utiliser pour supprimer des lignes dupliquées rapidement.

l'énoncé suivant supprime les lignes en double et conserve l'id le plus élevé:

DELETE t1 FROM contacts t1
    INNER JOIN
contacts t2 WHERE
t1.id < t2.id AND t1.email = t2.email;

si vous avez une grande table avec un grand nombre d'enregistrements, alors les solutions ci-dessus ne fonctionneront pas ou prendront trop de temps. Alors nous avons une solution différente

-- Create temporary table

CREATE TABLE temp_table LIKE table1;

-- Add constraint
ALTER TABLE temp_table ADD UNIQUE(title, company,site_id);

-- Copy data
INSERT IGNORE INTO temp_table SELECT * FROM table1;

-- Rename and drop
RENAME TABLE table1 TO old_table1, temp_table TO table1;
DROP TABLE old_table1;

j'aime être un peu plus précis quant aux enregistrements que je supprime donc voici ma solution:

delete
from jobs c1
where not c1.location = 'Paris'
and  c1.site_id > 64218
and exists 
(  
select * from jobs c2 
where c2.site_id = c1.site_id
and   c2.company = c1.company
and   c2.location = c1.location
and   c2.title = c1.title
and   c2.site_id > 63412
and   c2.site_id < 64219
)

vous pouvez facilement supprimer les enregistrements en double de ce code..

$qry = mysql_query("SELECT * from cities");
while($qry_row = mysql_fetch_array($qry))
{
$qry2 = mysql_query("SELECT * from cities2 where city = '".$qry_row['city']."'");

if(mysql_num_rows($qry2) > 1){
    while($row = mysql_fetch_array($qry2)){
        $city_arry[] = $row;

        }

    $total = sizeof($city_arry) - 1;
        for($i=1; $i<=$total; $i++){


            mysql_query( "delete from cities2 where town_id = '".$city_arry[$i][0]."'");

            }
    }
    //exit;
}

j'ai dû le faire avec des champs de texte et j'ai rencontré la limite de 100 octets sur l'index.

j'ai résolu cela en ajoutant une colonne, en faisant un hachage md5 des champs, et en faisant l'alter.

ALTER TABLE table ADD `merged` VARCHAR( 40 ) NOT NULL ;
UPDATE TABLE SET merged` = MD5(CONCAT(`col1`, `col2`, `col3`))
ALTER IGNORE TABLE table ADD UNIQUE INDEX idx_name (`merged`);