MySQL Insert On Duplicate Key Update

Insert on Duplicate Key Update 语句是 MySQL 中 INSERT 语句的扩展。当我们在 SQL 语句中指定 ON DUPLICATE KEY UPDATE 子句并且行会导致UNIQUE 或 PRIMARY KEY索引列中的重复错误值时，就会更新现有行。

换句话说，当我们向表中插入新值时，它会导致 UNIQUE OR PRIMARY KEY 列中出现重复行，我们将收到一条错误消息。但是，如果我们在SQL语句中使用 ON DUPLICATE KEY UPDATE 子句，它将使用新行值更新旧行，无论它具有唯一键列还是主键列。

例如，如果列 col1 被定义为 UNIQUE 并且包含值 10 到表 tab1 中，我们将在执行以下两个语句后得到类似的效果：

mysql> INSERT INTO tab1 (col1, col2, col3) VALUES (10,20,30) ON DUPLICATE KEY UPDATE col3=col3+1;  
  
mysql> UPDATE tab1 SET col3=col3+1 WHERE col1=1;  

它确保如果插入的行与表中的多个唯一索引匹配，则 ON DUPLICATE KEY 语句仅更新第一个匹配的唯一索引。因此，不建议对包含多个唯一索引的表使用此语句。

如果表包含 AUTO_INCREMENT 主键列并且 ON DUPLICATE KEY 语句尝试插入或更新行，则 Last_Insert_ID() 函数将返回其 AUTO_INCREMENT 值。

以下是MySQL中Insert on Duplicate Key Update语句的语法

INSERT INTO table (column_names)  
VALUES (data)  
ON DUPLICATE KEY UPDATE   
column1 = expression, column2 = expression…;  

在这个语法中，我们可以看到INSERT 语句仅在找到重复行时才添加带有列值对分配的 ON DUPLICATE KEY UPDATE 子句。ON DUPLICATE KEY UPDATE 子句的工作首先尝试将新值插入行，如果发生错误，它将使用新行值更新现有行。

VALUES() 函数仅用于该子句，在任何其他上下文中没有任何意义。它从 INSERT 部分返回列值，对于多行插入特别有用。

MySQL根据给定的操作使用 ON DUPLICATE KEY UPDATE 语句给出受影响的行数：

• 如果我们将新行插入到表中，它会返回一个受影响的行。
• 如果我们将现有行更新到表中，它会返回两个受影响的行。
• 如果我们使用其当前值将现有行更新到表中，它会返回受影响的行数 0。

MySQL INSERT ON DUPLICATE KEY 示例

让我们通过一个示例来了解 MySQL 中 INSERT ON DUPLICATE KEY UPDATE 子句的工作原理。

首先，使用以下语句创建一个名为“Student”的表：

CREATE TABLE Student (  
  Stud_ID int AUTO_INCREMENT PRIMARY KEY,  
  Name varchar(45) DEFAULT NULL,  
  Email varchar(45) DEFAULT NULL,  
  City varchar(25) DEFAULT NULL  
);  

接下来，将数据插入表中。执行以下语句：

INSERT INTO Student(Stud_ID, Name, Email, City)   
VALUES (1,'Stephen', 'stephen@javatpoint.com', 'Texax'),   
(2, 'Joseph', 'Joseph@javatpoint.com', 'Alaska'),   
(3, 'Peter', 'Peter@javatpoint.com', 'california');  

执行SELECT语句来验证插入操作：

SELECT * FROM Student;  

我们将得到如下输出，其中表中有三行：

同样，使用以下查询在表中再添加一行：

INSERT INTO Student(Stud_ID, Name, Email, City)   
VALUES (4,'John', 'john@javatpoint.com', 'New York');  

上面的语句将成功添加行，因为它没有任何重复的值。

最后，我们将在Stud_ID列中添加具有重复值的行：

INSERT INTO Student(Stud_ID, Name, Email, City)   
VALUES (4, 'John', 'john@javatpoint.com', 'New York')  
ON DUPLICATE KEY UPDATE City = 'California';  

MySQL 在成功执行上述查询后给出以下消息：

Query OK, 2 rows affected.  

在下面的输出中，我们可以看到行 id=4已经存在。因此，该查询仅将City New York更新为California。