我有一个表与以下字段:
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查询。
当前回答
我找到了一个简单的方法。(保持最新的)
DELETE t1 FROM table_name t1 INNER JOIN table_name t2
WHERE t1.primary_id < t2.primary_id
AND t1.check_duplicate_col_1 = t2.check_duplicate_col_1
AND t1.check_duplicate_col_2 = t2.check_duplicate_col_2
...
其他回答
删除表中重复的记录。
delete from job s
where rowid < any
(select rowid from job k
where s.site_id = k.site_id and
s.title = k.title and
s.company = k.company);
or
delete from job s
where rowid not in
(select max(rowid) from job k
where s.site_id = k.site_id and
s.title = k.title and
s.company = k.company);
如果您试图删除其中一个副本并保留另一个副本，那么这是完美的。注意，如果没有子查询，您将得到#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
)
更快的方法是在临时表中插入不同的行。使用delete，我花了几个小时从一个800万行的表中删除重复项。使用insert和distinct，只花了13分钟。
CREATE TABLE tempTableName LIKE tableName;
CREATE INDEX ix_all_id ON tableName(cellId,attributeId,entityRowId,value);
INSERT INTO tempTableName(cellId,attributeId,entityRowId,value) SELECT DISTINCT cellId,attributeId,entityRowId,value FROM tableName;
TRUNCATE TABLE tableName;
INSERT INTO tableName SELECT * FROM tempTableName;
DROP TABLE tempTableName;
使用Delete JOIN语句删除重复的行:
DELETE t1 FROM table_name t1
JOIN table_name t2
WHERE
t1.id < t2.id AND
t1.title = t2.title AND t1.company = t2.company AND t1.site_id = t2.site_id;
一种简单易懂且不需要主键的解决方案:
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;
我建议您保留您添加的约束，以便将来防止出现新的重复。
