如何在2列中找到副本而不是1 ?

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数据库表，其中有两列我感兴趣。它们各自都可以有副本，但是它们不应该有两个具有相同值的副本。

stone_id can have duplicates as long as for each upsharge title is different, and in reverse. But say for example stone_id = 412 and upcharge_title = "sapphire" that combination should only occur once.

如果每个upsharge标题都是不同的，那么stone_id可以有副本，反之亦然。但是，例如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。

6 个解决方案

#1

148

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

#2

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:

我发现添加一个unqiue索引是很有帮助的，它使用一个“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.

这有效地增加了唯一的约束，意味着您永远不会有重复的记录，而忽略将删除现有的重复记录。

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 :

更新:@查询者通知我，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=1 and then the above command will work fine

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

#3

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.

为了将来避免这种情况，要在这两个字段上创建一个复合惟一键。

#4

You can find duplicates like this..

你可以找到这样的复制品。

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

#5

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

#6

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)

这篇文章对我有帮助，但我也想知道如何删除和保留其中的一行……这里有一个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);
}

the (limit NUM_DUPES - 1) is what preserves the single row...

(limit NUM_DUPES - 1)是保持单行…

thanks all

感谢所有

#1

148