mysql - 从 MySQL 数据库中删除重复项

Question

我有一个包含 7000 多条记录的数据库。事实证明，这些记录中有几个重复项。我发现了一些关于如何删除重复项并只保留 1 条记录的建议。但在我的例子中，事情有点复杂:如果案例与另一条记录保存相同的数据，那么它们就不仅仅是重复的。相反，几个案例完全可以保存相同的数据。只有当它们保存相同的数据并且都在 30 秒内插入时，它们才会被标记为重复。

因此，我需要一个 SQL 语句来删除重复项(例如:所有字段，id 和 datetime 除外)，如果它们是在 40 秒范围内插入的(例如:评估 datetime 字段)。

由于我完全不是 SQL 专家并且无法在网上找到合适的解决方案，因此我真诚地希望你们中的一些人可以帮助我并为我指明正确的方向。那将不胜感激!

表结构如下:

CREATE TABLE IF NOT EXISTS `wp_ttr_results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `schoolyear` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `area` varchar(15) CHARACTER SET utf8 NOT NULL,
  `content` varchar(10) CHARACTER SET utf8 NOT NULL,
  `types` varchar(100) CHARACTER SET utf8 NOT NULL,
  `tasksWrong` varchar(300) DEFAULT NULL,
  `tasksRight` varchar(300) DEFAULT NULL,
  `tasksData` longtext CHARACTER SET utf8,
  `parent_id` varchar(20) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=68696 ;

所以再次澄清一下，重复案例是指:

[1]除了 id 和 datetime 字段外，所有字段的数据都与另一种情况相同

[2]插入数据库，根据datetime字段，在另一条具有相同值的记录的40秒内

如果两个条件都满足，则应删除除一个以外的所有情况。

Answer 1

正如@Juru 在评论中指出的那样，我们需要一把相当大的手术刀来切割这个。但是，可以通过存储过程以迭代方式执行此操作。

首先我们使用自连接来识别每条记录的第一个重复项，它本身不是重复项:

SELECT DISTINCT
  MIN(postdups.id AS id)
FROM wp_ttr_results AS base
INNER JOIN wp_ttr_results AS postdups
  ON base.id<postdups.id
  AND UNIX_TIMESTAMP(postdups.datetime)-UNIX_TIMESTAMP(base.datetime)<40
  AND base.user_id=postdups.user_id
  AND base.schoolyear=postdups.schoolyear
  AND base.area=postdups.area
  AND base.content=postdups.content
  AND base.types=postdups.types
  AND base.tasksWrong=postdups.tasksWrong
  AND base.tasksRight=postdups.tasksRight
  AND base.parent_id=postdups.user_id
LEFT JOIN wp_ttr_results AS predups
  ON base.id>predups.id
  AND UNIX_TIMESTAMP(base.datetime)-UNIX_TIMESTAMP(predups.datetime)<40
  AND base.user_id=predups.user_id
  AND base.schoolyear=predups.schoolyear
  AND base.area=predups.area
  AND base.content=predups.content
  AND base.types=predups.types
  AND base.tasksWrong=predups.tasksWrong
  AND base.tasksRight=predups.tasksRight
  AND base.parent_id=predups.user_id
WHERE predups.id IS NULL
GROUP BY base.id
;

这会选择最低的 id所有 later 记录 ( base.id<postdups.id )，它们与现有记录具有相同的有效负载并且在 40 秒的窗口内 (UNIX_TIMESTAMP(dups.datetime)-UNIX_TIMESTAMP(base.datetime)<40)，但跳过那些本身是重复的基本记录。在@Juru 的示例中，:30记录会被命中，因为它是 :00 的副本记录，它本身不是重复的，而是 :41记录不会被命中，因为它仅与 :30 重复, 它本身是 :00 的副本.

我们有

现在我们必须删除这条记录——因为 MySQL 不能从它正在读取的表中删除，我们必须使用一个变量来实现:

CREATE TEMPORARY TABLE cleanUpDuplicatesTemp SELECT DISTINCT 
  -- as above
;
DELETE FROM wp_ttr_results 
WHERE id IN
  (SELECT id FROM cleanUpDuplicatesTemp)
;
DROP TABLE cleanUpDuplicatesTemp
;

到现在为止，我们将删除每条记录的第一个重复项，在此过程中可能会发生变化，哪些会被视为重复项......

最后我们必须循环执行此过程，如果 SELECT DISTINCT 则退出循环什么都不返回。

将它们放在一个存储过程中:

DELIMITER ;;
CREATE PROCEDURE cleanUpDuplicates()
BEGIN
  DECLARE numDuplicates INT;
  iterate: LOOP
    DROP TABLE IF EXISTS cleanUpDuplicatesTemp;
    CREATE TEMPORARY TABLE cleanUpDuplicatesTemp
    SELECT DISTINCT
      MIN(postdups.id AS id)
    FROM wp_ttr_results AS base
    INNER JOIN wp_ttr_results AS postdups
      ON base.id<postdups.id
      AND UNIX_TIMESTAMP(postdups.datetime)-UNIX_TIMESTAMP(base.datetime)<40
      AND base.user_id=postdups.user_id
      AND base.schoolyear=postdups.schoolyear
      AND base.area=postdups.area
      AND base.content=postdups.content
      AND base.types=postdups.types
      AND base.tasksWrong=postdups.tasksWrong
      AND base.tasksRight=postdups.tasksRight
      AND base.parent_id=postdups.user_id
    LEFT JOIN wp_ttr_results AS predups
      ON base.id>predups.id
      AND UNIX_TIMESTAMP(base.datetime)-UNIX_TIMESTAMP(predups.datetime)<40
      AND base.user_id=predups.user_id
      AND base.schoolyear=predups.schoolyear
      AND base.area=predups.area
      AND base.content=predups.content
      AND base.types=predups.types
      AND base.tasksWrong=predups.tasksWrong
      AND base.tasksRight=predups.tasksRight
      AND base.parent_id=predups.user_id
    WHERE predups.id IS NULL
    GROUP BY base.id;
    SELECT COUNT(*) INTO numDuplicates FROM cleanUpDuplicatesTemp;
    IF numDuplicates<=0 THEN
      LEAVE iterate;
    END IF;
    DELETE FROM wp_ttr_results 
    WHERE id IN
      (SELECT id FROM cleanUpDuplicatesTemp)
  END LOOP iterate;
  DROP TABLE IF EXISTS cleanUpDuplicatesTemp;
END;;
DELIMITER ;

现在一个简单的CALL cleanUpDuplicates;应该可以解决问题。