我有一张有以下字段的表格:
id (Unique)
url (Unique)
title
company
site_id现在,我需要删除具有相同title, company and site_id的行。一种方法是使用下面的SQL和脚本(PHP):
SELECT title, site_id, location, id, count( * )
FROM jobs
GROUP BY site_id, company, title, location
HAVING count( * ) >1运行此查询后,可以使用服务器端脚本删除重复项。
但是,我想知道这是否只能使用SQL查询。
发布于 2010-07-22 18:24:05
一个非常简单的方法是在3列上添加一个UNIQUE索引。编写ALTER语句时,请包含IGNORE关键字。就像这样:
ALTER IGNORE TABLE jobs
ADD UNIQUE INDEX idx_name (site_id, title, company);这将删除所有重复行。作为一个额外的好处,将来重复的INSERTs会出错。像往常一样,在运行这样的东西之前你可能想做个备份.
发布于 2014-08-08 15:20:38
如果不想更改列属性,则可以使用下面的查询。
由于您有一个具有唯一it的列(例如,auto_increment列),所以可以使用它删除重复项:
DELETE `a`
FROM
`jobs` AS `a`,
`jobs` AS `b`
WHERE
-- IMPORTANT: Ensures one version remains
-- Change "ID" to your unique column's name
`a`.`ID` < `b`.`ID`
-- Any duplicates you want to check for
AND (`a`.`title` = `b`.`title` OR `a`.`title` IS NULL AND `b`.`title` IS NULL)
AND (`a`.`company` = `b`.`company` OR `a`.`company` IS NULL AND `b`.`company` IS NULL)
AND (`a`.`site_id` = `b`.`site_id` OR `a`.`site_id` IS NULL AND `b`.`site_id` IS NULL);在MySQL中,您可以使用零安全等运算符 (又名“宇宙飞船操作员”)进一步简化它:
DELETE `a`
FROM
`jobs` AS `a`,
`jobs` AS `b`
WHERE
-- IMPORTANT: Ensures one version remains
-- Change "ID" to your unique column's name
`a`.`ID` < `b`.`ID`
-- Any duplicates you want to check for
AND `a`.`title` <=> `b`.`title`
AND `a`.`company` <=> `b`.`company`
AND `a`.`site_id` <=> `b`.`site_id`;发布于 2010-07-22 18:26:48
MySQL对引用要删除的表有限制。您可以使用临时表来解决这个问题,比如:
create temporary table tmpTable (id int);
insert into tmpTable
(id)
select id
from YourTable yt
where exists
(
select *
from YourTabe yt2
where yt2.title = yt.title
and yt2.company = yt.company
and yt2.site_id = yt.site_id
and yt2.id > yt.id
);
delete
from YourTable
where ID in (select id from tmpTable);来自Kostanos的评论中的建议:
上面唯一缓慢的查询是DELETE,如果您有一个非常大的数据库的话。这个查询可能会更快:
DELETE FROM YourTable USING YourTable, tmpTable WHERE YourTable.id=tmpTable.idhttps://stackoverflow.com/questions/3311903
复制相似问题