Mysql - 防止具有唯一索引的组合列的重复条目 [英] Mysql - Preventing duplicate entries of combined columns with Unique Index

问题描述

我在数据库中遇到重复条目的问题，由于问题的性质，解决该问题的最简单方法是删除当前的重复行并防止添加更多重复项.

I have an issue with duplicate entries in a database and due to the nature of the problem the easiest way to fix it would be to remove current duplicate rows and prevent further duplicates from being added .

这是表结构:

| a     | b      |  c | 

 user    url1      token1
 photo   url1      token2
 action  action1   token3 
 user    url1      token4   
 photo   url1      token5
 action  action2   token6 

我只想在 2 列重复时防止重复条目，在本例中为 a 和 b .

I want to prevent duplicate entries only when 2 columns are duplicated, in this case a and b .

所以这里我们有用户 |url1 和照片 |url1 重复了两次.

So here we have user | url1 and photo | url1 duplicated twice.

当两列同时匹配另一行时，我想防止添加任何进一步的重复项，但到目前为止我发现的查询将分别考虑每一列，并防止向其中任何列添加任何进一步的重复项.

I want to prevent any further duplicates from being added when both columns match another row at same time but the queries I found so far will consider each column separately and preventing any further duplicates to be added to any of them .

我可以通过使用唯一索引的 mysql 查询来实现这一点吗?

Can I achieve this with a mysql query using unique index ?

我尝试使用以下代码:

Using ALTER TABLE `targets` ADD UNIQUE (
`a` ,
`b`
);

推荐答案

感谢各位的回复，但我在此期间找到了解决方案，而且简单多了！

Thanks for the replies guys, but I found the solution in the meantime and it was much simpler !

它被称为唯一的复合键，它允许做我想做的事情:

It's called unique composite key and it allows to do exactly what I wanted :

ALTER TABLE targets ADD UNIQUE KEY `uidx` (a, b, c);

问题已解决:)

这篇关于Mysql - 防止具有唯一索引的组合列的重复条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！