1/ ON DUPLICATE KEY UPDATE的簡(jiǎn)介

ON DUPLICATE KEY UPDATE是一種MySQL的語法，它在插入新數(shù)據(jù)時(shí)，如果遇到唯一鍵沖突（即已存在相同的唯一鍵值），則會(huì)執(zhí)行更新操作，而不是拋出異常或忽略該條數(shù)據(jù)。這個(gè)語法可以大大簡(jiǎn)化我們的代碼，減少不必要的判斷和查詢操作。

2/ ON DUPLICATE KEY UPDATE用法總結(jié)

1. on duplicate key update語句根據(jù)主鍵id或唯一鍵來判斷當(dāng)前插入是否已存在。
2. 記錄已存在時(shí)，只會(huì)更新on duplicate key update之后指定的字段。
3. 如果同時(shí)傳遞了主鍵和唯一鍵，以主鍵為判斷存在依據(jù)，唯一鍵字段內(nèi)容可以被修改。
4. 唯一鍵大小寫敏感，大小寫不同的值被認(rèn)為是兩個(gè)值，執(zhí)行插入。

3/ ON DUPLICATE KEY UPDATE用法總結(jié)

3.1 根據(jù)主鍵ID進(jìn)行更新

on dupdate key update 語句基本功能是：當(dāng)表中沒有原來記錄時(shí)，就插入，有的話就更新

insert into tb_student(id,name,age,address,update_time) values(1001,'jinkens',19,'上海','2024-03-05 15:59:35') 
on duplicate key update
age=values(age),
address=values(address),
update_time =now();

從執(zhí)行結(jié)果可以看出，更新了id為1001的age,address兩個(gè)字段，而name字段沒有修改生效。由此我們可以得出重要結(jié)論：

1. on duplicate key update 語句根據(jù)主鍵id來判斷當(dāng)前插入是否已存在。
2. 已存在時(shí)，只會(huì)更新on duplicate key update之后限定的字段。

3.2 根據(jù)主鍵ID進(jìn)行更新

根據(jù)唯一索引進(jìn)行更新是生產(chǎn)中比較常用的方式，因?yàn)閕d一般使用的是自增，很少會(huì)先把id查詢出來，然后根據(jù)id進(jìn)行更新。

insert into tb_student(name,age,address,update_time) values('jinkens',19,'上海','2024-03-05 15:59:35') 
on duplicate key update
age=values(age),
address=values(address),
update_time =now();

從執(zhí)行結(jié)果看，這次沒有傳入id,但是age,address字段仍然更新了。

on duplicate key update 語句也可以根據(jù)唯一鍵來判斷當(dāng)前插入的記錄是否已存在。

3.3 沒有主鍵或唯一鍵字段值相同就插入

insert into tb_student(name,age,address,update_time) values('jinkens',19,'上海','2024-03-05 15:59:35') 
on duplicate key update
name =values(name),
age=values(age),
address=values(address),
update_time =now();

沒有主鍵或唯一鍵字段值相同，既判斷當(dāng)前記錄不存在，新插入一條。

3.4 主鍵與唯一鍵字段同時(shí)存在

insert into tb_student(id,name,age,address,update_time) values(1001,'jinkens',19,'上海','2024-03-05 15:59:35') 
on duplicate key update
name =values(name),
age=values(age),
address=values(address),
update_time =now();

連唯一鍵name也被修改了

如果傳遞了主鍵，是可以修改為唯一鍵字段內(nèi)容

4/ ON DUPLICATE KEY UPDATE注意事項(xiàng)

4.1 on dupate key update 之后values的使用事項(xiàng)

on dupdate key update之后沒有用values的情況，只有當(dāng)使用了values后，才會(huì)更新為上下文中傳入值。

4.2 對(duì)values使用判斷

達(dá)到的效果是，如果傳入的name值為null,則不更新。不為null則更新。

4.3 唯一索引大小寫敏感問題

• 唯一索引大小寫不敏感時(shí)

4.4 ON DUPLICATE KEY UPDATE每次更新導(dǎo)致ID不連續(xù)原理

mysql中每個(gè)配置值是innodb_autoinc_lock_mode

innodb_autoinc_lock_mode中有3中模式,0、1和2，mysql5的默認(rèn)配置是1.

• 0是每次分配自增id的時(shí)候都會(huì)鎖表.
• 1只有在bulk insert的時(shí)候才會(huì)鎖表,簡(jiǎn)單insert的時(shí)候只會(huì)使用一個(gè)light-weight mutex,比0的并發(fā)性能高
• 2.沒有仔細(xì)看,好像是很多的不保證…不太安全.

數(shù)據(jù)庫(kù)默認(rèn)是1的情況下,就會(huì)發(fā)生上面的那種現(xiàn)象,每次使用insert into … on duplicate key update 的時(shí)候都會(huì)把簡(jiǎn)單自增id增加,不管是發(fā)生了insert還是update

4.5 death lock死鎖

在執(zhí)行insert … on duplicate key 語句時(shí)，如果不對(duì)同一個(gè)表同時(shí)進(jìn)行并發(fā)的insert或者update，基本不會(huì)造成死鎖。既insert … on duplicate key時(shí)盡量單線程串行進(jìn)行新增或更新

insert … on duplicate key 在執(zhí)行時(shí)，innodb引擎會(huì)先判斷插入的行是否產(chǎn)生重復(fù)key錯(cuò)誤，如果存在，在對(duì)該現(xiàn)有的行加上S（共享鎖）鎖，如果返回該行數(shù)據(jù)給mysql,然后mysql執(zhí)行完duplicate后的update操作，然后對(duì)該記錄加上X（排他鎖），最后進(jìn)行update寫入。

5/ ON DUPLICATE KEY UPDATE注意事項(xiàng)

• 唯一索引控制的是該數(shù)據(jù)是插入還是修改，當(dāng)唯一索引對(duì)應(yīng)的值存在時(shí)，就修改，否則新增。
• on duplicate key update 后面部分控制的是當(dāng)唯一索引所對(duì)應(yīng)的值存在時(shí)需要修改的內(nèi)容。

到此這篇關(guān)于MySQL中On duplicate key update的實(shí)現(xiàn)示例的文章就介紹到這了,更多相關(guān)MySQL On duplicate key update內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！