快捷導(dǎo)航

Mysql存在則修改不存在則新增的兩種實(shí)現(xiàn)方法實(shí)例

更新時(shí)間：2022年09月13日 09:20:17 作者：barcke

mysql語法支持如果數(shù)據(jù)存在則更新,不存在則插入,下面這篇文章主要給大家介紹了關(guān)于Mysql存在則修改不存在則新增的兩種實(shí)現(xiàn)方法,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

前言

假設(shè)有一張demo表，主鍵為id，唯一索引是code

create table demo
(
	id int auto_increment,
	name int null,
	gender int null,
	age int null,
	code int null,
	constraint demo_pk
		primary key (id)
);

create unique index demo_code_uindex
	on demo (code);

方法一 replace into

replace into 會(huì)根據(jù)唯一索引或主鍵進(jìn)行判斷，如果存在則覆蓋寫入字段，如果不存在則新增。

此方法有坑，如果主鍵是自增的，且通過唯一索引來進(jìn)行操作時(shí)，主鍵會(huì)變更，該方法底層是先進(jìn)性delete，在insert如果有子表

依賴的話不建議使用。

replace into 事例

REPLACE INTO demo(id, name, gender, age, code) VALUES (1,'1',1,1,1)

通過主鍵修改，此時(shí)沒有任何問題，id還是1

當(dāng)我們通過唯一索引code來更改。

REPLACE INTO demo(name, gender, age, code) VALUES ('1',1,1,1)

沒執(zhí)行一次主鍵id都會(huì)自增。

方法二 on duplicate key

on duplicate key 如果遇到重復(fù)的唯一索引則會(huì)進(jìn)行update，否則進(jìn)行新增，沒有replcae的坑，不會(huì)先進(jìn)行delete 在進(jìn)行 insert

on duplicate key 事例：

INSERT INTO demo(name, gender, age, code)
VALUES ('1',
       1,
       2,
       2)
ON DUPLICATE KEY UPDATE name   = values(name),
                       gender = values(gender),
                       age    = values(age),
                       code   = if(values(code) = 1,values(code), code)

無論執(zhí)行多少次，主鍵值都是不會(huì)變的。

但是此方法也有坑，如果表中不止一個(gè)唯一索引的話，在特定版本的mysql中容易產(chǎn)生dead lock（死鎖）

當(dāng)mysql執(zhí)行INSERT ON DUPLICATE KEY的 INSERT時(shí)，存儲(chǔ)引擎會(huì)檢查插入的行是否會(huì)產(chǎn)生重復(fù)鍵錯(cuò)誤。如果是的話，它會(huì)將現(xiàn)有的

行返回給mysql，mysql會(huì)更新它并將其發(fā)送回存儲(chǔ)引擎。當(dāng)表具有多個(gè)唯一或主鍵時(shí)，此語句對(duì)存儲(chǔ)引擎檢查密鑰的順序非常敏感。根據(jù)這個(gè)順序，

存儲(chǔ)引擎可以確定不同的行數(shù)據(jù)給到mysql，因此mysql可以更新不同的行。存儲(chǔ)引擎檢查key的順序不是確定性的。例如，InnoDB按照索引添加到

表的順序檢查鍵。

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寫入。

如果有兩個(gè)事務(wù)并發(fā)的執(zhí)行同樣的語句，那么就會(huì)產(chǎn)生death lock