1/ ON DUPLICATE KEY UPDATE的簡介

ON DUPLICATE KEY UPDATE是一種MySQL的語法，它在插入新數(shù)據(jù)時，如果遇到唯一鍵沖突（即已存在相同的唯一鍵值），則會執(zhí)行更新操作，而不是拋出異?；蚝雎栽摋l數(shù)據(jù)。這個語法可以大大簡化我們的代碼，減少不必要的判斷和查詢操作。

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

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

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

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

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

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兩個字段，而name字段沒有修改生效。由此我們可以得出重要結(jié)論：

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

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

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

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 主鍵與唯一鍵字段同時存在

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注意事項

4.1 on dupate key update 之后values的使用事項

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

4.2 對values使用判斷

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

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

• 唯一索引大小寫不敏感時

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

mysql中每個配置值是innodb_autoinc_lock_mode

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

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

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

4.5 death lock死鎖

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

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

5/ ON DUPLICATE KEY UPDATE注意事項

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

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

最新評論