INSERT ... ON DUPLICATE KEY UPDATE Syntax

如果表上有唯一值约束，为防止insert时唯一值约束列上发生唯一键冲突报错，可以采用insert into table ... on duplicate update ...来做处理

测试表结构，id和col1上都有唯一值约束

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udk_col1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

13:18:56[test](;)> select * from t1;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | 2 | 3 |
| 2 | 3 | 4 |
+----+------+------+
2 rows in set (0.00 sec)

当新插入的和主键冲突时

insert into t1 values(2,4,4) on duplicate key update col2=col2+1;
13:49:54[test](;)> select * from t1;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    2 |    3 |
|  2 |    3 |    5 |
+----+------+------+
2 rows in set (0.01 sec)

相当与执行了

update t1 set col2=col2+1 where id=2;

当主键有自增属性时，只insert时如果检测到冲突，会自增1，但是上面的写法就不会进行自增操作；还有值得关注的是当执行update后面的语句后，影响行数会变为2

当多个主键发生冲突时，且涉及到的行数大于1时只会对其中一行进行update

　　  
#相当于

update t1 set col2=col2+1 where id=1 or col1=3;

13:57:51[test](;)> select * from t1;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    2 |    4 |
|  2 |    3 |    5 |
+----+------+------+
2 rows in set (0.00 sec)

当在两个列上创建唯一值约束时

alter table t1 add col3 int;
alter table t1 add unique udk_col2_col3(col2,col3);
14:35:28[test](;)> select * from t1;
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 |    2 |    3 | NULL |
|  2 |    3 |    5 | NULL |
+----+------+------+------+
2 rows in set (0.00 sec)
14:35:48[test](;)> insert into t1(col2,col3) values(5,null);
Query OK, 1 row affected (0.10 sec)
14:36:54[test](;)> insert into t1(col2,col3) values(5,1);
Query OK, 1 row affected (0.11 sec)

14:37:04[test](;)> insert into t1(col2,col3) values(5,1);
ERROR 1062 (23000): Duplicate entry '5-1' for key 'udk_col2_col3'
14:37:07[test](;)> select * from t1;  
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 |    2 |    3 | NULL |
|  2 |    3 |    5 | NULL |
|  4 | NULL |    5 | NULL |
|  5 | NULL |    5 |    1 |
+----+------+------+------+
4 rows in set (0.00 sec)

14:38:22[test](;)> insert into t1(col2,col3) values(5,1) on duplicate key update col1=col2+col3;
Query OK, 2 rows affected (0.11 sec)

14:38:24[test](;)> select * from t1;
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 |    2 |    3 | NULL |
|  2 |    3 |    5 | NULL |
|  4 | NULL |    5 | NULL |
|  5 |    6 |    5 |    1 |
+----+------+------+------+
4 rows in set (0.00 sec)

对于上面的操作，对于col2=5,co3 is null时再插入相同的值依然可以成功，说明唯一索引对null值不做约束