mysql 中 replace into 與 insert into on duplicate key update 的用法和不同點(diǎn)實(shí)例分析
本文實(shí)例講述了mysql 中 replace into 與 insert into on duplicate key update 的用法和不同點(diǎn)。分享給大家供大家參考,具體如下:
replace into和insert into on duplicate key update都是為了解決我們平時(shí)的一個(gè)問(wèn)題
就是如果數(shù)據(jù)庫(kù)中存在了該條記錄,就更新記錄中的數(shù)據(jù),沒(méi)有,則添加記錄。
我們創(chuàng)建一個(gè)測(cè)試表test
CREATE TABLE `test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(32) DEFAULT '' COMMENT '姓名', `addr` varchar(256) DEFAULT '' COMMENT '地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
向該表中插入一些數(shù)據(jù)
INSERT INTO test VALUES (NULL, 'a', 'aaa'), (NULL, 'b', 'bbb'), (NULL, 'c', 'ccc'), (NULL, 'd', 'ddd');
影響行數(shù)4,結(jié)果如下:
我們運(yùn)行如下語(yǔ)句:
REPLACE INTO test VALUES(NULL, 'e', 'eee');
結(jié)果顯示,影響行數(shù)1條,記錄被插入成功了
注意上面的語(yǔ)句,我們并沒(méi)有填寫主鍵ID。
然后我們?cè)賵?zhí)行下面的語(yǔ)句:
REPLACE INTO test VALUES(1, 'aa', 'aaaa');
結(jié)果顯示,影響行數(shù)2條,ID為1的記錄被更新成功了
為什么會(huì)出現(xiàn)這種情況,原因就是replace into會(huì)首先嘗試先往表里面插入記錄,因?yàn)槲覀兊腎D是主鍵,不可重復(fù),顯然這條記錄是無(wú)法插入成功的,然后replace into會(huì)把這條已存在的記錄刪掉,然后再插入,所以會(huì)顯示影響行數(shù)是2。
我們?cè)龠\(yùn)行下面這條語(yǔ)句:
REPLACE INTO test(id,name) VALUES(1, 'aaa');
這里我們只指定id,name字段,我們來(lái)看看replace into后addr字段內(nèi)容是否還存在
顯然addr字段內(nèi)容沒(méi)有了,跟我們上面的分析是一致的,reaplce into先刪除了id為1的記錄,然后再插入記錄,但我們并沒(méi)有指定addr的值,所以會(huì)如上圖所示那樣。
但是有些時(shí)候我們的需求是,如果記錄存在則更新指定字段的數(shù)據(jù),原有字段數(shù)據(jù)仍保留,而不是上面所示的,addr字段數(shù)據(jù)沒(méi)有了。
這里就需要用到insert into on duplicate key update
執(zhí)行如下語(yǔ)句:
INSERT INTO test (id, name) VALUES(2, 'bb') ON DUPLICATE KEY UPDATE name = VALUES(name);
VALUES(字段名)表示獲取當(dāng)前語(yǔ)句insert的列值,VALUES(name)表示的就是'bb'
結(jié)果顯示,影響行數(shù)2條
如上圖所示,addr字段的值被保留了。
insert into on duplicate key update語(yǔ)句的做法是先插入記錄,如果不成功,則更新記錄,但是為什么影響的行數(shù)是2?
我們重新建一張表test2
CREATE TABLE `test2` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `sn` varchar(32) DEFAULT '' COMMENT '唯一鍵', `name` varchar(32) DEFAULT '' COMMENT '姓名', `addr` varchar(256) DEFAULT '' COMMENT '地址', PRIMARY KEY (`id`), UNIQUE KEY `sn` (`sn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
往里面插入點(diǎn)數(shù)據(jù)
INSERT INTO test2 VALUES (NULL, '01', 'a', 'aaa'), (NULL, '02', 'b', 'bbb'), (NULL, '03', 'c', 'ccc'), (NULL, '04', 'd', 'ddd');
我們運(yùn)行如下語(yǔ)句:
INSERT INTO test2 (sn, name, addr) VALUES ('02', 'bb', 'bbbb') ON DUPLICATE KEY UPDATE name = VALUES(name), addr = VALUES(addr);
結(jié)果如下:
每運(yùn)行一次上面的語(yǔ)句,雖然影響行數(shù)為0,但表test2的自增字段就加1。
顯然如果insert into on duplicate key update語(yǔ)句僅僅只是在原記錄基礎(chǔ)上進(jìn)行更新操作的話,自增字段是不會(huì)自動(dòng)加1的,說(shuō)明它也進(jìn)行了記錄刪除操作。
先插入記錄,如果不成功,則刪除原記錄,但保留了除update語(yǔ)句后字段的值,然后把保留的值與需要更新的值合并,然后插入一條新記錄。
總結(jié):
replace into 與 insert into on duplicate key update都是先嘗試插入記錄,如果不成功,則刪除記錄,replace into不保留原記錄的值,而insert into on duplicate key update保留。然后插入一條新記錄。
更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題:《MySQL查詢技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》
希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。
相關(guān)文章
mysql啟動(dòng)失敗之mysql服務(wù)無(wú)法啟動(dòng)(服務(wù)沒(méi)有報(bào)告任何錯(cuò)誤)的解決方法
作為一名程序猿,必不可少的便是和mysql打交道,那當(dāng)mysql故障,服務(wù)無(wú)法啟動(dòng)時(shí)該怎么解決呢,下面這篇文章主要給大家介紹了關(guān)于mysql啟動(dòng)失敗之mysql服務(wù)無(wú)法啟動(dòng),服務(wù)沒(méi)有報(bào)告任何錯(cuò)誤的解決方法,需要的朋友可以參考下2022-05-05MySQL中的GROUP_CONCAT()函數(shù)詳解與實(shí)戰(zhàn)應(yīng)用小結(jié)(示例詳解)
本文介紹了MySQL中的GROUP_CONCAT()函數(shù),詳細(xì)解釋了其基本語(yǔ)法、應(yīng)用示例以及ORDERBY和SEPARATOR參數(shù)的使用方法,此外,還提到了該函數(shù)的性能限制和注意事項(xiàng),感興趣的朋友一起看看吧2025-02-02MySQL數(shù)據(jù)庫(kù)列的增刪改實(shí)現(xiàn)方法
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)列的增刪改實(shí)現(xiàn)方法,結(jié)合實(shí)例形式分析了mysql數(shù)據(jù)庫(kù)針對(duì)列的增加、修改、刪除等相關(guān)操作sql命令及使用技巧,需要的朋友可以參考下2019-03-03mysql 5.7.17 winx64免安裝版配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了mysql 5.7.17 winx64免安裝版配置方法圖文教程,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2017-01-01MySQL 5.0.96 for Windows x86 32位綠色精簡(jiǎn)版安裝教程
這篇文章主要介紹了MySQL 5.0.96 for Windows x86 32位綠色精簡(jiǎn)版安裝教程,需要的朋友可以參考下2017-10-10MySQL對(duì)window函數(shù)執(zhí)行sum函數(shù)可能出現(xiàn)的一個(gè)Bug
這篇文章主要給大家介紹了關(guān)于MySQL對(duì)window函數(shù)執(zhí)行sum函數(shù)可能出現(xiàn)的一個(gè)Bug,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-07-07解析windows下使用命令的方式安裝mysql5.7的方法
這篇文章主要介紹了windows下使用命令的方式安裝mysql5.7的方法,本文圖文并茂給大家介紹的非常詳細(xì),需要的朋友可以參考下2017-01-01MySQL中ADD?COLUMN添加多個(gè)字段的寫法實(shí)例
這篇文章主要給大家介紹了關(guān)于MySQL中ADD?COLUMN添加多個(gè)字段的寫法實(shí)例,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2023-02-02利用SQL注入漏洞登錄后臺(tái)的實(shí)現(xiàn)方法
工作需要,得好好補(bǔ)習(xí)下關(guān)于WEB安全方面的相關(guān)知識(shí),故撰此文,權(quán)當(dāng)總結(jié),別無(wú)它意。讀這篇文章,我假設(shè)讀者有過(guò)寫SQL語(yǔ)句的經(jīng)歷,或者能看得懂SQL語(yǔ)句2012-01-01