是否可以插入一行，但仅限于某个值尚不存在？

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 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.

我正在为电子商务系统创建一个推特朋友，我需要将朋友的电子邮件插入数据库表，但前提是表中尚不存在。这是因为一旦新客户注册并购买了东西，我不希望任何超过1人获得推荐积分。因此，我只想在表格中输入一次电子邮件。

I'm using PHP 4 and MySql 4.1.

我正在使用PHP 4和MySql 4.1。

7 个解决方案

#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

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位，你可以玩，看看它是否有效，你不必指定一个动作。正如有人说的那样，如果它有独特的限制，那么无论如何都不会发生。如果您希望表中的所有电子邮件地址都是唯一的，则没有理由在列定义中将其指定为唯一。

#2

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 ('test@test.com')

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 docs for more information.

有关更多信息，请参阅MySql文档。

#3

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.

这可以归结为一个数据库查询。

#4

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_address in there and you 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 Docs：12.2.5.3

#5

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索引的新行具有相同的值，则在插入新行之前删除旧行。

#6

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..

如果它抛出异常，那么你知道你有一个重复的字段。对不起，如果那不回答你的问题..

#7

If the email field was the primary key then the constraints on the table would stop a duplicate from being entered.

如果电子邮件字段是主键，则表上的约束将阻止输入重复项。

#1