在MySQL資料庫中,有哪些方法可以避免重複的插入資料?
資料庫的唯一性是很多業務場景需要考慮的事情,我覺得可以有以下幾種方案:
1。資料表建立唯一索引。唯一索引能在資料庫層面確保資料不重發,重發insert的資料會返回報錯。
2。資料insert前先查詢是否存在。資料校驗是必要的,不能什麼資料都往資料庫裡操作,判斷存在的資料執行update即可。
3。如果是可以直接覆蓋的語句,又不想預先查詢,可以使用replace關鍵字。這個關鍵字比較少用,方法原理:將相同的欄位資料直接先刪掉,再重新新增,成功之後顯示操作條數的是刪除的條數加上新新增的條數。
4。有時候後端語言層面會避免不了的併發操作,例如大量執行緒的情況下。可以以先寫入redis,再寫入mysql
最常見的方式就是為欄位設定主鍵或唯一索引,當插入重複資料時,丟擲錯誤,程式終止,但這會給後續處理帶來麻煩,因此需要對插入語句做特殊處理,儘量避開或忽略異常,下面我簡單介紹一下,感興趣的朋友可以嘗試一下:
這裡為了方便演示,我新建了一個user測試表,主要有id,username,sex,address這4個欄位,其中主鍵為id(自增),同時對username欄位設定了唯一索引:
01
insert ignore into
即插入資料時,如果資料存在,則忽略此次插入,前提條件是插入的資料欄位設定了主鍵或唯一索引,測試SQL語句如下,當插入本條資料時,MySQL資料庫會首先檢索已有資料(也就是idx_username索引),如果存在,則忽略本次插入,如果不存在,則正常插入資料:
02
on duplicate key update
即插入資料時,如果資料存在,則執行更新操作,前提條件同上,也是插入的資料欄位設定了主鍵或唯一索引,測試SQL語句如下,當插入本條記錄時,MySQL資料庫會首先檢索已有資料(idx_username索引),如果存在,則執行update更新操作,如果不存在,則直接插入:
03
replace into
即插入資料時,如果資料存在,則刪除再插入,前提條件同上,插入的資料欄位需要設定主鍵或唯一索引,測試SQL語句如下,當插入本條記錄時,MySQL資料庫會首先檢索已有資料(idx_username索引),如果存在,則先刪除舊資料,然後再插入,如果不存在,則直接插入:
04
insert if not exists
即
insert into … select … where not exist ... ,
這種方式適合於插入的資料欄位沒有設定主鍵或唯一索引,當插入一條資料時,首先判斷MySQL資料庫中是否存在這條資料,如果不存在,則正常插入,如果存在,則忽略:
目前,就分享這4種MySQL處理重複資料的方式吧,前3種方式適合欄位設定了主鍵或唯一索引,最後一種方式則沒有此限制,只要你熟悉一下使用過程,很快就能掌握的,網上也有相關資料和教程,介紹的非常詳細,感興趣的話,可以搜一下,希望以上分享的內容能對你有所幫助吧,也歡迎大家評論、留言進行補充。
在
MySQL
中大概有四種方式可以防止資料的重複插入,我們以如下user_basic_infor表為例,分別簡單介紹一下這幾種方式,user_basic_infor的建表SQL語句如下:
insert ignore into
如上,在 user_basic_infor表中使用主鍵索引(PRIMARY KEY)以及唯一索引(UNIQUE KEY)確保資料具的唯一性,為避免重複插入記錄可以使用
insert ignore into
語法,如下:
當使用
ignore
插入資料時,如果出現錯誤,如重複記錄,將不返回錯誤,僅以警告形式返回。因此使用
ignore
時,請確保SQL語句本身沒有問題,否則也將會被忽略掉。
on duplicate key update
同樣,在user_basic_infor表中使用主鍵索引(PRIMARY KEY)以及唯一索引(UNIQUE KEY)確保資料具的唯一性,為避免重複插入記錄也可以使用
on duplicate key update
語法,如下:
如果在
insert
語句末尾指定了
on duplicate key update ……
,並且插入行後會導致在主鍵索引(PRIMARY KEY)或者唯一索引(UNIQUE KEY)中出現重複值時,則在出現重複值的行執行
update
,如果不會導致唯一值列重複的問題,則插入該新行。
replace into
同樣,在user_basic_infor表中使用主鍵索引(PRIMARY KEY)以及唯一索引(UNIQUE KEY)確保資料具的唯一性,為避免重複插入記錄也可以使用replace into 語法,如下:
replace
嘗試把新行插入到表中,當如果插入資料的主鍵索引(PRIMARY KEY)以及唯一索引(UNIQUE KEY)出現重複,導致報錯而造成插入失敗時,會先從表中刪除原有涉及到重複的行,然後再次嘗試把新行插入到表中,這種方法就是無論原來有沒有相同的記錄,都會先刪除再執行插入。
insert … select … where not exist ……
除此之外,在 MySQL 中,插入一條記錄,我們可以先檢查這條記錄是否已經存在,當記錄不存在時再執行插入操作,這樣可以不只透過主鍵索引(PRIMARY KEY)或者唯一索引(UNIQUE KEY)來判斷,也可透過其它條件,如下:
方法一:使用ignore關鍵字
如果是用主鍵primary或者唯一索引unique區分了記錄的唯一性,避免重複插入記錄可以使用:
複製程式碼 程式碼如下:
INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES (‘test9@163。com’, ‘99999’, ‘9999’);
這樣當有重複記錄就會忽略,執行後返回數字0
資料重複需要根據不通的業務不通的方法來解決,下面是一些常規的解決方法:
1、根據業務,加唯一約束。例如訂單表,你可以對流水號或者訂單號增加unique。
2、根據業務,如果不能加唯一約束,透過加鎖實現。例如mysql的get_lock和release_lock可以去顯示。當然自己建立一個鎖表也是可以的,將整個請求鎖住。
3、如果mysql效能不好,就使用一些快取系統去實現,例如redis或者Memcache。
4、非同步佇列
簡要說下三者的區別:
insert into 最普遍的插入,如果表中存在主鍵相同的資料,執行會報錯。
replace into 如果表中存在主鍵相同的資料則根據主鍵修改當前主鍵的資料,反之則插入(
存在就刪除然後插入
,
反之直接插入
)
insert ignore 如果表中存在主鍵相同的資料不在插入該條資料,反之則插入(
存在則忽略,反之插入
)
測試:
1。新增一張測試表 並預置資料
CREATE TABLE `insert_text` ( `id` varchar(50) NOT NULL, `value` varchar(50) DEFAULT NULL , `memo` varchar(50) DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `insert_text` (`id`, `value`, `memo`) VALUES (“1”, “2”, “3”);
2。檢測replace into執行效果
(1)執行語句(該語句的ID與預置資料的ID相同):
REPLACE INTO `insert_text` (`id`, `value`, `memo`) VALUES (‘1’, ‘replace’, ‘replace’);
執行結果:
(注意:這裡返回的受影響行數為2,所以在統計插入資料成功的條數需注意下)
(2)執行語句(該語句的ID與預置資料的ID不同):
REPLACE INTO `insert_text` (`id`, `value`, `memo`) VALUES (‘2’, ‘replace’, ‘replace’);
執行結果:
在這裡因為主鍵不同,可以理解為執行了insert into
3。檢測insert ignore執行效果:
(1)執行語句(該語句的ID與預置資料的ID相同):
INSERT IGNORE `insert_text` (`id`, `value`, `memo`) VALUES (‘1’, ‘ignore’, ‘ignore’);
資料未發生改變
(2)執行語句(該語句的ID與預置資料的ID不同):
INSERT IGNORE `insert_text` (`id`, `value`, `memo`) VALUES (‘2’, ‘ignore’, ‘ignore’);
在這裡因為主鍵不同,可以理解為執行了insert into
好了,測試完成,需要根據不同的場景選擇對應的插入方式————————fillt
MySQL
資料庫中避免重複資料插入
在資料庫中已經存在重複資料 ,並且資料量很大 的時候,在修改sql語句在插入時 避免重複插入,可以有以下方法。
設定唯一索引
使用ignore或者REPLACE INTO 或者ON DUPLICATE KEY UPDATE
on duplicate key update
如果資料存在會觸發更新操作 執行後面語句的update
INSERT INTO tb_table(a,b,c) VALUE(‘1’,‘bbb’,‘ccc’) ON DUPLICATE KEY UPDATE b= ‘bbb’,c=‘ccc’
ON DUPLICATE KEY UPDATE 之後的語句不是條件判斷 條件的判斷會根據你的唯一索引來判斷觸發update之後會執行 UPDATE之後的語句進行更新如果update之後寫的是條件判斷的話 就會出現只能插入無法更新的操作
replace into
如果資料存在就刪除再插入
REPLACE INTO `student`(`name`, `age`) VALUES(‘Jack’, 18);
insert ignore into
如果插入時 資料存在則忽略此次插入資料
INSERT IGNORE INTO `student`(`name`, `age`) VALUES(‘Jack’, 18);
沒有設定唯一索引
但是當你的資料庫中資料存在重複,重複欄位並沒有設定唯一索引或者主鍵的時候,
上面的方法是無法使用的,這種情況可以使用
insert if not exists
insert if not existsINSERT INTO TABLE (field1, field2, fieldn) SELECT ‘field1’,‘field2’,‘fieldn’FROM 表 WHERENOT EXISTS (SELECT * FROM 表名 WHERE 欄位= ?)
首先資料重複定義需要明確,是某個欄位還是多個欄位重複。
重複定義的不同,使用的方法不同。
譬如
id不可重複,使用索引即可。儲存資料會失敗,進行異常回滾。
欄位較多的,索引過多效能受影響,可以透過程式碼邏輯判斷,先判斷在儲存。
如果在分散式下儲存,此時還需要引入分散式鎖,保證每次僅一端操作!分散式下還會引入其他問題,資料一致性問題,這些都是需要考慮的。
具體還是取決業務需求、利益權衡!
試著站在生活角度來回答一下這個資料庫問題
1。人不能兩次踏入一條河流,是因為時間,河流都隨時發生了變化。
2。世界上沒有兩片完全相同的葉子,是因為造物主賦予了生命。
3。一個人不允許擁有兩個身份證,是因為公安機關在管理。
對於資料庫來說,是否有唯一性索引,是否有寫入前判斷機制,是否重複資料約定規則足夠清晰,都是影響插入重複資料的關鍵。
生活中的規律,與此相似。
你可以用insert if not exists寫sql語句,例如:
INSERT INTO TABLE (field1, field2, fieldn) SELECT ‘field1’,‘field2’,‘fieldn’
FROM
表名
WHERE
NOT EXISTS (
SELECT
*
FROM
表名
WHERE
欄位= ?
)