我有一个表与以下字段:
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，我花了几个小时从一个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;
其他回答
下面是我用过的方法，它很有效:
create table temp_table like my_table;
T_id是唯一的列
insert into temp_table (id) select id from my_table GROUP by t_id;
delete from my_table where id not in (select id from temp_table);
drop table temp_table;
要做到这一点，一个非常简单的方法是在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中被移除，所以它不起作用。
如果IGNORE语句在我的例子中不起作用，你可以使用下面的语句:
CREATE TABLE your_table_deduped LIKE your_table;
INSERT your_table_deduped
SELECT *
FROM your_table
GROUP BY index1_id,
index2_id;
RENAME TABLE your_table TO your_table_with_dupes;
RENAME TABLE your_table_deduped TO your_table;
#OPTIONAL
ALTER TABLE `your_table` ADD UNIQUE `unique_index` (`index1_id`, `index2_id`);
#OPTIONAL
DROP TABLE your_table_with_dupes;
如果你有一个有大量记录的大表，那么上述解决方案将不起作用或花费太多时间。然后我们有一个不同的解
-- Create temporary table
CREATE TABLE temp_table LIKE table1;
-- Add constraint
ALTER TABLE temp_table ADD UNIQUE(title, company,site_id);
-- Copy data
INSERT IGNORE INTO temp_table SELECT * FROM table1;
-- Rename and drop
RENAME TABLE table1 TO old_table1, temp_table TO table1;
DROP TABLE old_table1;
一种简单易懂且不需要主键的解决方案:
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;
我建议您保留您添加的约束，以便将来防止出现新的重复。
