仅使用MySQL查询如何删除重复项？

Question

仅使用MySQL查询如何删除重复项？

18

我有一个包含以下列的表：

URL_ID    
URL_ADDR    
URL_Time

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

是否可能在不使用任何编程的情况下完成此操作？

- Jim

11

SQL是一种编程语言，用于管理关系型数据库。 - OMG Ponies

URL_ADDR中的URL是什么样子的？它们都有相同的格式吗？例如：带或不带www。 - Joshua Partogi

8个回答

9

您可能想尝试在http://labs.creativecommons.org/2010/01/12/removing-duplicate-rows-in-mysql/提到的方法。

ALTER IGNORE TABLE your_table ADD UNIQUE INDEX `tmp_index` (URL_ADDR);

- Box

1

自MySQL 5.7.4起，不再支持使用“ALTER IGNORE”命令。：https://dev59.com/p2035IYBdhLWcg3wYO51#5456599 - J.D.

5

这将只保留特定的URL_ADDR中具有最高URL_ID的那些记录。

DELETE FROM table
WHERE URL_ID NOT IN 
    (SELECT ID FROM 
       (SELECT MAX(URL_ID) AS ID 
        FROM table 
        WHERE URL_ID IS NOT NULL
        GROUP BY URL_ADDR ) X)   /*Sounds like you would need to GROUP BY a 
                                   calculated form - e.g. using REPLACE to 
                                  strip out www see Daniel's answer*/

(衍生表'X'旨在避免错误“无法在FROM子句中更新目标表'tablename'”)

- Martin Smith

@Vilx：这就是为什么你要将它埋在另一个子查询中的原因。 - OMG Ponies

4

好的，你可以这样做：

创建一个临时表；
INSERT INTO ... SELECT DISTINCT 从原始表中选择并插入到临时表中；
清空原始表；
INSERT INTO ... SELECT 从临时表中选择并插入到原始表中；
删除临时表。

这种方法有些笨拙和麻烦，并需要多次查询（更不用说特权），但如果你找不到其他解决方案，它会起到作用。

- Vilx-

2

您可以按照URL_ADDR进行分组，这将有效地为您提供URL_ADDR字段中的唯一值。

select 
 URL_ID
 URL_ADDR
 URL_Time
from
 some_table
group by
 URL_ADDR

祝愉快！

- Doug

1

这正是我正在寻找的！ - Paulo Guimarães

1

 DELETE FROM `your_table`
 WHERE 
    `unique_id` IN (
    SELECT 
        `unique_id`
    FROM (
        SELECT 
            `unique_id_in_table`,
            ROW_NUMBER() OVER (
                PARTITION BY `group_by_column`
                ORDER BY `group_by_column`) AS row_num
        FROM 
            `your_table`
    ) t
     WHERE row_num > 1
 );

其中：

your_table是表名
group_by_column是你想要唯一的列
unique_id_in_table是每行应该唯一的ID

- Roman Losev

1

如何为多列创建Daniel Vassallo指南？

DELETE t2 FROM directory1 t1 JOIN directory1 t2 ON (t2.page = t1.page, t2.parentTopic = t1.parentTopic, t2.title = t1.title, t2.description = t1.description, t2.linktype = t1.linktype, t2.priority = t1.priority AND t2.linkID > t1.linkID);

也许是这样的吗？

- Olariu Romeo Vicentiu

0

只要你的URL_ID列是唯一的，这个代码就可以运行。

DELETE FROM url WHERE URL_ID IN (
SELECT URL_ID
FROM url a INNER JOIN (
    SELECT URL_ADDR, MAX(URL_ID) MaxURLId 
    FROM url
    GROUP BY URL_ADDR
    HAVING COUNT(*) > 1) b ON a.URL_ID <> b.MaxURLId AND a.URL_ADDR = b.URL_ADDR
)

- Tahbaza

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Daniel Vassallo · Accepted Answer

考虑以下测试用例：

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');

我们的测试表现在包含：

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 语法，如下所示：

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

... 这将删除重复条目，仅基于 url_id 保留第一个 URL：

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)

更新 - 基于上面的新评论：

如果重复的URL不具有相同的格式，您可能希望应用REPLACE()函数来删除www。或http://部分。例如：

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);