Mysql - 删除重复记录
我有一个带有唯一索引的条形码列表。数据已在每个条形码的末尾添加了额外的字符(-xx)以防止出现重复,但是一旦删除后缀,将会出现大量重复项。下面是数据的一个示例:Mysql - 删除重复记录
itemnumber barcode
17912 2-14
18082 2-1
21870 2-10
29219 2-8
然后我创建了两个临时表,玛蒂和曼尼,既与itemnumber和剥离下来条形码。所以,这两个表将包含
itemnumber barcode
17912 2
18082 2
21870 2
29219 2
等
而且我试图删除所有,但在马蒂表条形码中的第一项“2”(和所有其他条形码)。我希望然后用正确的第一项更新原始表,用户可以在应用程序中及时修复重复项。
所以,这是我的查询,删除所有,但在马蒂表中的每个条形码
DELETE FROM marty
WHERE itemnumber NOT IN
(SELECT MIN(itemnumber) FROM manny GROUP BY barcode)
中的第一项有13万行的马蒂和曼尼。该查询花了24小时,然后没有正确完成。与服务器的连接崩溃,并且查询没有执行所有更新。
有没有更好的方式来处理这一点,就不是我们的子查询,我认为这是造成延误?这个团队可能会放慢速度,因为有这么多的记录。
感谢
MySQL的使用IN
非常大集时是出了名的慢。一个脚本替代:
使用脚本来构建一个长itemnumber = X OR itemnumber = y OR itemnumber = z
条(块大小〜1000)和INSERT
匹配的行(即不会在你以前的查询已经DELETE
d的那些)到新表,TRUNCATE
现有并将新表格的内容加载回旧版本INSERT INTO marty SELECT * FROM marty_tmp
。
您可能希望锁定表或在交易的最后TRUNCATE
,INSERT
运行。
编辑:
- 查询
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数组为止(n.b.最后一个查询的可能少于1000个desiredItemNumbers)。 - 您现在有一个表格,其结果是您将剩下的结果为
DELETE
d,因此请交换marty
和marty_tmp
表格的内容。 TRUNCATE marty
INSERT INTO marty SELECT * FROM marty_tmp
嗨,安迪,我不太相信“构建一个很长的........”我仍然需要确定重复条形码列表的第一个itemnumber。那是我遇到问题的地方,我知道实现这一目标的唯一方法是使用select in和group by,这太慢了。 MartinMac – 2012-03-20 12:23:04
@Martin更新了答案 – Andy 2012-03-20 13:58:49
如果要创建临时表反正,怎么样用 “INSERT INTO” 建立你的表或“CREATE TABLE .. AS ...“基于:
SELECT MIN(itemnumber) AS itemnumber, barcode
FROM marty
GROUP BY barcode
Glenn,太棒了。不错,简单,速度非常快,而且运行良好。我只需要使用substr(条形码,1,长度(条形码))去除已使用的后缀。太感谢了。 – 2012-03-20 15:44:12
这里是一个两阶段的办法,避免了使用NOT IN
它也不会使用临时表。‘曼尼’首先,加盟。‘马蒂’给自己挑选出的行itemnumber!=分钟(itemnumber)。使用UPDATE
设置barcode
为这些行NULL
。第二遍用DELETE
然后删除在第一阶段中被标记的所有行。
对于这个例子,我分裂的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;
一个多种变异:这个变体工作没有任何临时表删除重复:
Delete m1
From Marty m1
join Marty m2
on m1.barcode = m2.barcode
and m1.itemnumber > m2.itemnumber
是'itemnumber'独特之处?如果是的话,我认为你的方法最终会奏效,尽管它效率低下,因为它搜索整个“曼尼”表中的每一行“烈士”。 – gcbenison 2012-03-20 12:44:39