避免在INSERT查询中复制唯一键

I have a MySQL query that looks like this:

我有一个MySQL查询,如下所示:

INSERT INTO beer(name, type, alcohol_by_volume, description, image_url) VALUES('{$name}', {$type}, '{$alcohol_by_volume}', '{$description}', '{$image_url}')

INSERT INTO啤酒(名称,类型,alcohol_by_volume,description,image_url)VALUES('{name name}',{$ type},'{$ alcohol_by_volume}','{$ description}','{$ image_url}')

The only problem is that name is a unique value, which means if I ever run into duplicates, I get an error like this:

唯一的问题是name是一个唯一值,这意味着如果我遇到重复,我会收到如下错误:

Error storing beer data: Duplicate entry 'Hocus Pocus' for key 2

Is there a way to ensure that the SQL query does not attempt to add a unique value that already exists without running a SELECT query for the entire database?

有没有办法确保SQL查询不会尝试添加已存在的唯一值而不运行整个数据库的SELECT查询?

5 个解决方案

#1

You could of course use INSERT IGNORE INTO, like this:

您当然可以使用INSERT IGNORE INTO,如下所示:

INSERT IGNORE INTO beer(name, type, alcohol_by_volume, description, image_url) VALUES('{$name}', {$type}, '{$alcohol_by_volume}', '{$description}', '{$image_url}')

You could use ON DUPLICATE KEY as well, but if you just don't want to add a row INSERT IGNORE INTO is a better choice. ON DUPLICATE KEY is better suited if you want to do something more specific when there are a duplicate.

您也可以使用ON DUPLICATE KEY,但如果您只是不想添加行INSERT IGNORE INTO是更好的选择。 ON DUPLICATE KEY更适合如果你想在有重复时做更具体的事情。

If you decide to use ON DUPLICATE KEY - avoid using this clause on tables with multiple unique indexes. If you have a table with multiple unique indexes ON DUPLICATE KEY-clause could be giving unexpected results (You really don't have 100% control what's going to happen)

如果您决定使用ON DUPLICATE KEY - 请避免在具有多个唯一索引的表上使用此子句。如果你有一个包含多个唯一索引的表ON DUPLICATE KEY-clause可能会给出意想不到的结果(你真的没有100%控制会发生什么)

Example: - this row below only updates ONE row (if type is 1 and alcohol_by_volume 1 (and both columns are unique indexes))

示例: - 下面的这一行只更新一行(如果type为1而alcohol_by_volume为1(两列都是唯一索引))

ON DUPLICATE KEY UPDATE beer SET type=3 WHERE type=1 or alcohol_by_volume=1

To sum it up:

把它们加起来:

ON DUPLICATE KEY just does the work without warnings or errors when there are duplicates.

ON DUPLICATE KEY当有重复项时,只执行没有警告或错误的工作。

INSERT IGNORE INTO throws a warning when there are duplicates, but besides from that just ignore to insert the duplicate into the database.

当有重复项时,INSERT IGNORE INTO会发出警告,但除此之外,只需忽略将副本插入数据库。

#2

As it just so happens, there is a way in MySQL by using ON DUPLICATE KEY UPDATE. This is available since MySQL 4.1

正如它所发生的那样,在MySQL中有一种方法可以使用ON DUPLICATE KEY UPDATE。从MySQL 4.1开始就可以使用它

INSERT INTO beer(name, type, alcohol_by_volume, description, image_url)
  VALUES('{$name}', {$type}, '{$alcohol_by_volume}', '{$description}',
  '{$image_url}')
  ON DUPLICATE KEY UPDATE type=type;

You could also use INSERT IGNORE INTO... as an alternative, but the statement would still throw a warning (albeit, instead of an error).

您也可以使用INSERT IGNORE INTO ...作为替代方法,但该语句仍会发出警告(尽管代替错误)。

#3

Yes, there is. You can use the ON DUPLICATE KEY clause of mysql INSERT statement. The syntax is explained here

就在这里。您可以使用mysql INSERT语句的ON DUPLICATE KEY子句。这里解释了语法

INSERT INTO beer(name, type, alcohol_by_volume, ...) 
  VALUES('{$name}', {$type}, '{$alcohol_by_volume}', ...)
  ON DUPLICATE KEY UPDATE 
     type={$type}, alcohol_by_volume = '{$alcohol_by_volume}', ... ;

#4

Yes, by first selecting the name from the database, and if the result of the query is not null (zero records), then the name already exists, and you have to get another name.

是的,首先从数据库中选择名称,如果查询结果不为空(零记录),则名称已存在,您必须获得另一个名称。

#5

Quite simply - your code needs to figure out what it wants to do if something's trying to insert a duplicate name. As such, what you need to do first is run a select statement:

很简单 - 如果某些东西试图插入重复的名称,您的代码需要弄清楚它想要做什么。因此,首先需要执行的是运行select语句:

 SELECT * FROM beer WHERE name='{$name}'

And then run an 'if' statement off of that to determine if you got a result.

然后运行一个'if'语句来确定你是否得到了结果。

if results = 0, then go ahead and run your insert. Else ... whatever you want to do. Throw an error back to the user? Modify the database in a different way? Completely ignore it? How is this insert statement coming about? A mass update from a file? User input from a web page?

如果结果= 0,则继续运行插入。否则......无论你想做什么。向用户抛出错误?以不同的方式修改数据库?完全忽略它?这个插入语句怎么样?从文件批量更新?用户从网页输入?

The way you're reaching this insert statement, and how it should affect your work flow, should determine exactly how you're handling that 'else'. But you should definitely handle it.

你到达这个插入语句的方式,以及它应该如何影响你的工作流程,应该确切地确定你如何处理'else'。但你绝对应该处理它。

But just make sure that the select and insert statements are in a transaction together so that other folks coming in to do the same sort of stuff isn't an issue.

但是,只需确保select和insert语句一起处于事务中,以便其他人进入相同类型的东西不是问题。

#1