MySQL 如何在 2 列而不是 1 中找到重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/642656/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find duplicates in 2 columns not 1
提问by JD Isaacks
I have a MySQL database table with two columns that interest me. Individually they can each have duplicates, but they should never have a duplicate of BOTH of them having the same value.
我有一个包含我感兴趣的两列的 MySQL 数据库表。单独地，它们每个都可以有重复项，但它们绝不应该有具有相同值的 BOTH 的重复项。
stone_idcan have duplicates as long as for each upshargetitle is different, and in reverse. But say for example stone_id= 412 and upcharge_title= "sapphire" that combination should only occur once.
stone_id只要每个upsharge标题不同，就可以有重复，反之亦然。但是说例如stone_id= 412 和upcharge_title= "sapphire" 组合应该只出现一次。
This is ok:
还行吧：
stone_id = 412 upcharge_title = "sapphire"
stone_id = 412 upcharge_title = "ruby"
This is NOT ok:
这不行：
stone_id = 412 upcharge_title = "sapphire"
stone_id = 412 upcharge_title = "sapphire"
Is there a query that will find duplicates in both fields? And if possible is there a way to set my data-base to not allow that?
是否有一个查询会在两个字段中找到重复项？如果可能的话，有没有办法将我的数据库设置为不允许这样做？
I am using MySQL version 4.1.22
我使用的是 MySQL 版本 4.1.22
回答by Miyagi Coder
You should set up a composite key between the two fields. This will require a unique stone_id and upcharge_title for each row.
您应该在两个字段之间设置一个复合键。这将需要每行唯一的 stone_id 和 upcharge_title。
As far as finding the existing duplicates try this:
至于找到现有的重复尝试这个：
select stone_id,
upcharge_title,
count(*)
from your_table
group by stone_id,
upcharge_title
having count(*) > 1
回答by SeanDowney
I found it helpful to add a unqiue index using an "ALTER IGNORE" which removes the duplicates and enforces unique records which sounds like you would like to do. So the syntax would be:
我发现使用“ALTER IGNORE”添加唯一索引很有帮助，该索引删除重复项并强制执行听起来像您想要的唯一记录。所以语法是：
ALTER IGNORE TABLE `table` ADD UNIQUE INDEX(`id`, `another_id`, `one_more_id`);
This effectively adds the unique constraint meaning you will never have duplicate records and the IGNORE deletes the existing duplicates.
这有效地添加了唯一约束，这意味着您永远不会有重复的记录，并且 IGNORE 会删除现有的重复项。
You can read more about eh ALTER IGNORE here: http://mediakey.dk/~cc/mysql-remove-duplicate-entries/
您可以在此处阅读有关 eh ALTER IGNORE 的更多信息：http: //mediakey.dk/~cc/mysql-remove-duplicate-entries/
Update: I was informed by @Inquisitive that this may fail in versions of MySql> 5.5 :
更新：@Inquisitive 告知我这可能会在 MySql> 5.5 版本中失败：
It fails On MySQL > 5.5 and on InnoDB table, and in Percona because of their InnoDB fast index creation feature [http://bugs.mysql.com/bug.php?id=40344]. In this case first run
set session old_alter_table=1and then the above command will work fine
它在 MySQL > 5.5 和 InnoDB 表以及 Percona 上失败，因为它们的 InnoDB 快速索引创建功能 [ http://bugs.mysql.com/bug.php?id=40344]。在这种情况下首先运行
set session old_alter_table=1，然后上面的命令将正常工作
Update - ALTER IGNORERemoved In 5.7
更新 -ALTER IGNORE在 5.7 中删除
From the docs
从文档
As of MySQL 5.6.17, the IGNORE clause is deprecated and its use generates a warning. IGNORE is removed in MySQL 5.7.
从 MySQL 5.6.17 开始，不推荐使用 IGNORE 子句，并且使用它会生成警告。MySQL 5.7 中删除了 IGNORE。
One of the MySQL dev's give two alternatives:
MySQL 开发人员之一提供了两种选择：
- Group by the unique fields and delete as seen above
- Create a new table, add a unique index, use
INSERT IGNORE, ex:
- 按唯一字段分组并删除，如上所示
- 创建一个新表，添加唯一索引，使用
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;
But depending on the size of your table, this may not be practical
但是根据你的桌子的大小，这可能不切实际
回答by Jason Punyon
You can find duplicates like this..
你可以找到这样的重复项..
Select
stone_id, upcharge_title, count(*)
from
particulartable
group by
stone_id, upcharge_title
having
count(*) > 1
回答by Ian Nelson
To find the duplicates:
要查找重复项：
select stone_id, upcharge_title from tablename group by stone_id, upcharge_title having count(*)>1
To constrain to avoid this in future, create a composite unique key on these two fields.
为了限制将来避免这种情况，请在这两个字段上创建一个复合唯一键。
回答by P Daddy
Incidentally, a composite unique constraint on the table would prevent this from occurring in the first place.
顺便说一句，表上的复合唯一约束首先会防止这种情况发生。
ALTER TABLE table
ADD UNIQUE(stone_id, charge_title)
(This is valid T-SQL. Not sure about MySQL.)
（这是有效的 T-SQL。不确定 MySQL。）
回答by groovenectar
this SO post helped me, but i too wanted to know how to delete and keep one of the rows... here's a PHP solution to delete the duplicate rows and keep one (in my case there were only 2 columns and it is in a function for clearing duplicate category associations)
这篇 SO 帖子对我有帮助，但我也想知道如何删除和保留其中一行......这是一个删除重复行并保留一个的 PHP 解决方案（在我的情况下只有 2 列，它位于清除重复类别关联的功能）
$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);
}
the (limit NUM_DUPES - 1) is what preserves the single row...
(limit NUM_DUPES - 1) 是保留单行的...
thanks all
谢谢大家
