系統(tǒng)環(huán)境：
Mysql：5.7.32
基本功能
如果在
INSERT語句末尾指定了ON DUPLICATE KEY UPDATE語句，并且新插入的行會導(dǎo)致UNIQUE KEY（唯一索引）或PRIMARY KEY（主鍵值）重復(fù)惕橙，那么會對原有記錄進行UPDATE操作湘纵；如果不會導(dǎo)致唯一索引或主鍵值重復(fù)帕胆，則執(zhí)行INSERT操作；
解決了什么問題
我們在實際的項目中逛拱，經(jīng)常有以下需求：
向表中插入一條記錄時，如果
UNIQUE KEY（唯一索引）或PRIMARY KEY已存在糠亩，則更新記錄，否則插入一條記錄
邏輯上我們會這么寫（偽代碼）：
result = mysql_query("select...")
row = mysql_fetch(result)
if row：
mysql_query("update...")
else:
mysql_query("insert...")
這么做有兩個問題：
1准验、效率低下赎线，每次需要執(zhí)行兩條語句
2、高并發(fā)時會出問題糊饱，不能保證兩條語句的原子性
Mysql通過ON DUPLICATE KEY UPDATE語句為我們解決了以上問題垂寥，既保證了原子性也保證了效率。
PS：在使用
INSET...ON DUPLICATE KEY UPDATE這樣的語句來插入記錄時另锋，如果遇到主鍵或者唯一二級索引列的值重復(fù)滞项，會對B+樹中已存在的相同鍵值的記錄加X鎖。
與UPDATE語句的異同
1夭坪、假設(shè)列a為UNIQUE索引且a=1的記錄已經(jīng)存在文判，下面兩條語句效果相同：
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;
區(qū)別：
如果
a是Innodb表中的自增鍵，則INSERT語句會導(dǎo)致auto-increment的值增加室梅，UPDATE語句則不會
2戏仓、如果b也是UNIQUE索引，下面兩條語句效果相同：
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 OR b=2 LIMIT 1;
區(qū)別：
如果一條記錄中包含多個唯一索引（含主鍵）亡鼠，那么即使匹配上多條記錄也只會更新一條記錄柜去。所以，一般情況在拆宛，不要在有多個唯一索引的表上使用
ON DUPLICATE KEY UPDATE語句；
存在什么問題
1讼撒、對于
Innodb引擎的表浑厚，使用INSET...ON DUPLICATE KEY UPDATE語句會導(dǎo)致auto-increment的值增加；
2根盒、如果一條記錄中包含多個唯一索引（含主鍵）钳幅，那么即使匹配上多條記錄也只會更新一條記錄。所以炎滞，一般情況在敢艰，不要在有多個唯一索引的表上使用ON DUPLICATE KEY UPDATE語句；
3册赛、如果是插入操作钠导，影響的行數(shù)為1震嫉；如果是更新操作，影響的行數(shù)為2牡属；如果更新的數(shù)據(jù)和已有的數(shù)據(jù)一樣（就相當于沒變票堵，所有值保持不變），影響的行數(shù)為0逮栅；
4悴势、這是Mysql獨有的語法，其它數(shù)據(jù)庫可能不支持措伐。
VALUES函數(shù)
在ON DUPLICATE KEY UPDATE語句的賦值表達式中特纤，可以使用VALUES(列名)來引用INSERT語句中該列的值，也就是將要插入的列的值侥加。在多行插入時VALUES函數(shù)特別有用捧存。
示例：
INSERT INTO t1 (a, b, c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
等同于
INSERT INTO t1 (a, b, c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a, b, c) VALUES (4,5,6)
ON DUPLICATE KEY UPDATE c=9;
Tips：VALUES()函數(shù)只在
ON DUPLICATE KEY UPDATE或INSERT語句中有意義，其它時候會返回NULL官硝。
實驗
1矗蕊、實驗前準備，創(chuàng)建表氢架，并插入一條記錄
CREATE TABLE t1 (
a int(11) NOT NULL AUTO_INCREMENT,
b int(11),
c int(11),
PRIMARY KEY(a)
)ENGINE=Innodb default charset=utf8;
INSERT INTO t1(a, b, c) VALUES(1, 1, 1);
查看表中記錄
mysql> SELECT * FROM t1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 1 |
+---+------+------+
1 row in set (0.01 sec)
查看當前Auto_increment值：
mysql> show table status like 't1'\G;
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 2
Create_time: 2021-03-09 03:32:51
Update_time: 2021-03-09 03:33:22
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
2傻咖、單UNIQUE鍵測試
插入ON DUPLICATE KEY UPDATE語句并查看：
mysql> INSERT INTO t1 (a, b, c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
Query OK, 2 rows affected (0.01 sec)
從插入結(jié)果看，影響了2行記錄岖研。
再次查看表中記錄及Auto_increment值：
mysql> SELECT * FROM t1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 2 |
+---+------+------+
1 row in set (0.00 sec)
mysql> show table status like 't1'\G;
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 2 // 與官網(wǎng)文檔不符卿操，按照官網(wǎng)文檔，這里應(yīng)該增加孙援，實際卻并沒有增加害淤，不知道是哪里不對
Create_time: 2021-03-09 03:32:51
Update_time: 2021-03-09 03:34:40
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
3、多UNIQUE鍵測試
修改t1表結(jié)構(gòu)拓售，將b列改為UNIQUE索引
mysql> ALTER TABLE t1 ADD UNIQUE KEY uk_b(b);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
增加一列
mysql> INSERT INTO t1(a, b, c) VALUES(2, 2, 3);
Query OK, 1 row affected (0.01 sec)
查看
mysql> desc t1;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| a | int(11) | NO | PRI | NULL | auto_increment |
| b | int(11) | YES | UNI | NULL | |
| c | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM t1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 2 |
| 2 | 2 | 3 |
+---+------+------+
2 rows in set (0.00 sec)
mysql> show table status like 't1'\G;
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 3
Create_time: 2021-03-09 03:40:17
Update_time: 2021-03-09 03:41:41
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
插入ON DUPLICATE KEY UPDATE語句窥摄，使a、b列同時沖突：
mysql> INSERT INTO t1(a, b, c) VALUES(1, 2, 4) ON DUPLICATE KEY UPDATE c=c+1;
Query OK, 2 rows affected (0.00 sec)
查看
mysql> SELECT * FROM t1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 3 |
| 2 | 2 | 3 |
+---+------+------+
2 rows in set (0.00 sec)
從結(jié)果看础淤，只有第一行執(zhí)行更新崭放，驗證了多行記錄滿足條件只會更新一行的說法。
4鸽凶、VALUSE()函數(shù)實驗
先把b列索引刪除币砂，改為普通列
mysql> ALTER TABLE t1 DROP KEY uk_b;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
插入記錄
mysql> INSERT INTO t1 (a, b, c) VALUES (1,2,3), (2,5,6), (3, 4, 5) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 1 Warnings: 0
查看結(jié)果
mysql> SELECT * FROM t1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 3 |
| 2 | 2 | 7 |
| 3 | 4 | 5 |
+---+------+------+
3 rows in set (0.00 sec)
可見，所有重復(fù)主鍵的行已經(jīng)更新玻侥，沒有重復(fù)主鍵的按原數(shù)據(jù)執(zhí)行插入操作决摧。
還可以將原數(shù)據(jù)與新插入的數(shù)據(jù)一起操作，示例如下：
mysql> INSERT INTO t1 (a, b, c) VALUES (1,2,5) ON DUPLICATE KEY UPDATE c=c+VALUES(c);
Query OK, 2 rows affected (0.01 sec)
查看結(jié)果
mysql> SELECT * FROM t1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 8 |
| 2 | 2 | 7 |
| 3 | 4 | 5 |
+---+------+------+
3 rows in set (0.00 sec)
總結(jié)
INSERT...ON DUPLICATE KEY UPDATE語句，會在UNIQUE KEY（唯一索引）或PRIMARY KEY（主鍵值）重復(fù)時掌桩，對原有記錄進行UPDATE操作边锁；不重復(fù)，則執(zhí)行INSERT操作拘鞋；VALUSE()函數(shù)在INSERT...ON DUPLICATE KEY UPDATE語句中非常有用砚蓬，它可以獲取到將要插入的列的值，對多行操作非常有用INSERT...ON DUPLICATE KEY UPDATE有一些副作用盆色，使用時要注意
AUTO_INCREMENT自增值問題（這個在實驗中未驗證）- 多行匹配時灰蛙，只能更新一行（當表中存在多個唯一索引時慎用）
參考
Mysql官方文檔
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
