php 我可以使用 SET 选项将 ON DUPLICATE KEY UPDATE 与 INSERT 查询一起使用吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7618056/
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
Can I use ON DUPLICATE KEY UPDATE with an INSERT query using the SET option?
提问by shecky
I've seen the following (using the VALUES option):
我看到了以下内容（使用 VALUES 选项）：
$query = "INSERT INTO $table (column-1, column-2, column-3) VALUES ('value-1', 'value-2', 'value-3') ON DUPLICATE KEY UPDATE SET column1 = value1, column2 = value2, column3 = value3, ID=LAST_INSERT_ID(ID)";
... but I can't figure how to add ON DUPLICATE KEY UPDATE to what I'm using:
...但我不知道如何将 ON DUPLICATE KEY UPDATE 添加到我正在使用的内容中：
$query = "INSERT INTO $table SET
column-1 ='value-1',
column-2 ='value-2',
column-3 ='value-3'
";
e.g.:, pseudo-code
例如：，伪代码
$query = "INSERT INTO $table SET
column-1 ='value-1',
column-2 ='value-2',
column-3 ='value-3'
ON DUPLICATE KEY UPDATE SET
column1 = value1,
column2 = value2,
column3 = value3,
$id=LAST_INSERT_ID(id)";
$my_id = mysql_insert_id();
";
I would find the latter easier to read. Would appreciate clarification, didn't find an example in the manual.
我会发现后者更容易阅读。希望澄清，在手册中没有找到示例。
cheers
干杯
回答by Kevin Bedell
I've used ON DUPLICATE KEY UPDATEa lot. For some situations it's non-standard SQL extension that's really worth using.
我用ON DUPLICATE KEY UPDATE了很多。对于某些情况，它是真正值得使用的非标准 SQL 扩展。
First, you need to make sure you have a unique key constraint in place. The ON DUPLICATE KEY UPDATEfunction only kicks in if there would've been a unique key violation.
首先，您需要确保您有一个唯一的键约束。该ON DUPLICATE KEY UPDATE功能仅在存在唯一密钥违规的情况下才会启动。
Here's a commonly used format:
下面是一个常用的格式：
$query = "INSERT INTO $table (column1, column2, column3)
VALUES ('value-1', 'value-2', 'value-3')
ON DUPLICATE KEY UPDATE
column1 = values(column1),
column2 = values(column2),
column3 = values(column3);"
column1 = values(column1)means "Update column1 with the value that would have been inserted if the query hadn't hit the duplicate key violation." In other words, it just means update column1 to what it would've been had the insert worked.
column1 = values(column1)意思是“如果查询没有遇到重复键冲突，则使用将插入的值更新 column1。” 换句话说，它只是意味着将 column1 更新为插入工作时的内容。
Looking at this code, it doesn't seem correct that you're updating all three of the columns you're trying to insert. Which of the columns has a unique constraint on it?
查看此代码，您更新尝试插入的所有三列似乎不正确。哪一列对其有唯一约束？
EDIT: Modify based on 'SET' format of mysql insert statement per the question from the OP.
编辑：根据 OP 中的每个问题，根据 mysql 插入语句的“SET”格式进行修改。
Basically to use ON DUPLICATE KEY UPDATE, you just write the insert statement as you normally would, but add the ON DUPLICATE KEY UPDATEclause tacked onto the end. I believe it should work like this:
基本上要使用ON DUPLICATE KEY UPDATE，您只需像往常一样编写插入语句，但ON DUPLICATE KEY UPDATE在末尾添加子句。我相信它应该像这样工作：
INSERT INTO $table
set column1 = 'value-1',
column2 = 'value-2',
column3 = 'value-3'
ON DUPLICATE KEY UPDATE
column1 = values(column1),
column2 = values(column2),
column3 = values(column3);
Again, one of the columns you're inserting has to have a unique index (or a combination of the columns). That can be because one of them is the primary key or because there is a unique index on the table.
同样，您要插入的列之一必须具有唯一索引（或列的组合）。这可能是因为其中一个是主键，或者因为表上有一个唯一索引。
