為什么不建議使用ON DUPLICATE KEY UPDATE？
往期熱門(mén)文章：
1、3種常見(jiàn)的數(shù)據(jù)脫敏方案 2、這個(gè)隊(duì)列的思路真的好，現(xiàn)在它是我簡(jiǎn)歷上的亮點(diǎn)了。 3、痛快！SpringBoot終于禁掉了循環(huán)依賴！ 4、BigDecimal使用不當(dāng)，造成P0事故！ 5、Spring Boot 啟動(dòng)時(shí)自動(dòng)執(zhí)行代碼的幾種方式，還有誰(shuí)不會(huì)？？
文章來(lái)源：https://c1n.cn/BeR1Q
目錄
背景
官方資料
進(jìn)行驗(yàn)證
總結(jié)
背景
<update id="updateByIds">
update tb_user
<trim prefix="set" suffixOverrides=",">
<trim prefix="name = case" suffix="end,">
<foreach collection="list" item="i" index="index">
when id= #{i.id,jdbcType=VARCHAR} then #{i.name,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="weight = case" suffix="end,">
<foreach collection="list" item="i" index="index">
when id= #{i.id,jdbcType=VARCHAR} then #{i.weight,jdbcType=DECIMAL}
</foreach>
</trim>
<trim prefix="high = case" suffix="end,">
<foreach collection="list" item="i" index="index">
when id= #{i.id,jdbcType=VARCHAR} then #{i.high,jdbcType=DECIMAL}
</foreach>
</trim>
</trim>
where id in
<foreach collection="list" item="item" open="(" close=")" separator=",">
#{item.id,jdbcType=VARCHAR}
</foreach>
</update>
官方資料
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
進(jìn)行驗(yàn)證
CREATE TABLE `t1` (
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵ID',
`b` int(11),
`c` int(11),
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='臨時(shí)測(cè)試表'
| 驗(yàn)證主鍵插入并更新功能
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
INSERT INTO t1 (b,c) VALUES (20,30)
ON DUPLICATE KEY UPDATE c=c+1;
| 驗(yàn)證多字段唯一索引問(wèn)題
If column b is also unique, the INSERT is equivalent to this UPDATE statement instead:
UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.
ALTER TABLE t1 ADD UNIQUE INDEX uniq_b (b ASC);
INSERT INTO t1 (a,b,c) VALUES (3,20,30)
ON DUPLICATE KEY UPDATE c=c+1;
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;
ALTER TABLE t1 DROP INDEX uniq_b ;
ALTER TABLE ntocc_test.t1
ADD UNIQUE INDEX uniq_b (b ASC);
;
The effects are not quite identical: For an InnoDB table where a is an auto-increment column, the INSERT statement increases the auto-increment value but the UPDATE does not.
UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.
| 涉及到的鎖說(shuō)明
An INSERT … ON DUPLICATE KEY UPDATE on a partitioned table using a storage engine such as MyISAM that employs table-level locks locks any partitions of the table in which a partitioning key column is updated. (This does not occur with tables using storage engines such as InnoDB that employ row-level locking.) For more information, see Section 22.6.4, “Partitioning and Locking”.
Hi,
I am facing this same issue in version 5.7.18. Deadlock error when multiple threads execute INSERT… ON DUPLICATE KEY UPDATE for bulk insert/update.
How it can be fixed?
I am facing the same issue when multiple threads are trying to insert in same table with primary key and unique index. Records are being inserted are different. Also It seems to be taking next-key lock here.
總結(jié)
on duplicate key update 在 MyISAM 存儲(chǔ)引擎下使用的是表鎖，性能不好。
on duplicate key update 在 InnoDB 下并發(fā)事務(wù)情況下可能會(huì)存在鎖表/死鎖問(wèn)題。
應(yīng)盡量避免在多唯一索引的情況下使用此語(yǔ)句。
往期熱門(mén)文章：
1、MySQL 暴跌！ 2、超越 Xshell！號(hào)稱下一代 Terminal 終端神器，用完愛(ài)不釋手！ 3、IDEA 官宣全新默認(rèn) UI，太震撼了！！ 4、讓你直呼「臥槽」的 GitHub 項(xiàng)目！ 5、Kafka又笨又重，為啥不選Redis？ 6、50多個(gè)高頻免費(fèi) API 接口分享 7、IDEA公司再發(fā)新神器！超越 VS Code 騷操作! 8、我懷疑這是 IDEA 的 BUG，但是我翻遍全網(wǎng)沒(méi)找到證據(jù)！ 9、Spring MVC 中的 Controller 是線程安全的嗎？ 10、Gitee 倒下了？？？