我有一个表与以下字段:
id (Unique)
url (Unique)
title
company
site_id
现在,我需要删除具有相同标题、company和site_id的行。一种方法是使用下面的SQL和脚本(PHP):
SELECT title, site_id, location, id, count( * )
FROM jobs
GROUP BY site_id, company, title, location
HAVING count( * ) >1
运行此查询后,可以使用服务器端脚本删除重复项。
但是,我想知道这是否只能使用SQL查询。
要做到这一点,一个非常简单的方法是在3列上添加UNIQUE索引。在编写ALTER语句时,请包含IGNORE关键字。像这样:
ALTER IGNORE TABLE jobs
ADD UNIQUE INDEX idx_name (site_id, title, company);
这将删除所有重复的行。作为一个额外的好处,将来重复的insert将出错。像往常一样,在运行这样的程序之前,您可能想要进行备份…
编辑:不再工作在MySQL 5.7+
这个特性在MySQL 5.6中已经被弃用,在MySQL 5.7中被移除,所以它不起作用。
从8.0版(2018)开始,MySQL终于支持窗口函数了。
窗口函数既方便又高效。下面是一个演示如何使用它们来解决这个任务的解决方案。
在子查询中,可以使用ROW_NUMBER()为表中columnn1 /column2组中的每条记录分配位置,按id排序。如果没有重复项,记录将获得第1行。如果存在副本,它们将按升序编号(从1开始)。
一旦子查询中的记录正确编号,外层查询只删除行号不是1的所有记录。
查询:
DELETE FROM tablename
WHERE id IN (
SELECT id
FROM (
SELECT
id,
ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY id) rn
FROM output
) t
WHERE rn > 1
)