删除MySQL中的重复行 [英] Remove duplicate rows in MySQL
问题描述
company
site_id
现在,我需要删除行相同的标题,公司和site_id。一种方法是使用以下SQL以及脚本(PHP):
SELECT标题,site_id,位置, id,count(*)
pre>
FROM jobs
GROUP BY site_id,company,title,location
HAVING count(*)> 1
运行此查询后,我可以使用服务器端脚本删除重复项。
但是,我想知道这是否只能使用SQL查询。解决方案一个非常简单的方法这样做是在3列上添加一个
UNIQUE
索引。当您写入ALTER
语句时,请包含IGNORE
关键字。像这样:ALTER IGNORE TABLE作业
ADD UNIQUE INDEX idx_name(site_id,title,company);
这将删除所有重复的行。作为一个额外的好处,未来
INSERT
是重复的将会出错。一如往常,您可能需要在运行这样的事情之前进行备份...I have a table with the following fields:
id (Unique) url (Unique) title company site_id
Now, I need to remove rows having same title, company and site_id. One way to do it will be using the following SQL along with a script (PHP):
SELECT title, site_id, location, id, count( * ) FROM jobs GROUP BY site_id, company, title, location HAVING count( * ) >1
After running this query, I can remove duplicates using a server side script. But, I want to know if this can be done only using SQL query.
解决方案A really easy way to do this is to add a
UNIQUE
index on the 3 columns. When you write theALTER
statement, include theIGNORE
keyword. Like so:ALTER IGNORE TABLE jobs ADD UNIQUE INDEX idx_name (site_id, title, company);
This will drop all the duplicate rows. As an added benefit, future
INSERTs
that are duplicates will error out. As always, you may want to take a backup before running something like this...这篇关于删除MySQL中的重复行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!