我可以使用SET选项对插入查询使用ON DUPLICATE KEY UPDATE吗? [英] Can I use ON DUPLICATE KEY UPDATE with an INSERT query using the SET option?

问题描述

我已经看到了以下内容(使用VALUES选项):

I've seen the following (using the VALUES option):

$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)"; 

...但是我不知道如何在我正在使用的内容上添加ON DUPLICATE KEY UPDATE:

... but I can't figure how to add ON DUPLICATE KEY UPDATE to what I'm using:

$query = "INSERT INTO $table SET
    column-1 ='value-1',
    column-2 ='value-2',
    column-3 ='value-3'
";

例如:，伪代码

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

欢呼

推荐答案

我经常使用ON DUPLICATE KEY UPDATE.在某些情况下，非标准的SQL扩展确实值得使用.

I've used ON DUPLICATE KEY UPDATE a lot. For some situations it's non-standard SQL extension that's really worth using.

首先，您需要确保具有适当的唯一键约束. ON DUPLICATE KEY UPDATE函数仅在发生唯一键冲突时才起作用.

First, you need to make sure you have a unique key constraint in place. The ON DUPLICATE KEY UPDATE function only kicks in if there would've been a unique key violation.

这是一种常用的格式:

 $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)的意思是如果查询没有遇到重复的键冲突，则使用要插入的值更新column1".换句话说，这仅意味着将column1更新为如果插入有效的状态.

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.

看这段代码，您更新要插入的所有三列似乎不正确.哪一列对此有唯一约束?

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?

根据OP中的问题，基于mysql插入语句的'SET'格式进行修改.

Modify based on 'SET' format of mysql insert statement per the question from the OP.

基本上要使用ON DUPLICATE KEY UPDATE，您只需像往常一样编写insert语句，但在末尾添加ON DUPLICATE KEY UPDATE子句.我相信它应该像这样工作:

Basically to use ON DUPLICATE KEY UPDATE, you just write the insert statement as you normally would, but add the ON DUPLICATE KEY UPDATE clause tacked onto the end. I believe it should work like this:

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.

这篇关于我可以使用SET选项对插入查询使用ON DUPLICATE KEY UPDATE吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！