php 是否可以插入一行但前提是值不存在？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/285937/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to insert a row but only if a value does not already exist?
提问by alex
Is it possible to insert a row, but only if one of the values already in the table does not exist?
是否可以插入一行，但前提是表中已有的值之一不存在？
I'm creating a Tell A Friendwith 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.
我正在为电子商务系统创建一个带有推荐点的Tell A Friend，我需要将朋友的电子邮件插入数据库表中，但前提是该表中尚不存在。这是因为一旦新客户注册并购买东西，我不希望有超过 1 人获得推荐积分。因此，我只想要表格中的一封电子邮件。
I'm using PHP 4 and MySql 4.1.
我正在使用 PHP 4 和 MySql 4.1。
回答by Todd
This works if you have a unique index or primary key on the column (EmailAddr in this example):
如果列上有唯一索引或主键（本例中为 EmailAddr），则此方法有效：
INSERT IGNORE INTO Table (EmailAddr) VALUES ('[email protected]')
Using this if a record with that email already exists (duplicate key violation) instead of an error, the statement just fails and nothing is inserted.
如果该电子邮件的记录已经存在（重复密钥冲突）而不是错误，则使用此语句，该语句将失败并且不会插入任何内容。
See the MySql docsfor more information.
有关更多信息，请参阅MySql 文档。
回答by victoriah
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
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.
知道我的运气有更好的方法来做到这一点。AFAIK 在 MySQL 中没有等效的“ON DUPLICATE KEY DO NOTHING”。我不确定 email=email_Address 位，您可以玩一下看看它是否有效，而无需指定操作。正如上面有人所说，如果它有独特的约束，无论如何都不会发生。如果您希望表中的所有电子邮件地址都是唯一的，则没有理由在列定义中将其指定为唯一的。
回答by Gene
Most likely something like:
很可能是这样的：
IF NOT EXISTS(SELECT * FROM myTable WHERE Email=@Email) THEN INSERT INTO blah blah
That can be rolled into one database query.
这可以合并为一个数据库查询。
回答by mttmllns
A slight modification/addition to naeblis's answer:
对 naeblis 的回答稍作修改/补充：
INSERT INTO table (email) VALUES (email_address)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)
This way you don't have to throw email=email_addressin there andyou get the correct value for LAST_INSERT_ID()if the statement updates.
这样你就不必email=email_address在那里扔了，如果语句更新，你会得到正确的值LAST_INSERT_ID()。
Source:MySQL Docs: 12.2.5.3
来源：MySQL 文档：12.2.5.3
回答by Peter Howe
MySQL offers REPLACE INTO http://dev.mysql.com/doc/refman/5.0/en/replace.html:
MySQL 提供REPLACE INTO http://dev.mysql.com/doc/refman/5.0/en/replace.html：
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
REPLACE 的工作方式与 INSERT 完全相同，但如果表中的旧行与 PRIMARY KEY 或 UNIQUE 索引的新行具有相同的值，则在插入新行之前删除旧行。
回答by José Leal
I'm not sure if I got it, but what about a
我不确定我是否得到了它，但是一个
try {
mysql_query($sql);
}
catch(Exception $e) {
}
combined with an unique field index in MySQL?
结合 MySQL 中的唯一字段索引？
if it throws an exception then you know that you got a duplicated field. Sorry if that don't answer your question..
如果它抛出异常，那么你知道你有一个重复的字段。对不起，如果那不能回答你的问题..
回答by Feet
If the email field was the primary key then the constraints on the table would stop a duplicate from being entered.
如果电子邮件字段是主键，则表上的约束将阻止输入重复项。
