Comment supprimer les doublons sur une table MySQL ?

Question

J'ai besoin de DELETE les rangs dupliqués pour les données spécifiées sur une MySQL table.

Comment puis-je faire cela avec une requête SQL ?

DELETE (DUPLICATED TITLES) FROM table WHERE SID = "1"

Quelque chose comme ça, mais je ne sais pas comment le faire.

Answer 1

Cela supprime les doublons en place, sans créer une nouvelle table

ALTER IGNORE TABLE `table_name` ADD UNIQUE (title, SID)

note : ne fonctionne bien que si l'index tient dans la mémoire

Answer 2

Supposons que vous ayez un tableau employee avec les colonnes suivantes :

employee (first_name, last_name, start_date)

Afin de supprimer les lignes avec un duplicata first_name colonne :

delete
from employee using employee,
    employee e1
where employee.id > e1.id
    and employee.first_name = e1.first_name

Answer 3

Après avoir supprimé les doublons pour tous les SID, et pas seulement pour un seul.

Avec la table temporaire

CREATE TABLE table_temp AS
SELECT * FROM table GROUP BY title, SID;

DROP TABLE table;
RENAME TABLE table_temp TO table;

Desde temp_table est fraîchement créé, il n'a pas d'index. Vous devrez les recréer après avoir supprimé les doublons. Vous pouvez vérifier quels index vous avez dans la table avec SHOW INDEXES IN table

Sans table d'appoint :

DELETE FROM `table` WHERE id IN (
  SELECT all_duplicates.id FROM (
    SELECT id FROM `table` WHERE (`title`, `SID`) IN (
      SELECT `title`, `SID` FROM `table` GROUP BY `title`, `SID` having count(*) > 1
    )
  ) AS all_duplicates 
  LEFT JOIN (
    SELECT id FROM `table` GROUP BY `title`, `SID` having count(*) > 1
  ) AS grouped_duplicates 
  ON all_duplicates.id = grouped_duplicates.id 
  WHERE grouped_duplicates.id IS NULL
)

Answer 4

Suppression des lignes en double dans MySQL (en supposant que vous ayez une colonne d'horodatage à trier) :

Créez le tableau et insérez quelques lignes :

create table penguins(foo int, bar varchar(15), baz datetime);
insert into penguins values(1, 'skipper', now());
insert into penguins values(1, 'skipper', now());
insert into penguins values(3, 'kowalski', now());
insert into penguins values(3, 'kowalski', now());
insert into penguins values(3, 'kowalski', now());
insert into penguins values(4, 'rico', now());
select * from penguins;
    +------+----------+---------------------+
    | foo  | bar      | baz                 |
    +------+----------+---------------------+
    |    1 | skipper  | 2014-08-25 14:21:54 |
    |    1 | skipper  | 2014-08-25 14:21:59 |
    |    3 | kowalski | 2014-08-25 14:22:09 |
    |    3 | kowalski | 2014-08-25 14:22:13 |
    |    3 | kowalski | 2014-08-25 14:22:15 |
    |    4 | rico     | 2014-08-25 14:22:22 |
    +------+----------+---------------------+
6 rows in set (0.00 sec)

Supprimez les doublons en place :

delete a
    from penguins a
    left join(
    select max(baz) maxtimestamp, foo, bar
    from penguins
    group by foo, bar) b
    on a.baz = maxtimestamp and
    a.foo = b.foo and
    a.bar = b.bar
    where b.maxtimestamp IS NULL;
Query OK, 3 rows affected (0.01 sec)
select * from penguins;
+------+----------+---------------------+
| foo  | bar      | baz                 |
+------+----------+---------------------+
|    1 | skipper  | 2014-08-25 14:21:59 |
|    3 | kowalski | 2014-08-25 14:22:15 |
|    4 | rico     | 2014-08-25 14:22:22 |
+------+----------+---------------------+
3 rows in set (0.00 sec)

Vous avez terminé, les lignes en double sont supprimées, la dernière par timestamp est conservée.

Pour ceux d'entre vous qui ne disposent pas d'un timestamp ou d'une colonne unique.

Vous n'avez pas de timestamp ou une colonne d'index unique pour trier ? Tu vis dans un état de dégénérescence. Vous devrez effectuer des étapes supplémentaires pour supprimer les lignes en double.

créer la table des pingouins et ajouter quelques lignes

create table penguins(foo int, bar varchar(15)); 
insert into penguins values(1, 'skipper'); 
insert into penguins values(1, 'skipper'); 
insert into penguins values(3, 'kowalski'); 
insert into penguins values(3, 'kowalski'); 
insert into penguins values(3, 'kowalski'); 
insert into penguins values(4, 'rico'); 
select * from penguins; 
    # +------+----------+ 
    # | foo  | bar      | 
    # +------+----------+ 
    # |    1 | skipper  | 
    # |    1 | skipper  | 
    # |    3 | kowalski | 
    # |    3 | kowalski | 
    # |    3 | kowalski | 
    # |    4 | rico     | 
    # +------+----------+ 

faites un clone du premier tableau et copiez-le dans celui-ci.

drop table if exists penguins_copy; 
create table penguins_copy as ( SELECT foo, bar FROM penguins );  

#add an autoincrementing primary key: 
ALTER TABLE penguins_copy ADD moo int AUTO_INCREMENT PRIMARY KEY first; 

select * from penguins_copy; 
    # +-----+------+----------+ 
    # | moo | foo  | bar      | 
    # +-----+------+----------+ 
    # |   1 |    1 | skipper  | 
    # |   2 |    1 | skipper  | 
    # |   3 |    3 | kowalski | 
    # |   4 |    3 | kowalski | 
    # |   5 |    3 | kowalski | 
    # |   6 |    4 | rico     | 
    # +-----+------+----------+ 

L'agrégat max fonctionne sur le nouvel indice moo :

delete a from penguins_copy a left join( 
    select max(moo) myindex, foo, bar 
    from penguins_copy 
    group by foo, bar) b 
    on a.moo = b.myindex and 
    a.foo = b.foo and 
    a.bar = b.bar 
    where b.myindex IS NULL; 

#drop the extra column on the copied table 
alter table penguins_copy drop moo; 
select * from penguins_copy; 

#drop the first table and put the copy table back: 
drop table penguins; 
create table penguins select * from penguins_copy; 

observer et nettoyer

drop table penguins_copy; 
select * from penguins;
+------+----------+ 
| foo  | bar      | 
+------+----------+ 
|    1 | skipper  | 
|    3 | kowalski | 
|    4 | rico     | 
+------+----------+ 
    Elapsed: 1458.359 milliseconds 

Que fait cette grosse déclaration de suppression SQL ?

La table pingouins avec l'alias 'a' est jointe à gauche sur un sous-ensemble de table pingouins appelé alias 'b'. La table de droite 'b', qui est un sous-ensemble, trouve le timestamp maximum [ ou moo maximum ] groupé par les colonnes foo et bar. Il est associé à la table de gauche 'a'. (foo,bar,baz) à gauche a toutes les lignes du tableau. Le sous-ensemble de droite 'b' a un (maxtimestamp,foo,bar) qui n'est comparé à gauche qu'à celui qui est le maximum.

Chaque ligne qui n'est pas ce maximum a une valeur maxtimestamp de NULL. Filtrez sur ces lignes NULL et vous obtenez un ensemble de toutes les lignes groupées par foo et bar qui ne sont pas le dernier horodatage baz. Supprimez ceux-là.

Faites une sauvegarde de la table avant de l'exécuter.

Empêchez ce problème de se reproduire sur cette table :

Si vous avez réussi à faire fonctionner ça, et que ça a éteint votre feu de "rangée dupliquée". Super. Maintenant, définissez une nouvelle clé unique composite sur votre table (sur ces deux colonnes) pour empêcher l'ajout de nouveaux doublons.

Comme un bon système immunitaire, les mauvaises lignes ne devraient même pas être autorisées à entrer dans la table au moment de l'insertion. Plus tard, tous les programmes qui ajoutent des doublons émettront des protestations, et lorsque vous les corrigerez, ce problème ne se reproduira plus.

Answer 5

Cette procédure supprime tous les doublons (y compris les multiples) dans une table, en conservant le dernier doublon. Il s'agit d'une extension de Récupération du dernier enregistrement dans chaque groupe

J'espère que cela sera utile à quelqu'un.

DROP TABLE IF EXISTS UniqueIDs;
CREATE Temporary table UniqueIDs (id Int(11));

INSERT INTO UniqueIDs
    (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON
    (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields 
    AND T1.ID < T2.ID)
    WHERE T2.ID IS NULL);

DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);