主頁(yè) > 知識(shí)庫(kù) > MySql 如何實(shí)現(xiàn)無(wú)則插入有則更新

MySql 如何實(shí)現(xiàn)無(wú)則插入有則更新

熱門(mén)標(biāo)簽:北京金倫外呼系統(tǒng) 武漢電銷機(jī)器人電話 催天下外呼系統(tǒng) 400電話辦理服務(wù)價(jià)格最實(shí)惠 呂梁外呼系統(tǒng) html地圖標(biāo)注并導(dǎo)航 大豐地圖標(biāo)注app 400電話變更申請(qǐng) 南太平洋地圖標(biāo)注

摘要

某些場(chǎng)景會(huì)有這樣的需求:無(wú)記錄則插入,有記錄則更新。例如:新增用戶,以身份證號(hào)碼作為唯一身份標(biāo)識(shí),插入時(shí)若先查詢是否存在記錄再?zèng)Q定插入還是更新,在高并發(fā)情況下必然存在問(wèn)題。本文提供三種解決方案。

方案一 加鎖

無(wú)論通過(guò)synchronized鎖、ReentranLock鎖還是分布式鎖,都可以解決該問(wèn)題。缺點(diǎn)是,加鎖會(huì)影響性能。方法二和三都是數(shù)據(jù)庫(kù)層面解決方案,個(gè)人感覺(jué)比方法一好一些。

方案二 Unique和Replace Into … SELECT …

首先對(duì)唯一性的字段添加唯一索引ALTER TABLE tb_name ADD UNIQUE (col1、col2...),,通過(guò)唯一索引即可保證數(shù)據(jù)的唯一性。

加入唯一索引后,通過(guò)INSERT INTO插入相同數(shù)據(jù)就會(huì)報(bào)錯(cuò),此時(shí)需要使用REPLACE INTO插入數(shù)據(jù),用法是一樣的。通過(guò)REPLACE INTO插入數(shù)據(jù)時(shí),若存在相同數(shù)據(jù),會(huì)將之前的記錄刪除,再重新插入數(shù)據(jù)。缺點(diǎn)是,存在先刪除再插入的過(guò)程,sql需要考慮全部數(shù)據(jù)列,不然會(huì)丟失部分列的數(shù)據(jù)。缺點(diǎn)是,建立唯一索引會(huì)影響插入效率。下面是具體的例子。

# 建立索引
ALTER TABLE user ADD UNIQUE (id_card);
# 假設(shè)user表只有id,name,id_card三個(gè)字段,且id字段自增。
# 現(xiàn)在需要插入name=ly,id_card=142733的記錄。
# 但是,若之前存在id_card=142733的記錄,修改name=ly即可。
REPLACE INTO user (id,name,id_card) 
SELECT id,'ly',142733 FROM user RIGHT JOIN (SELECT 1) AS tab 
ON user.id_card = 142733;

通過(guò)RIGHT JOIN (SELECT 1),若存在id_card=142733的記錄,執(zhí)行sql后會(huì)將原始id保存在臨時(shí)的結(jié)果集中,隨name和id_card一同插入。若不存在該記錄,則將null作為id隨name和id_card一同插入。最終實(shí)現(xiàn)

方案三 通過(guò)預(yù)插入語(yǔ)句判斷是否存在記錄

通過(guò)預(yù)插入語(yǔ)句,嘗試插入,判斷修改的記錄是否大于0,若大于0表示插入成功,若為0則表示記錄已存在,需要執(zhí)行更新操作。

# 預(yù)插入
INSERT INTO user (name,id_card)
SELECT 'ly',142733 FROM DUAL 
WHERE NOT EXISTE (SELECT id_card FROM user WHERE id_card = 142733) ;
# 若預(yù)插入語(yǔ)句插入成功(修改記錄數(shù)=1),則無(wú)需后續(xù)操作。否則執(zhí)行更新操作。
UPDATE user SET name = 'ly' WHERE id_card = 142733;

通過(guò)NOT EXISTE條件,若存在id_card=142733的記錄則偽表DUAL記錄為空,預(yù)插入語(yǔ)句修改記錄為0,此時(shí)需要執(zhí)行更新操作。

若不存在id_card=142733的記錄,則偽表DUAL記錄為一行且內(nèi)容是'ly',142733,預(yù)插入語(yǔ)句修改記錄為1,此時(shí)不必執(zhí)行更新語(yǔ)句。

Mysql批量插入更新性能優(yōu)化

對(duì)于數(shù)據(jù)量較大的插入和更新,因io/cpu等性能瓶頸,會(huì)產(chǎn)生大量的時(shí)間消耗,目前主流的優(yōu)化主要包括預(yù)編譯、單條sql插入多條數(shù)據(jù)、事務(wù)插入等,下面詳細(xì)介紹一下:

單條插入(Mybatis)

INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
(${cityCode}, ${cityName}, ${provinceName}, ${alias}, ${abbrePy})

單條預(yù)編譯插入(Mybatis)

采用預(yù)編譯可以節(jié)約mysql服務(wù)的解析時(shí)間,mytatis中就是采用#變量

INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
(#{cityCode}, #{cityName}, #{provinceName}, #{alias}, #{abbrePy})

單條sql插入多條數(shù)據(jù)

即是拼接sql,在一個(gè)sql中插入多條或更新多條數(shù)據(jù)。

INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
("cityCode1", "cityName1", "provinceName1" "alias1", "abbrePy1"),("cityCode2", "cityName2", "provinceName2" "alias2", "abbrePy2")

快的原因

1、合并后日志量(MySQL的binlog和innodb的事務(wù)讓日志)減少了,降低日志刷盤(pán)的數(shù)據(jù)量和頻率,從而提高效率;

2、通過(guò)合并SQL語(yǔ)句,減少網(wǎng)絡(luò)傳輸?shù)腎O;

3、通過(guò)合并SQL語(yǔ)句,減少SQL語(yǔ)句解析的次數(shù);

注意事項(xiàng)

1、數(shù)據(jù)庫(kù)sql長(zhǎng)度是有限制,sql長(zhǎng)度別溢出,會(huì)報(bào)錯(cuò);

2、亂序插入時(shí)候速度超過(guò)innodb_buffer的容量,每次定位索引涉及較多的磁盤(pán)讀寫(xiě)操作,性能下降較快;

事務(wù)插入

事務(wù)插入即在插入前開(kāi)啟事務(wù),插入結(jié)束關(guān)閉事務(wù)進(jìn)行提交即可。

快的原因

1、進(jìn)行一個(gè)INSERT操作時(shí),MySQL內(nèi)部會(huì)建立一個(gè)事務(wù),在事務(wù)內(nèi)才進(jìn)行真正插入處理操作。通過(guò)使用事務(wù)可以減少創(chuàng)建事務(wù)的消耗;

注意事項(xiàng)

1、事務(wù)不能過(guò)大,MySQL有innodb_log_buffer_size配置項(xiàng),事務(wù)超出這個(gè)時(shí),會(huì)刷磁盤(pán),導(dǎo)致性能下降;

2、亂序插入時(shí)候速度超過(guò)innodb_buffer的容量,每次定位索引涉及較多的磁盤(pán)讀寫(xiě)操作,性能下降較快;

測(cè)試結(jié)果

環(huán)境:i5-4200U 1.6GHZ,12G內(nèi)存,固態(tài)硬盤(pán)

\ :單條插入: :單條預(yù)編譯: :單條插入多條: :事務(wù)插入:
1000 4600毫秒 3334毫秒 8毫秒 704毫秒
10000 27204毫秒 26249毫秒 2959毫秒 2959毫秒
100000 240954毫秒 254716毫秒 17286毫秒 20539毫秒

總結(jié)

采用合并sql+事務(wù)插入組合,效率最高,亂序插入時(shí)候速度超過(guò)innodb_buffer的容量,每次定位索引涉及較多的磁盤(pán)讀寫(xiě)操作,性能下降較快;盡量采用非亂序方式即可。以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。

您可能感興趣的文章:
  • mysql如果數(shù)據(jù)不存在,則插入新數(shù)據(jù),否則更新的實(shí)現(xiàn)方法
  • mysql 記錄不存在時(shí)插入 記錄存在則更新的實(shí)現(xiàn)方法
  • mysql 存在該記錄則更新,不存在則插入記錄的sql

標(biāo)簽:麗水 西寧 無(wú)錫 迪慶 徐州 龍巖 自貢 南充

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySql 如何實(shí)現(xiàn)無(wú)則插入有則更新》,本文關(guān)鍵詞  MySql,如何,實(shí)現(xiàn),無(wú),則,插入,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySql 如何實(shí)現(xiàn)無(wú)則插入有則更新》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于MySql 如何實(shí)現(xiàn)無(wú)則插入有則更新的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章