mysql中on duplicate key update的用法及說明
1、應(yīng)用場(chǎng)景
日常開發(fā)中，對(duì)于一個(gè)數(shù)據(jù)想做到存在即更新，不存在則新增，通常的做法是先查詢數(shù)據(jù)庫(kù)中是否存在對(duì)應(yīng)的數(shù)據(jù)，如果存在就使用更新的方法，不存在就使用新增的方法
如果是單個(gè)數(shù)據(jù)，倒也沒什么問題，但如果是批量數(shù)據(jù)的話，會(huì)消耗大量的資源來(lái)進(jìn)行查詢操作，這樣就得不償失了。
這種情況我們可以使用mysql提供的 on duplicate key update 來(lái)進(jìn)行操作。
2、基礎(chǔ)使用語(yǔ)法
2.1 假設(shè)此時(shí)我們表中沒有數(shù)據(jù)
執(zhí)行語(yǔ)句（為了展示效果使用用法2）
#用法1：使用values來(lái)獲取值（推薦，因?yàn)椴迦攵鄠€(gè)的時(shí)候可以用） INSERT INTO `test` ( id, name ) VALUES( 1, '曉明' ) ON DUPLICATE KEY UPDATE id = VALUES(id), name = VALUES(name) #用法2：直接使用值 INSERT INTO `user` ( id, name ) VALUES( 1, '曉明') ON DUPLICATE KEY UPDATE id = '123', name = 'xiaoming'
- 執(zhí)行結(jié)果
2.2 有數(shù)據(jù)后再次執(zhí)行
執(zhí)行相同sql語(yǔ)句，執(zhí)行結(jié)果
這里就是關(guān)鍵：
我們可以看到變動(dòng)為2，此時(shí)就是說明原數(shù)據(jù)進(jìn)行了更新，更新內(nèi)容為下面UPDATE中設(shè)置的字段值 ，id也可變化，數(shù)據(jù)庫(kù)中數(shù)據(jù)如下
Q：我們?cè)俅螆?zhí)行一次這個(gè)語(yǔ)句，結(jié)果是什么樣子呢？
A：自然是判斷出該表中無(wú)此數(shù)據(jù)，新增一條額外的新數(shù)據(jù)
- sql結(jié)果
驗(yàn)證得到結(jié)論：
其實(shí)就是會(huì)自動(dòng)檢測(cè)是否存在Duplicate entry，如果存在values后面的值就會(huì)自動(dòng)更改，不存在則插入
3、批量插入
- 執(zhí)行語(yǔ)句
INSERT INTO test(`id`,`name`,`address`)
VALUES('4','修改10','北京'),
('1', '曉明',1)
ON DUPLICATE KEY UPDATE
name=VALUES(name),address=VALUES(address);- sql結(jié)果
id為1和id為4的分別被修改和新增，可以同時(shí)進(jìn)行兩種操作類型
4、Mybatis中的寫法
- 單獨(dú)插入
<insert id="insertUser" parameterType="com.test.User">
INSERT INTO user(
id,
name,
gender,
birthday,
address)
VALUES
(#{id},
#{name},
#{gender},
#{birthday},
#{address})
ON DUPLICATE KEY UPDATE
id = VALUES(id),
name = VALUES(name),
gender = VALUES(gender)
birthday = VALUES(birthday),
address = VALUES(address)
</insert>
- 批量插入
<insert id="insertUser" parameterType="java.util.List">
INSERT INTO user(
id,
name,
gender,
birthday,
address)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id},
#{item.name},
#{item.gender},
#{item.birthday},
#{item.address})
</foreach>
ON DUPLICATE KEY UPDATE
id = VALUES(id),
name = VALUES(name),
gender = VALUES(gender)
birthday = VALUES(birthday),
address = VALUES(address)
</insert>
5、情景模擬
插入失敗提示如下
ERROR 1062 (23000): Duplicate entry 'value' for key 'PRIMARY'
如果數(shù)據(jù)庫(kù)中已有某條數(shù)據(jù)，以下的兩條語(yǔ)句可等同：
INSERT INTO tablename (id, data) VALUES (1, 10) ON DUPLICATE KEY UPDATE data=data+10;
UPDATE tablename SET data=data+10 WHERE id=1;
總結(jié)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
mysql數(shù)據(jù)庫(kù)實(shí)現(xiàn)超鍵、候選鍵、主鍵與外鍵的使用
數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí),關(guān)鍵字的概念至關(guān)重要,本文就來(lái)介紹一下mysql數(shù)據(jù)庫(kù)實(shí)現(xiàn)超鍵、候選鍵、主鍵與外鍵的使用,具有一定的參考價(jià)值,感興趣的可以了解一下2024-09-09
這篇文章主要為大家詳細(xì)介紹了MYSQL GROUP BY用法,具有一定的實(shí)用性和參考價(jià)值，感興趣的小伙伴們可以參考一下2016-10-10
mysql如何設(shè)置主從數(shù)據(jù)庫(kù)的同步
這篇文章主要介紹了mysql如何設(shè)置主從數(shù)據(jù)庫(kù)的同步問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-10-10
MySQL連表更新實(shí)戰(zhàn)案例及常見錯(cuò)誤
更新數(shù)據(jù)是使用數(shù)據(jù)庫(kù)時(shí)最重要的任務(wù)之一,下面這篇文章主要介紹了MySQL連表更新實(shí)戰(zhàn)案例及常見錯(cuò)誤的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-12-12
Mysql?InnoDB?B+樹索引目錄項(xiàng)記錄頁(yè)管理
這篇文章主要為大家介紹了Mysql?InnoDB?B+樹索引目錄項(xiàng)記錄頁(yè)管理，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2022-05-05
MySQL中having關(guān)鍵字詳解以及與where的區(qū)別
在MySQL中HAVING和WHERE是用于過濾數(shù)據(jù)的兩個(gè)重要的關(guān)鍵字,它們?cè)诓樵冋Z(yǔ)句中的位置和功能有所不同,這篇文章主要給大家介紹了關(guān)于MySQL中having關(guān)鍵字詳解以及與where區(qū)別的相關(guān)資料,需要的朋友可以參考下2024-07-07
在MySQL數(shù)據(jù)庫(kù)中ERROR 1045是一個(gè)常見的錯(cuò)誤,表示您嘗試連接數(shù)據(jù)庫(kù)時(shí)提供的用戶名和密碼不正確,這篇文章主要介紹了MySQL 1045錯(cuò)誤終極解決方法的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2026-04-04
MySQL數(shù)據(jù)庫(kù)連接數(shù)查詢、配置簡(jiǎn)單示例代碼
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)連接數(shù)查詢、配置的相關(guān)資料,連接數(shù)與性能密切相關(guān),需要根據(jù)實(shí)際需求合理配置,通過設(shè)置最大連接數(shù)、使用連接池和優(yōu)化應(yīng)用程序連接邏輯,可以提高數(shù)據(jù)庫(kù)的穩(wěn)定性和性能,需要的朋友可以參考下2025-03-03
mysql根據(jù)逗號(hào)將一行數(shù)據(jù)拆分成多行數(shù)據(jù)
本文主要介紹了mysql根據(jù)逗號(hào)將一行數(shù)據(jù)拆分成多行數(shù)據(jù)，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-12-12
實(shí)操M(fèi)ySQL+PostgreSQL批量插入更新insertOrUpdate
這篇文章主要介紹了MYsql和PostgreSQL優(yōu)勢(shì)對(duì)比以及如何實(shí)現(xiàn)MySQL + PostgreSQL批量插入更新insertOrUpdate，附含詳細(xì)的InserOrupdate代碼實(shí)例,需要的朋友可以參考下2021-08-08