问题描述
我有一个 MySQL 数据库表，其中包含我感兴趣的两列.单独地，他们每个人都可以有重复项，但他们永远不应该有两个具有相同值的重复项.
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.
stone_id 可以有重复，只要每个 upsharge 的标题不同，并且相反.但是例如 stone_id = 412 和 upcharge_title = "sapphire" 组合应该只出现一次.
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.
没关系:
stone_id = 412 upcharge_title = "sapphire"
stone_id = 412 upcharge_title = "ruby"
这不行:
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?
我使用的是 MySQL 4.1.22 版
I am using MySQL version 4.1.22
推荐答案
你应该在两个字段之间设置一个复合键.这需要每行都有一个唯一的 stone_id 和 upcharge_title.
You should set up a composite key between the two fields. This will require a unique stone_id and upcharge_title for each row.
只要找到现有的重复项，试试这个:
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 列而不是 1 列中查找重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！