获取Mysql重复行的ID以进行删除

Question

我正在尝试编写一个Laravel eloquent语句来执行以下操作。

查询一个表，并获取所有重复行的ID（最好是除了第一个重复实例的ID之外的所有ID）。

现在我有以下mysql语句：

select `codes`, count(`codes`) as `occurrences`, `customer_id` from `pizzas` 
group by `codes`, `customer_id` 
having `occurrences` > 1;

重复数据是指在codes和customer_id这两列的组合中，有任意行数据相同，例如：

codes,customer_id
183665A4,3
183665A4,3
183665A4,3
183665A4,3
183665A4,3

我试图仅保留其中一个，删除其他的。

这会返回代码的集合，包括它们的发生次数和客户ID，因为我只想要同时具有这两个条件的行。

目前我通过循环来实现，保存第一个实例的ID，然后再次调用并删除没有该ID的任何实例。由于我们需要对每个重复的实例进行多次查询以进行删除，因此这似乎不太快，因为有大约5000万行数据，每个查询都需要很长时间。

// get every order that shares the same code and customer ID
$orders = Order::select('id', 'codes', DB::raw('count(`codes`) as `occurrences`'), 'customer_id')
            ->groupBy('codes')
            ->groupBy('customer_id')
            ->having('occurrences', '>', 1)
            ->limit(100)
            ->get();

        // loop through those orders
        foreach ($orders as $order)
        {
            // find the first order that matches this duplicate set
            $first_order = Order::where('codes', $order->codes)
                                ->where('customer_id', $order->customer_id)
                                ->first();
            
            // delete all but the first        
            Order::where('codes', $order->codes)
                ->where('customer_id', $order->customer_id)
                ->where('id', '!=', $first_order->id)
                ->delete();
        }

有一种更有效的方法来跟踪所有具有相同代码和客户ID的行，并删除所有重复项但保留第一个实例，对吧？哈哈

我想也许如果我可以添加一个假列到结果中，该列是每个ID的数组，那么至少我可以删除第一个ID并删除其他ID。

- Octoxan

---

我不太确定你的意思或如何去做，@Popnoodles 抱歉 =( - 而且事实上，我不想删除重复项的第一个实例，只想删除重复项。 - Octoxan

什么是重复项？我很难想象你的数据。codes 包含什么？unique 逻辑是否是 codes 和 customer_id 的组合？也许您可以编辑您的问题，并包含来自数据库的一些示例行，以帮助我们更好地可视化它。 - Tim Lewis

@TimLewis 是的，这就是应该保持唯一的内容，因此不应存在多个customer_id/codes对。我也编辑了问题。我有大约3000万条记录，其中有大约200万需要被删除。 - Octoxan

“3000万条记录，其中约200万需要删除” - 哎呀...这让你希望在该表上有一个unique索引codes|customer_id，是吧？对于这么多的记录，我的方法与你的方法相同；分批处理，找到重复的数据对，找到第一个数据的id，然后运行一个单独的DELETE FROM pizzas WHERE id IN ?语句，其中?是ID列表减去第一个数据。该语句将运行X次，直到所有重复数据都被删除。然后，请务必添加一个唯一索引。 - Tim Lewis

哦，索引，不是键。我发誓在 Laravel 4 很久以前创建时没有办法为多个列的组合设置唯一性，但现在看起来有了。如果/当我完成清理几百万行时，知道这点很好。谢谢！ - Octoxan

Answer 1

不要涉及PHP

这似乎不是很快

问题中的逻辑本质上很慢，因为它有很多查询，并且对于每个查询，都有：

• DB<->PHP网络往返
• PHP ORM逻辑/开销

根据问题中的数字，整个代码需要调用多达10,000次（如果每个这2百万个重复记录都恰好有2个出现），假设有1k组重复项，则总共为：

• 1,000个查询查找重复项
• 100,000个查询查找第一个记录
• 100,000个删除查询

201,000个查询实在是太多了，而PHP开销使其变得慢了一个数量级（基于经验的猜测）。

直接在数据库上操作

仅仅消除php/orm/network的时间（即使在同一台机器上）也会使该过程明显加快，这将涉及编写一个过程来模拟问题中的php逻辑。

但是，还有一种更简单的方法，具体情况取决于情况。 在评论中，您说：

• 表的大小为140GB
• 它包含5000万行
• 大约有200万个重复记录
• 没有足够的空闲空间来制作表的副本

按面值接受这些评论，我建议的过程是：

确保您拥有功能性的数据库备份

在执行任何操作之前，请确保您拥有可用的数据库备份。如果您犯了一个错误，例如删除表-请确保您可以在不丢失数据的情况下进行恢复。

无论如何，您都将首先在数据库副本上测试此过程，对吧 :)?

创建一个“要保留的ID”表并填充它

这是使用唯一索引删除重复项的排列组合：

CREATE TABLE ids_to_keep (
    id INT PRIMARY KEY,
    codes VARCHAR(50) NOT NULL, # use same schema as source table
    customer_id INT NOT NULL, # use same schema as source table
    UNIQUE KEY derp (codes,customer_id)
);

INSERT IGNORE INTO ids_to_keep 
    SELECT id, codes, customer_id from pizzas;

Mysql会在唯一索引冲突时默默地删除行，导致表中每个codes+customer_id元组只有一个id。

如果您没有足够的空间来存储这个表 - 请腾出空间 :). 它不应该太大；140GB和50M行意味着每行约为3kb - 这个临时表可能只需要原始大小的几个百分比。

删除重复记录

在执行任何预计会很慢的查询之前，请使用EXPLAIN检查查询是否能在合理的时间内完成。

要作为单个查询运行：

DELETE FROM 
  pizzas 
WHERE 
  id NOT IN (SELECT id from ids_to_keep);

如果您希望分块处理任务：

DELETE FROM 
  pizzas 
WHERE 
  id BETWEEN (0,10000) AND
  id NOT IN (SELECT id from ids_to_keep);

清理

当表格不再需要时，请将其清除：

DROP TABLE ids_to_keep;

确保不再发生这种情况

为了防止再次发生此类情况，请在表中添加唯一索引：

CREATE UNIQUE INDEX ON pizzas(codes, customer_id);

- AD7six

Answer 2

试试这个，它将只保留重复和非重复的ID中最新的ID：

   $deleteDuplicates = DB::table('orders as ord1') 
  ->join('orders as ord2', 'ord1.codes', '<', 'ord2.codes') 
   ->where('ord1.codes', '=', 'ord2.codes') ->delete();

- Waqas Altaf

---

我已经有“几年”没有使用mysql了，那个查询是否有效（因为那里没有涉及php逻辑，只有查询会是更好的答案IMO :)）？我记得如果您尝试使用连接到同一表的联接进行删除，mysql会抱怨，并需要使用命名查询作为解决方法。我怀疑在问题中的数字生成临时表之前，此查询将死亡。 - AD7six