删除 MySQL 中的重复行

2021-11-20 00:00:00 sql duplicates mysql

我有一个包含以下字段的表格:

I have a table with the following fields:

id (Unique) url (Unique) title company site_id

现在，我需要删除具有相同 title、company 和 site_id 的行.一种方法是使用以下 SQL 和脚本 (PHP):

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.

但是，我想知道这是否可以仅使用 SQL 查询来完成.

But, I want to know if this can be done only using SQL query.

推荐答案

一个非常简单的方法是在 3 列上添加一个 UNIQUE 索引.在编写 ALTER 语句时，请包含 IGNORE 关键字.像这样:

A really easy way to do this is to add a UNIQUE index on the 3 columns. When you write the ALTER statement, include the IGNORE keyword. Like so:

ALTER IGNORE TABLE jobs ADD UNIQUE INDEX idx_name (site_id, title, company);

这将删除所有重复的行.作为额外的好处，以后重复的 INSERT 会出错.与往常一样，您可能希望在运行类似操作之前进行备份...

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...

相关文章