如何在两列而不是一列中查找重复项

Question

我有一个MySQL数据库表格，其中两列引起我的兴趣。它们各自可以有重复值，但是它们不应该同时具有相同值的重复项。

stone_id 可以有重复项，只要每个 upsharge 标题是不同的，反之亦然。但是举个例子，如果 stone_id = 412 并且 upcharge_title = "sapphire"，那么这种组合应该只出现一次。

这是可以的：

stone_id = 412 upcharge_title = "sapphire"
stone_id = 412 upcharge_title = "ruby"

这样做是不正确的：

stone_id = 412 upcharge_title = "sapphire"
stone_id = 412 upcharge_title = "sapphire"

有没有一种查询可以找到两个字段中的重复项？如果可能，有没有办法设置我的数据库不允许这样做？

我正在使用MySQL版本4.1.22

- John Isaacks

Answer 1

你应该在这两个字段之间设置一个复合键。这将要求每行都有唯一的stone_id和upcharge_title。

至于查找现有的重复项，请尝试以下方法：

select   stone_id,
         upcharge_title,
         count(*)
from     your_table
group by stone_id,
         upcharge_title
having   count(*) > 1

- Miyagi Coder

---

谢谢，那确实选择了它们。您能否告诉我如何删除重复项（但是留下1个副本）？非常感谢！ - JD Isaacks

一种方法是获取所有不同的数据并重新创建表格。 - Miyagi Coder

@John Isaacks：如果没有其他可以用来区分它们的字段（即所有字段都是重复的），那么您将不得不删除这两行并重新创建一行。一种方法是将重复项复制到表的副本中，从原始表中删除它们，并重新插入来自副本的不同行。 - P Daddy

这在Postgres 8.1上不起作用，有人能帮我吗？ - Lennon

不错！☺ 有没有办法也显示 id？在我的情况下，重复的是 first_name 和 last_name，但是 id 是不同的。在您输入的查询中，我可以清楚地看到那些重复的记录，但我只能看到其中一个 id。您如何使其显示其他 id？ - Pathros

Answer 2

我发现使用"ALTER IGNORE"添加唯一索引非常有用，它可以删除重复记录并强制要求唯一记录，这似乎是你想要做的。因此，语法将如下所示：

ALTER IGNORE TABLE `table` ADD UNIQUE INDEX(`id`, `another_id`, `one_more_id`);

这实际上添加了唯一约束，意味着您永远不会有重复记录，并且 IGNORE 删除了现有的重复项。

关于 ALTER IGNORE 的更多信息，请参阅此处：http://mediakey.dk/~cc/mysql-remove-duplicate-entries/

更新：我被 @Inquisitive 告知此方法可能在 MySql > 5.5 版本中失败：

在 MySQL > 5.5 中以及 InnoDB 表中和 Percona 中它因为 InnoDB 快速索引创建功能而失败 [http://bugs.mysql.com/bug.php?id=40344]。在这种情况下， 首先运行set session old_alter_table=1，然后执行上面的命令即可正常工作。

更新 - 5.7 版本移除了 ALTER IGNORE

来自文档：

从 MySQL 5.6.17 开始，IGNORE 子句已弃用，并且其使用会生成警告。IGNORE 在 MySQL 5.7 中已删除。

MySQL 开发人员提供了两个替代方案：

• 按唯一字段分组并按上述方法删除
• 创建一个新表，添加唯一索引，使用 INSERT IGNORE，例如：

CREATE TABLE duplicate_row_table LIKE regular_row_table;
ALTER TABLE duplicate_row_table ADD UNIQUE INDEX (id, another_id);
INSERT IGNORE INTO duplicate_row_table SELECT * FROM regular_row_table;
DROP TABLE regular_row_table;
RENAME TABLE duplicate_row_table TO regular_row_table;

但是根据你的表格大小，这可能不太实际。

- SeanDowney

---

没错，但至少下次你就知道了。我也遇到了同样的问题，觉得与其他人分享是个好主意。 - SeanDowney

我只是开玩笑说它晚了3年。真的很高兴你分享了。因此加上1。 - JD Isaacks

我想这会随意删除其中一个重复项，因此请确保每行之间没有不同的数据，这些数据可能是有用的或需要保留的。 - Joshua Pinter

即使回答晚了2年，也要点赞。我不小心删除了一个复合键，这真是救命稻草。谢谢。 - ivcode

感谢提供有用的信息。虽然我刚刚在phpMyAdmin中运行了它，但收到了以下警告。警告：#1681 'IGNORE'已被弃用，并将在未来版本中删除。 - TheWebsiteGuy

Answer 3

您可以像这样找到重复项...

Select
    stone_id, upcharge_title, count(*)
from 
    particulartable
group by 
    stone_id, upcharge_title
having 
    count(*) > 1

- Jason Punyon

Answer 4

查找重复项：

select stone_id, upcharge_title from tablename group by stone_id, upcharge_title having count(*)>1

为了避免这种情况发生，可以在这两个字段上创建一个复合唯一键来进行限制。

- Ian Nelson

---

非常感谢，能否告诉我如何删除除了一个副本之外的所有副本。还有在phpmyadmin中如何设置组合键。非常感谢！ - JD Isaacks

Answer 5

顺便说一下，对表格进行复合唯一约束将防止这种情况发生。

ALTER TABLE table
    ADD UNIQUE(stone_id, charge_title)

（这是有效的 T-SQL。不确定 MySQL 是否适用。）

- P Daddy

---

我认为这个方法是可行的，但在删除重复项之前，它不会让我执行。谢谢。 - JD Isaacks

Answer 6

这个stackoverflow的帖子对我有所帮助，但是我也想知道如何删除并且保留其中一行... 这是一个PHP解决方案，可以删除重复的行并保留一个（在我的情况下只有两列，并且它在清除重复类别关联的函数中）

$dupes = $db->query('select *, count(*) as NUM_DUPES from PRODUCT_CATEGORY_PRODUCT group by fkPRODUCT_CATEGORY_ID, fkPRODUCT_ID having count(*) > 1');
if (!is_array($dupes))
    return true;
foreach ($dupes as $dupe) {
    $db->query('delete from PRODUCT_CATEGORY_PRODUCT where fkPRODUCT_ID = ' . $dupe['fkPRODUCT_ID'] . ' and fkPRODUCT_CATEGORY_ID = ' . $dupe['fkPRODUCT_CATEGORY_ID'] . ' limit ' . ($dupe['NUM_DUPES'] - 1);
}

(限制 NUM_DUPES - 1) 是保留单行的关键...

谢谢大家

- groovenectar

---

ALTER IGNORE TABLE table ADD UNIQUE INDEX index_name(stone_id, charge_title)这个语句将会删除重复的行，只保留一个唯一的组合。 - dev-null-dweller

@dev-null-dweller - 你帮我省去了在C#中编程处理这个问题的数小时时间。非常感谢。- 已经工作 - MariaDB 10.5.16 - WLFree

Answer 7

这是对我有效的方法（忽略空值和空白）。两个不同的电子邮件列：

SELECT * 
FROM   members 
WHERE  email IN (SELECT soemail 
                 FROM   members 
                 WHERE  NOT Isnull(soemail) 
                        AND soemail <> ''); 

- Mark Gerrior