MySQL使用主键删除重复行

Question

我有一张表格，其中包含以下列：

ID(primary key),
USER,
ACTION
TIME
LOCATION

我正在尝试使用列USER，ACTION，TIME，LOCATION一起删除重复条目。

我编写了以下查询：

DELETE FROM test.testlogins  
WHERE id IN (SELECT * 
             FROM (SELECT id FROM test.testlogins 
                   GROUP BY USER, ACTION, TIME, LOCATION HAVING (COUNT(*) > 1)
                  ) AS A
            );

然而，当我执行它时，每次只删除一行。我的测试数据有大约40多行是重复的，每个都分配了一个单独的id。

- silverzpeed

Answer 1

DELETE t1.*
FROM
  testlogins t1 INNER JOIN testlogins t2
  ON t1.user=t2.user
     AND t1.action=t2.action
     AND t1.time=t2.time
     AND t2.location=t2.location
     AND t1.id>t2.id

如果你想保留ID最小的行，可以使用t1.id>t2.id，如果你想保留ID最大的行，则可以使用t1.id<t2.id。

- fthiella

---

这个代码按照预期工作（删除了多个重复项），但我也想尝试将其用作触发器，以防止添加重复项。您有什么想法可以避免添加重复项吗？ - silverzpeed

@silverzpeed，你应该使用CREATE UNIQUE INDEX idx_userlogins ON testlogins (USER, ACTION, TIME, LOCATION)创建一个唯一索引。 - fthiella

我在一个远程服务器上有一个脚本，该脚本构建一个插入脚本并执行插入操作。如果日志中看到了重复的错误输出，这会影响插入吗？ - silverzpeed

@silverzpeed 请查看我的另一个答案。是的，如果有唯一索引，插入重复数据时会出现错误...您可以使用“INSERT .. ON DUPLICATE ...”语法来忽略重复插入而不会引发错误。 - fthiella

Answer 2

最简单的解决方法是使用ALTER IGNORE为表添加唯一索引。如果表大小不是很大，这将避免以后出现问题。

ALTER IGNORE TABLE testlogins ADD UNIQUE KEY (USER, ACTION, TIME, LOCATION)

或者

在其他数据库中创建一张新表，并使用新的唯一索引将所有数据加载到新表中，使用INSERT IGNORE命令。

- georgecj11

Answer 3

另一种编写此查询的方法是：

DELETE tl
    FROM test.testlogins tl LEFT JOIN
         (SELECT MIN(id) as id
          FROM test.testlogins 
          GROUP BY USER, ACTION, TIME, LOCATION
         ) tokeep
         ON tl.id = tokeep.minid
    WHERE tokeep.id IS NULL;

假设你每次只删除一个id的原因是因为你语句中的group by只返回一个id，而这就是你要删除的那个。如果某个列的组合出现了40次，那么你每次delete只会删除这个组中的一个id。

另一方面，这种方法会找到要保留的行（任意选择最小的id），然后删除其他所有行。

- Gordon Linoff

Answer 4

DELETE FROM testlogins  
WHERE EXISTS ( SELECT 'a'
               FROM testlogins t2
               WHERE t2.USER = testlogins.USER
               AND t2.ACTION= testlogins.ACTION
               AND t2.TIME = testlogins.TIME 
               AND t2.LOCATION = testlogins.LOCATION 
               AND t2.ID > testlogins.ID
              )

删除所有具有相同属性且ID低于最大值的行

- Javaluca

---

这在MySQL中不起作用，因为子查询无法引用正在被删除行的表。 - Gordon Linoff