插入重复的密钥更新 [英] insert on duplicate key update

问题描述

我想在查询中使用在重复键更新上插入"来插入新行(如果不存在)或更新行(如果存在).我似乎无法弄清楚的是，如果我没有唯一的ID，该如何使用它(因为尚未创建该行，并且该ID在插入时会自动递增)

I would like to use "insert on duplicate key update" in a query to either insert a new row if it does not exist or update a row if it does. What I can not seem to figure out is how to use this if I do not have the unique id (because the row has not yet been created, and this ID will be autoincremented upon insert)

insert into foodchoices (unique,notunique) values (Idonthavethis,'test')
on duplicate key update notunique = 'stuff';

现在，在上面的示例中，该字段显示为"Idonthavethis"，由于该字段尚未作为行插入，因此我对该字段没有任何唯一值.但是，我确实希望这能以某种方式插入，但我只是不知道如何插入.我希望它会像这样:

Now, in this example above, where it says "Idonthavethis", I do not have any unique value for this field, because it has not yet been inserted as a row. However, I do expect that this inserts somehow, I just dont know how. I expect it to act like this:

insert into foodchoices (notunique) values ('test')

但是，如果它是一个已经存在的字段，我将具有该唯一值.没有唯一值时，是否可以使用某种形式的通配符或其他格式?

BUT, if it is a field that does already exist, I WILL have that unique value. Is there some form of wildcard or something I can use for when I do not have the unique value?

推荐答案

我相信在如果表包含 AUTO_INCREMENT 列，并且 INSERT ... UPDATE 插入一行，则 LAST_INSERT_ID()函数返回 AUTO_INCREMENT 值.如果该语句改为更新一行，则 LAST_INSERT_ID()没有意义.但是，您可以使用 LAST_INSERT_ID(expr)解决此问题.假设id是 AUTO_INCREMENT 列.要使 LAST_INSERT_ID()对更新有意义，请插入以下行:

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

这篇关于插入重复的密钥更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！