mysql insert into on_MySQL之INSERT INTO ON DUPLICATE KEY UPDATE用法详解 | 夕辞

MySQL数据库使用中如果主键或唯一键冲突，会报错，因此我们会用到insert into on duplicate key update语法，但是该语法具体怎么用以及返回什么值，今天就来讨论下。

首先，看下相比直接insert into，update触发的时机：

如果指定ON DUPLICATE KEY UPDATE语句，那么当将要插入的行导致Unique index(唯一索引)或Primary key(主键索引)值重复的时候，就会发生旧数据的更新。

例如以下例子，对于Update效果，他们有相似的作用：

INSERT INTO t1 (a,b,c) VALUES (1,2,3)

ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

在ON DUPLICATE KEY UPDATE子句中的赋值表达式中，可以使用VALUES(col_name)函数从INSERT ... ON DUPLICATE KEY UPDATE语句的INSERT部分引用列值。换句话说，ON DUPLICATE KEY UPDATE子句中的VALUES(col_name)是指在没有重复键冲突的情况下将要插入的col_name的值。此功能在多行插入中特别有用。 VALUES()函数仅在ON DUPLICATE KEY UPDATE子句或INSERT语句中才有意义，否则返回NULL。

PS：对于a为自动递增列的InnoDB表，效果并不相同。对于自动递增列，INSERT语句会增加自动递增值，但UPDATE不会。

如果b列也是唯一的，那么INSERT和以下UPDATE语句有相同效果：

UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

如果a = 1或b = 2匹配几行，则仅更新一行。通常，应该尝试避免在具有多个唯一索引的表上使用ON DUPLICATE KEY UPDATE子句。

执行后返回数值：

1.如果是首次插入，则受影响行值为1；

2.如果是更新了现有行，则受影响行值为2；

3.如果更新的值和现有值一样，则受影响行值为0。

注意：针对场景3，如果在连接到mysqld时为mysql_real_connect()，API函数指定CLIENT_FOUND_ROWS标志，并且将现有行设置为当前值，则受影响的行值为1(而不是0)。

以上就是基本用法，当然也有一些更复杂的注意事项，不过对于InnoDB引擎，不过在正常的开发中，如果表有很多唯一索引，应尽量通过程序控制，而不用SQL来保证事务及原子性，以免造成问题，比如Redis、Zookeeper，在常见的原子操作中，都可以作为锁来保证事务，毕竟INSERT INTO不可以用WHERE条件。