on duplicate key update

insert操作有冲突时，不执行insert操作，只更新。

mysql> show create table user;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>  insert into user(id,name) values(1,"aaa"),(2,"bbb");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
+----+------+
2 rows in set (0.00 sec)

mysql>  insert into user (id,name) values (1,"aaa") on duplicate key update id = 1,name="aaa";
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into user (id,name) values (1,"aaa") on duplicate key update id = 1,name="bbb";
Query OK, 2 rows affected (0.01 sec)

mysql>  insert into user (id,name) values (1,"aaa") on duplicate key update name="ccc"; 
Query OK, 2 rows affected (0.00 sec)

mysql> select * from user;                                                             +----+------+
| id | name |
+----+------+
|  1 | ccc  |
|  2 | bbb  |
+----+------+
2 rows in set (0.00 sec)

mysql>  insert into user (id,name) values (2,"aaa") on duplicate key update name="ddd"; 
Query OK, 2 rows affected (0.00 sec)

mysql> select * from user;                                                             +----+------+
| id | name |
+----+------+
|  1 | ccc  |
|  2 | ddd  |
+----+------+
2 rows in set (0.00 sec)

结论：

1. 字段值更新前后相同，则不执行更新操作，命令影响行数是0。

2. 目前尝试的结果是，on duplicate key update后的条件可以不接冲突字段，更新操作只会影响on duplicate key update 前面锁定的行，命令影响行数是2.