J'ai une table qui contient 6.820.483 et entre ces lignes, il y a beaucoup de doublons, j'ai découvert que l'exécution de cette requête:

SELECT player_id, match_id, team_id, count(*) 
FROM fixtures
GROUP BY player_id, match_id, team_id
HAVING COUNT(*) > 1

Exemple de structure:

player_id | match_id  | team_id
  19014       2506172    12573
  19014       2506172    12573
  19015       2506172    12573
  19016       2506172    12573
  19016       2506172    12573
  19016       2506172    12573

Comment puis-je supprimer en toute sécurité uniquement les doublons? Dans l'exemple ci-dessus, le tableau devrait ressembler à:

player_id | match_id  | team_id
  19014       2506172    12573
  19015       2506172    12573
  19016       2506172    12573

Structure de la table:

CREATE TABLE IF NOT EXISTS `swp`.`fixtures` (
  `player_id` INT NOT NULL,
  `match_id` INT NOT NULL,
  `team_id` INT NOT NULL,
  INDEX `player_id_idx` (`player_id` ASC),
  INDEX `match_id_idx` (`match_id` ASC),
  INDEX `FK_team_fixtures_id_idx` (`team_id` ASC),
  CONSTRAINT `FK_player_fixtures_id`
    FOREIGN KEY (`player_id`)
    REFERENCES `swp`.`player` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_match_fixtures_id`
    FOREIGN KEY (`match_id`)
    REFERENCES `swp`.`match` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_team_fixtures_id`
    FOREIGN KEY (`team_id`)
    REFERENCES `swp`.`team` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
1
Spartaok 20 nov. 2018 à 18:20

3 réponses

Meilleure réponse

Je ne suis pas un MySQL, mais vous pouvez essayer ceci (si vous êtes sûr qu'aucun nouvel enregistrement ne sera inséré entre-temps):

CREATE TABLE tmp_fixtures
(
  player_id INT NOT NULL,
  match_id  INT NOT NULL,
  team_id   INT NOT NULL
);

SELECT DISTINCT
       player_id,
       match_id,
       team_id
  INTO tmp_fixtures
  FROM fixtures;

TRUNCATE TABLE fixtures;

Afin de vous assurer qu'aucun enregistrement dupliqué n'est plus créé, vous pouvez effectuer les opérations suivantes:

ALTER TABLE fixtures ADD PRIMARY KEY (player_id, match_id, team_id);

Après cela, remplissez la table et nettoyez:

INSERT INTO fixtures (player_id, match_id, team_id)
  SELECT player_id,
         match_id,
         team_id
  FROM   tmp_fixtures;

DROP TABLE tmp_fixtures;
3
Robert Kock 21 nov. 2018 à 08:15

Il n'y a pas d'autre solution que de sauvegarder des lignes distinctes de la table dans une table temporaire et de la restaurer par la suite comme @Robert Kock l'a proposé, mais:
les doublons peuvent réapparaître comme avant.
Donc, avant de restaurer la table, exécutez cette instruction:

ALTER TABLE swp.fixtures ADD PRIMARY KEY(player_id, match_id, team_id);

Pour ajouter une clé primaire à plusieurs colonnes afin que le problème ne réapparaisse plus.

Modifier1
De: https: // dev. mysql.com/doc/refman/8.0/en/ansi-diff-select-into-table.html

MySQL Server ne prend pas en charge SELECT ... INTO TABLE Sybase SQL extension. Au lieu de cela, MySQL Server prend en charge INSERT INTO ... SELECT syntaxe SQL standard, qui est fondamentalement la même chose. Voir section 13.2.6.1, "INSERT ... SELECT Syntaxe". Par exemple:

INSERT INTO tbl_temp2 (fld_id)
    SELECT tbl_temp1.fld_order_id
    FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;


Edit2 (après la suggestion de Gordon Linoff)
Donc, tout le code devrait être:

CREATE TABLE tmp_fixtures AS 
    SELECT DISTINCT player_id, match_id, team_id FROM fixtures;

TRUNCATE TABLE fixtures;

ALTER TABLE fixtures ADD PRIMARY KEY(player_id, match_id, team_id);

INSERT INTO fixtures (player_id, match_id, team_id)
    SELECT player_id, match_id, team_id FROM tmp_fixtures;

DROP TABLE tmp_fixtures;


À utiliser avec prudence et uniquement si vous disposez d'une sauvegarde de vos données.

1
forpas 20 nov. 2018 à 16:35

Robert et forpas ont tous deux fourni de bien meilleures réponses, mais techniquement, je pense que cela pourrait être fait sans créer une nouvelle table (au moins en MSSQL). J'ai essayé de traduire en MySQL. Encore une fois, je ne le ferais probablement jamais de cette façon, en particulier sur de grands ensembles de données, mais c'était un exercice intéressant.

Comme avec toutes les solutions, si vous essayez cette sauvegarde d'abord votre table.

DECLARE @i INT = 0

WHILE @i < 6820483
BEGIN
  DELETE FROM f
  FROM (
      SELECT *
      FROM fixtures
      WHERE player_id IN (SELECT player_id FROM fixtures GROUP BY player_id HAVING COUNT(*) > 1)
      LIMIT 1
  ) f

  SET @i = @i + 1
END

Comme indiqué par les autres réponses, vous souhaiterez probablement créer un Primary Key pour éviter cela à l'avenir.

3
Derrick Moeller 20 nov. 2018 à 16:29