问题描述
是否可以插入一行，但前提是该表中已经存在的值之一不存在?
Is it possible to insert a row, but only if one of the values already in the table does not exist?
我正在为电子商务系统创建带有引荐点的告诉朋友，我需要在其中将朋友的电子邮件插入数据库表，但前提是该电子邮件地址在数据库表中不存在桌子.这是因为我不希望有超过1个人在新客户注册并购买商品后获得推荐积分.因此，我只希望在表中一次发送一封电子邮件.
I'm creating a Tell A Friend with referral points for an ecommerce system, where I need to insert the friend's email into the database table, but only if it doesn't already exist in the table. This is because I don't want any more than 1 person getting the referral points once the new customer signs up and purchases something. Therefore I want only one email ever once in the table.
我正在使用PHP 4和MySql 4.1.
I'm using PHP 4 and MySql 4.1.
推荐答案
如果列是主键还是唯一索引:
If the column is a primary key or a unique index:
INSERT INTO table (email) VALUES (email_address) ON DUPLICATE KEY UPDATE
email=email_address
知道我的运气，有更好的方法. AFAIK在MySQL中没有等效的"ON DUPLICATE KEY DO NOTHING".我不确定email = email_Address位，您无需指定任何操作就可以尝试一下，看看它是否有效.正如上面有人指出的那样，如果它具有独特的约束条件，则无论如何都不会发生.而且，如果您希望表格中的所有电子邮件地址都是唯一的，则没有理由在列定义中将其指定为唯一.
Knowing my luck there's a better way of doing it though. AFAIK there's no equivalent of "ON DUPLICATE KEY DO NOTHING" in MySQL. I'm not sure about the email=email_Address bit, you could play about and see if it works without you having to specify an action. As someone states above though, if it has unique constraints on it nothing will happen anyway. And if you want all email addresses in a table to be unique there's no reason to specify it as unique in your column definition.
这篇关于仅当值不存在时才可以插入行吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！