如何安全地只删除重复的行? [英] How to safely remove only duplicated rows?

问题描述

我有一个包含 6.820.483 的表，在这些行之间有很多重复项，我发现运行这个查询:

I have a tables that contains 6.820.483 and between these rows there a lot of duplicates, I discovered that running this query:

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

结构示例:

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

我怎样才能安全地只删除重复项?在上面的示例中，表格应如下所示:

how can I safely remove only the duplicates? In the example above the table should looks like:

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

表结构:

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;

推荐答案

我不是 MySQL 的期望，但你可以试试这个(如果你确定在此期间不会插入新记录):

I'm not a MySQL expect but you could try this (if you're sure no new records will be inserted in the meantime):

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;

为了确保不再创建重复记录，您可以执行以下操作:

In order to make sure no duplicated records are created anymore, you could do the following:

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

在此之后，重新填充表并清理:

After this, repopulate the table and clean up:

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

DROP TABLE tmp_fixtures;

这篇关于如何安全地只删除重复的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！