我有一个表与以下字段:
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查询。
当前回答
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.id
其他回答
我想更具体地说明我删除了哪些记录，下面是我的解决方案:
delete
from jobs c1
where not c1.location = 'Paris'
and c1.site_id > 64218
and exists
(
select * from jobs c2
where c2.site_id = c1.site_id
and c2.company = c1.company
and c2.location = c1.location
and c2.title = c1.title
and c2.site_id > 63412
and c2.site_id < 64219
)
一种简单易懂且不需要主键的解决方案:
add a new boolean column alter table mytable add tokeep boolean; add a constraint on the duplicated columns AND the new column alter table mytable add constraint preventdupe unique (mycol1, mycol2, tokeep); set the boolean column to true. This will succeed only on one of the duplicated rows because of the new constraint update ignore mytable set tokeep = true; delete rows that have not been marked as tokeep delete from mytable where tokeep is null; drop the added column alter table mytable drop tokeep;
我建议您保留您添加的约束，以便将来防止出现新的重复。
如果不想更改列属性，那么可以使用下面的查询。
因为你有一个列，它有唯一的id(例如，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`;
这将删除标题、公司和网站值相同的重复行。最后一个重复项将被保留，其余重复项将被删除(如果你想保留第一个重复项并删除其他重复项，将id的比较项更改为大于t1。Id > t2.id)
DELETE t1 FROM tablename t1
INNER JOIN tablename t2
WHERE
t1.id < t2.id AND
t1.title = t2.title AND
t1.company=t2.company AND
t1.site_ID=t2.site_ID;
如果您试图删除其中一个副本并保留另一个副本，那么这是完美的。注意，如果没有子查询，您将得到#1093错误。
DELETE FROM table_name
WHERE id IN (
SELECT * FROM (SELECT n.id FROM table_name n
WHERE n.column2 != "value"
GROUP BY n.column HAVING COUNT(n.column ) > 1) x
)
