INSERT table SET ....... ON DUPLICATE KEY UPDATE ....

表結構：

 CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'auto_id',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT 'to_identified',
  `age` int(1) NOT NULL COMMENT 'old',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='student_information' |

資料：

select * from student;
+----+--
--------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  23 |
|  2 | lisi     |  24 |
|  3 | wangwu   |  29 |
|  4 | abo      |  27 |
|  5 | justin   |  23 |
|  6 | anjilina |  19 |
|  7 | jiexika  |  18 |
|  8 | rose     |  10 |
+----+----------+-----+
8 rows in set (0.00 sec)

開始進行操作

INSERT student SET id=1,age=234  ON DUPLICATE KEY UPDATE age=456;
// id=1存在,age=234不存在
Query OK, 2 rows affected (0.00 sec)




mysql> select * from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan | 456 |
|  2 | lisi     |  24 |
|  3 | wangwu   |  29 |
|  4 | abo      |  27 |
|  5 | justin   |  23 |
|  6 | anjilina |  19 |
|  7 | jiexika  |  18 |
|  8 | rose     |  10 |
+----+----------+-----+
8 rows in set (0.00 sec)
//執行結果將id=1之前的age數值更新


mysql> INSERT student SET id=2,age=24  ON DUPLICATE KEY UPDATE age=456;    
// id=2存在,age=24存在
Query OK, 2 rows affected (0.00 sec)

mysql> select * from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan | 456 |
|  2 | lisi     | 456 |
|  3 | wangwu   |  29 |
|  4 | abo      |  27 |
|  5 | justin   |  23 |
|  6 | anjilina |  19 |
|  7 | jiexika  |  18 |
|  8 | rose     |  10 |
+----+----------+-----+
8 rows in set (0.00 sec)
//執行結果將id=2之前的age數值更新




mysql> INSERT student SET id=343,age=24  ON DUPLICATE KEY UPDATE age=456; // id=343不存在
Query OK, 1 row affected (0.00 sec)


mysql> select * from student;
+-----+----------+-----+
| id  | name     | age |
+-----+----------+-----+
|   1 | zhangsan | 456 |
|   2 | lisi     | 456 |
|   3 | wangwu   |  29 |
|   4 | abo      |  27 |
|   5 | justin   |  23 |
|   6 | anjilina |  19 |
|   7 | jiexika  |  18 |
|   8 | rose     |  10 |
| 343 |          |  24 |
+-----+----------+-----+
9 rows in set (0.00 sec)

//執行結果插入了一條id=343的資料

結論： 更新對應的值，如果存在就更新，不存在就插入

更多的官方解釋可以檢視Mysql手冊：

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html