Mysql -删除重复项

Question

我有一个表，它有一个带有唯一索引的条形码列。数据已经在每个条形码的末尾加载了额外的字符(-xx)，以防止重复，但一旦去掉后缀，就会有很多重复。以下是数据示例：

itemnumber  barcode

17912       2-14
18082       2-1
21870       2-10
29219       2-8

然后，我创建了两个临时表，marty和manny，这两个表都包含itemnumber和条形码。因此，两个表都将包含

itemnumber  barcode

17912       2
18082       2
21870       2
29219       2

等

I尝试删除marty表(以及所有其他条形码)中条形码为'2‘的第一个条目以外的所有条目。然后，我希望用正确的第一个条目更新原始表，用户可以在应用程序中及时修复副本。

因此，我的查询是删除每个条形码的marty表中除第一个条目之外的所有条目

DELETE FROM marty
  WHERE itemnumber NOT IN
    (SELECT MIN(itemnumber) FROM manny GROUP BY barcode)

在marty和manny中有13万行。查询耗时超过24小时，然后没有正常完成。与服务器的连接崩溃，查询未执行所有更新。

有没有更好的方法来避免使用子查询，我认为这是导致延迟的原因？而group by可能也会因为有这么多记录而放慢速度。

谢谢

Answer 1

另一个变体:这个变体不需要任何临时表就可以删除重复项：

 Delete m1
 From Marty m1
 join Marty m2 
    on m1.barcode = m2.barcode 
    and m1.itemnumber > m2.itemnumber

Answer 2

当对非常大的集合使用IN时，MySQL的速度是出了名的慢。一个脚本化的替代方案：

使用脚本构造一个长itemnumber = X OR itemnumber = y OR itemnumber = z子句(分块大小约为1000)，并将匹配的行(即在前面的查询中不是DELETEd的行) INSERT到一个新表中，TRUNCATE现有的行，并使用INSERT INTO marty SELECT * FROM marty_tmp将新表的内容加载回旧表。

您可能希望锁定表或在事务中运行以获得最终的TRUNCATE INSERT。

编辑：

• Query SELECT MIN(itemnumber) FROM manny GROUP BY barcode从脚本中，将结果存储在desiredItemNumbers数组中
• 获取1000个desiredItemNumbers的批次，并构造此查询：INSERT INTO manny_tmp SELECT * FROM manny WHERE itemnumber = desiredItemNumbers[0] OR itemnumber = desiredItemNumbers[1] ...。重新运行此查询，直到耗尽desiredItemNumbers数组(注意：最后一个查询可能只有不到1000个desiredItemNumbers).
• You，现在有了一个表，其中包含了如果您对其余的tables.
• TRUNCATE marty
• INSERT INTO marty SELECT * FROM marty_tmp

执行DELETEd操作时会得到的结果，因此请交换marty和marty_tmp so的内容

Answer 3

这里有一个避免使用NOT IN的两阶段方法。它也不使用临时表"manny“。首先，将"marty“连接到它自己，挑选出itemnumber != min(itemnumber)的行。使用UPDATE将这些行的barcode设置为NULL。然后，使用DELETE的第二次传递将删除在第一阶段中标记的所有行。

对于本例，我将"marty“的barcode列拆分为两列；这可以通过修改原始格式的表来完成(需要动态拆分列值)。

select * from marty;
+------------+---------+---------+
| itemnumber | barcode | subcode |
+------------+---------+---------+
|      17912 |       2 |      14 |
|      18082 |       2 |       1 |
|      21870 |       2 |      10 |
|      29219 |       2 |       8 |
|      30133 |       3 |       5 |
|      30134 |       3 |       7 |
|      30139 |       3 |       9 |
|      30142 |       3 |      12 |
+------------+---------+---------+
8 rows in set (0.00 sec)

UPDATE
  (marty m1
   JOIN
     (SELECT barcode,
             MIN(itemnumber) AS itemnumber
      FROM marty
      GROUP BY barcode) m2
   USING(barcode))
SET m1.barcode = NULL WHERE m1.itemnumber != m2.itemnumber;

mysql> select * from marty;
+------------+---------+---------+
| itemnumber | barcode | subcode |
+------------+---------+---------+
|      17912 |       2 |      14 |
|      18082 |    NULL |       1 |
|      21870 |    NULL |      10 |
|      29219 |    NULL |       8 |
|      30133 |       3 |       5 |
|      30134 |    NULL |       7 |
|      30139 |    NULL |       9 |
|      30142 |    NULL |      12 |
+------------+---------+---------+
8 rows in set (0.00 sec)

DELETE FROM marty WHERE barcode IS NULL;