MySQL数据库upsert使用

 本文翻译自：MySQL UPSERT - javatpoint，并附带自己的一些理解和使用经验.

MySQL UPSERT

UPSERT是数据库管理系统管理数据库的基本功能之一，它允许数据库操作语言在表中插入一条新的数据或更新已有的数据。UPSERT是一个原子操作，也就是说它是一个单步完成的操作。当UPSERT操作的是一条新数据时，会触发INSERT操作，若记录已经存在于表中，则UPSERT类似于UPDATE语句。

默认情况下，MySQL提供了ON DUPLICATE KEY UPDATE选项给INSERT语句来实现UPSERT功能。然而，INSERT还有一些其他选项来满足目标，比如INSERT IGNORE或REPLACE，我们将会学习并了解所有这些方案的细节。

MySQL UPSERT Example

我们可以通过以下三种方式来实现MySQL UPSERT操作：

1. INSERT IGNORE
2. REPLACE
3. INSERT ON DUPLICATE KEY UPDATE

INSERT IGNORE

当我们向表中插入非法行时，INSERT IGNORE语句会忽略执行时的error。比如，主键列不允许我们存储重复值。当我们使用INSERT向表中插入一条数据，而这条数据的主键已经在表中存在了，此时MySQL服务器生成error，语句执行失败。然而，当我们使用INSERT IGNORE来执行此语句时，MySQL服务器将会生成warning而不是error。

当我们使用INSERT IGNORE批量插入数据时，产生了warning的行会被忽略，即不会被插入表中。

语法：

INSERT IGNORE INTO table_name (column_names)  
VALUES ( value_list), ( value_list) .....;  

例子：

先创建一张表，主键为id，同时还有一个唯一索引email，所以id，email 都不能重复，当插入的数据和这两个字段中的任意一个重复时，就会产生warning，从而使INSERT IGNORE忽略这条数据。

 向表中插入三条数据：现在我们执行下面语句：

可知，主键或唯一索引重复都会导致服务器报错，在执行批量插入时，一条语句报错会使整个批量插入无效。但当我们使用INSERT IGNORE时，主键或为一索引重复只会产生warning，同时，INSERT IGNORE会忽视这些产生的warning的行，将没有产生warning的行插入表中：

REPLACE

在某些情况下，我们希望更新已经存在的数据。此时可以使用REPLACE，当我们使用REPLACE命令时，可能会有下列两种情况发生：

• 如果数据库中没有对应的记录，则执行标准的INSERT语句
• 如果数据库中有对应的记录，则REPLACE语句会先删除数据库中的对应记录，再执行标准的INSERT语句（当主键或唯一索引重复时，会执行此更新操作）

在REPLACE语句中，更新数据分为两步，先删除原有记录，在插入要更新的记录。

语法：

REPLACE [INTO] table_name(column_list)  
VALUES(value_list);  

例子：

上面的代码中我们通过REPLACE操作两条数据，其中，第一条数据在数据库中没有与之冲突的主键或索引，所以执行插入操作，影响了1行数据；第二条数据与数据库中有主键冲突，所以会先删除数据库中原有数据，再将该数据插入数据库，以实现更新效果，删除一行、插入一行 ，影响了两行数据，所以此次操作一共影响了三行。

INSERT ON DUPLICATE KEY UPDATE

目前为止，我们已经看过两种UPSERT命令了，但它们都有一些限制。INSERT IGNORE只是简单忽略了duplicate error。REPLACE会检测INSERT error，但是它在添加新数据前会删除原有数据。因此，我们仍然需要一种更好的解决方案。

INSERT ON DUPLICATE KEY UPDATE是一个更好的解决方案，它不会删除重复的行，当我们在SQL语句中使用ON DUPLICATE KEY UPDATE子句并且有一行数据在主键或唯一索引上产生duplicate error时，会在已有的数据上做更新。

语法：

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

例子：

当使用INSERT ON DUPLICATE KEY UPDATE插入一条不存在的数据时，结果和INSERT一样：

主键冲突：

唯一索引冲突：

同时有多个唯一索引冲突或主键和唯一索引同时冲突时，一样会导致更新操作。

INSERT ON DUPLICATE KEY UPDATE批量插入：

insert into student ( id, name, email, city)
values
( 1, '小明', 'asasasasa', 'qw'),
( 1, '小王', 'asqww', qr'),
( 1, '小芳', 'asttyty', 'ds')
on duplicate key update
name = values(name),
city = values(city);

values(col_name)函数意思是，取出当前插入语句中col_name字段对应的值。values函数的参数是列名。