MySQL系列教程 --- 38、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;  

我们将得到的输出如下，我们有3行到表：

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

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已经存在。所以查询仅更新城市纽约与加州。