mysql中on duplicate key update的用法及說明
1、應(yīng)用場景
日常開發(fā)中，對于一個(gè)數(shù)據(jù)想做到存在即更新，不存在則新增，通常的做法是先查詢數(shù)據(jù)庫中是否存在對應(yīng)的數(shù)據(jù)，如果存在就使用更新的方法，不存在就使用新增的方法
如果是單個(gè)數(shù)據(jù)，倒也沒什么問題，但如果是批量數(shù)據(jù)的話，會(huì)消耗大量的資源來進(jìn)行查詢操作，這樣就得不償失了。
這種情況我們可以使用mysql提供的 on duplicate key update 來進(jìn)行操作。
2、基礎(chǔ)使用語法
2.1 假設(shè)此時(shí)我們表中沒有數(shù)據(jù)
執(zhí)行語句（為了展示效果使用用法2）
#用法1：使用values來獲取值（推薦，因?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語句，執(zhí)行結(jié)果
這里就是關(guān)鍵：
我們可以看到變動(dòng)為2，此時(shí)就是說明原數(shù)據(jù)進(jìn)行了更新，更新內(nèi)容為下面UPDATE中設(shè)置的字段值 ，id也可變化，數(shù)據(jù)庫中數(shù)據(jù)如下
Q：我們再次執(zhí)行一次這個(gè)語句，結(jié)果是什么樣子呢？
A：自然是判斷出該表中無此數(shù)據(jù)，新增一條額外的新數(shù)據(jù)
- sql結(jié)果
驗(yàn)證得到結(jié)論：
其實(shí)就是會(huì)自動(dòng)檢測是否存在Duplicate entry，如果存在values后面的值就會(huì)自動(dòng)更改，不存在則插入
3、批量插入
- 執(zhí)行語句
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ù)庫中已有某條數(shù)據(jù)，以下的兩條語句可等同：
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滿意度調(diào)查分組去除最高最低求平均分的實(shí)現(xiàn)思路
該文章介紹了如何使用SQL查詢實(shí)現(xiàn)按部門分組,去除每個(gè)部門的最高分和最低分,并計(jì)算剩余分?jǐn)?shù)的平均分,文章詳細(xì)描述了實(shí)現(xiàn)這一需求的步驟,包括使用窗口函數(shù)、子查詢和CTE等技術(shù),感興趣的朋友跟隨小編一起看看吧2025-01-01
本文主要介紹了MySQL中union和unionall區(qū)別，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-04-04
事務(wù)(Transaction)是訪問和更新數(shù)據(jù)庫的程序執(zhí)行單元;事務(wù)中可能包含一個(gè)或多個(gè)sql語句,這些語句要么都執(zhí)行,要么都不執(zhí)行,下面這篇文章主要給大家介紹了關(guān)于MySQL基礎(chǔ)教程之事務(wù)異常情況的相關(guān)資料,需要的朋友可以參考下2022-10-10
MYSQL的select 學(xué)習(xí)筆記...2007-02-02
Navicat遠(yuǎn)程連接SQL Server并轉(zhuǎn)換成MySQL步驟詳解
最近遇到一個(gè)需求是將SQL Server轉(zhuǎn)換為 MySQL的格式，由于不想在本地安裝 SQL Server，所以決定在遠(yuǎn)程的 Windows 服務(wù)器上安裝，并在本地使用Navicat遠(yuǎn)程連接它，然而在實(shí)現(xiàn)過程中遇到了諸多問題，記錄于此。感興趣的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧。2017-01-01
一般情況下,我們排序都是直接利用 order by 字段 asc/desc;但是如果要排序的字段數(shù)據(jù)格式并不能直接實(shí)現(xiàn),或者說我們需要指定的順序且沒有什么規(guī)律,簡單的order by字段就實(shí)現(xiàn)不了,所以本文給大家介紹了MySQL實(shí)現(xiàn)字段的自定義排序的方法,需要的朋友可以參考下2024-04-04
這篇文章給大家介紹了mysql 安裝失敗的快速解決方法包括windows下mysql安裝失敗的一個(gè)解決案例，本文給大家介紹的非常詳細(xì)，具有參考借鑒價(jià)值，感興趣的朋友一起看下吧2016-10-10
存儲(chǔ)過程可以一次執(zhí)行多個(gè)sql語句,所以php只連接數(shù)據(jù)庫一次就能查詢多個(gè)語句;不過要返回多個(gè)結(jié)果集就必須用mysqli擴(kuò)展來查詢,否則會(huì)提示錯(cuò)誤can't return a result set in the given context2008-09-09
MySQL自動(dòng)填充create_time和update_time的兩種方式
當(dāng)我們創(chuàng)建業(yè)務(wù)表的時(shí)候 通常都需要設(shè)置create_time 和 update_time,下面這篇文章主要給大家介紹了關(guān)于MySQL自動(dòng)填充createTime和updateTime的兩種方式,需要的朋友可以參考下2022-05-05
MySQL比較運(yùn)算符使用詳解及注意事項(xiàng)
這篇文章主要給大家介紹了關(guān)于MySQL比較運(yùn)算符使用詳解及注意事項(xiàng)的相關(guān)資料,Mysql可以通過運(yùn)算符來對表中數(shù)據(jù)進(jìn)行運(yùn)算,比如通過出生日期求年齡等,需要的朋友可以參考下2024-01-01