問題描述
我有一個包含以下字段的表格:
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...
這篇關于刪除 MySQL 中的重復行的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!