SQL防止重复的条目(如果存在两个值) [英] SQL Preventing Duplicate Entries if 2 Values Exist
问题描述
我有一个查询，我想检查是否有2列具有相同的数据，如果不添加数据，我将详细说明.
I have a query that I want to check if there are 2 columns with the same data and if so not add data, I will elaborate.
这是我的查询
INSERT INTO " . TABLE_PREFIX . "installs (
username,userid,email,addontitle,addonversion,bburl,bbtitle,webmasteremail,cookie,dateline
) VALUES (
'$username','$userid','$email','$addontitle','$addonversion','$bburl','$bbtitle','$webmasteremail','$cookie',NOW()
)
我想检查bburl和addontitle是否与正在提交的条目匹配，如果不匹配则不添加该条目. 让我们添加一些示例条目
I want to check if bburl and addontitle match an entry being submitted and if so not to add that entry. Lets add some example entries
1 --- test@test.com --- Addon --- 1.2 --- test.com --- test --- admin@test.com --- 1 --- ????????
1 --- test@test.com --- Addon2 --- 1.2 --- test.com --- test --- admin@test.com --- 1 --- ????????
1 --- test@test.com --- Addon --- 1.2 --- test2.com --- test --- admin@test.com --- 1 --- ????????
所有这些条目都会添加，但是现在我们得到另一个条目:
all of these entries would add but now we get another entry:
1 --- test@test.com --- Addon --- 1.3 --- test2.com --- test --- admin@test.com --- 1 --- ????????
test2.com已经安装了Addon，所以我不希望添加此条目.我该如何预防?
test2.com has already installed Addon so I dont want this entry added. How can I prevent this?
作为一个单独的问题.如果您发现我不需要的请求条目，似乎test2.com只是在更新产品，有没有办法让我修改现有值并更新已更改的内容?
As a separate question. If you notice the requested entry I don't want, it seems test2.com was just updating the product, is there a way I can make that modify the existing values and update what has been changed?
推荐答案
您可以在要唯一的列上创建唯一的索引/约束:
You can create a unique index/constraint on the columns you want to be unique:
alter table t add constraint unq_t_bburl_addontitle unique t(bburl, addontitle)
然后在表上插入将产生错误.您可以通过on duplicate key update避免错误:
An insert on the table will then generate an error. You can avoid the error with on duplicate key update:
insert into t ( . . . )
values ( . . . )
on duplicate key update bburl = values(bburl);
update不执行任何操作(因为值已经匹配)，但这可以防止代码返回错误.
The update does nothing (because the values already match) but this prevents the code from returning an error.
这篇关于SQL防止重复的条目(如果存在两个值)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
