如何从MySQL表中删除重复的行 [英] How to delete duplicate rows from a MySQL table

问题描述

我有一个MySQL表，如:

ID, Col1, Col2, Col3, Col4, etc...

ID是 primary key ，自创建表以来一直有效.

ID is a primary key and has been working since the table's creation.

我想做的是删除除一条记录以外的所有记录，其中所有其他列都相同.

What I want to do is delete all but one records where all the other columns are identical.

推荐答案

DELETE DupRows.*
FROM MyTable AS DupRows
   INNER JOIN (
      SELECT MIN(ID) AS minId, col1, col2
      FROM MyTable
      GROUP BY col1, col2
      HAVING COUNT(*) > 1
   ) AS SaveRows ON SaveRows.col1 = DupRows.col1 AND SaveRows.col2 = DupRows.col2
      AND SaveRows.minId <> DupRows.ID;

当然，您必须在所有三个位置将col1，col2扩展到所有列.

Of course you have to extend col1, col2 in all three places to all columns.

我只是从保留的脚本中删除了该脚本，然后对其进行了重新测试，该脚本在MySQL中执行.

I just pulled this out of a script I keep and re-tested, it executes in MySQL.

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