MySQL INSERT ON DUPLICATE KEY UPDATE

插入时如果遇到重复键值进行更新语句是MySQL中INSERT语句的扩展。当我们在SQL语句中使用ON DUPLICATE KEY UPDATE子句，并且一个行会导致在UNIQUE或PRIMARY KEY索引列中出现重复错误值时，将发生对现有行的更新。

换句话说，当我们向表中插入新值，并且在UNIQUE或PRIMARY KEY列中导致重复行时，将会得到一个错误信息。然而，如果我们在SQL语句中使用ON DUPLICATE KEY UPDATE子句，它会将旧行更新为新行的值，无论它是否具有唯一或主键列。

例如 ，如果列col1被定义为UNIQUE并且包含值10，则在执行下面两个语句之后，将会得到类似的效果：

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已经存在。因此，查询仅更新了城市New York为California。