仅使用MySQL查询删除重复项? [英] Remove duplicates using only a MySQL query?

问题描述

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

I have a table with the following columns:

URL_ID    
URL_ADDR    
URL_Time

我想使用MySQL查询删除URL_ADDR列上的重复项.

I want to remove duplicates on the URL_ADDR column using a MySQL query.

是否可以在不使用任何编程的情况下做这样的事情?

Is it possible to do such a thing without using any programming?

推荐答案

考虑以下测试案例:

CREATE TABLE mytb (url_id int, url_addr varchar(100));

INSERT INTO mytb VALUES (1, 'www.google.com');
INSERT INTO mytb VALUES (2, 'www.microsoft.com');
INSERT INTO mytb VALUES (3, 'www.apple.com');
INSERT INTO mytb VALUES (4, 'www.google.com');
INSERT INTO mytb VALUES (5, 'www.cnn.com');
INSERT INTO mytb VALUES (6, 'www.apple.com');

我们的测试表现在包含:

Where our test table now contains:

SELECT * FROM mytb;
+--------+-------------------+
| url_id | url_addr          |
+--------+-------------------+
|      1 | www.google.com    |
|      2 | www.microsoft.com |
|      3 | www.apple.com     |
|      4 | www.google.com    |
|      5 | www.cnn.com       |
|      6 | www.apple.com     |
+--------+-------------------+
5 rows in set (0.00 sec)

然后，我们可以使用多表 DELETE 语法如下:

Then we can use the multiple-table DELETE syntax as follows:

DELETE t2
FROM   mytb t1
JOIN   mytb t2 ON (t2.url_addr = t1.url_addr AND t2.url_id > t1.url_id);

...将删除重复的条目，仅保留基于url_id的第一个网址:

... which will delete duplicate entries, leaving only the first url based on url_id:

SELECT * FROM mytb;
+--------+-------------------+
| url_id | url_addr          |
+--------+-------------------+
|      1 | www.google.com    |
|      2 | www.microsoft.com |
|      3 | www.apple.com     |
|      5 | www.cnn.com       |
+--------+-------------------+
3 rows in set (0.00 sec)

---

更新-除了上面的新评论:

---

UPDATE - Further to new comments above:

如果重复的URL不会具有相同的格式，则您可能需要应用

If the duplicate URLs will not have the same format, you may want to apply the REPLACE() function to remove www. or http:// parts. For example:

DELETE t2
FROM   mytb t1
JOIN   mytb t2 ON (REPLACE(t2.url_addr, 'www.', '') = 
                   REPLACE(t1.url_addr, 'www.', '') AND 
                   t2.url_id > t1.url_id);

这篇关于仅使用MySQL查询删除重复项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！