sql重複插入問題

gary-liu發表於2017-03-10
SQL

問題

在專案中，由於別人併發的呼叫介面，導致插入了重複資料

解決方案

1.因為使用多臺機器部署，可以使用分散式鎖用一臺機器處理，對處理的方法加鎖或同步關鍵字，但效能會有很大影響，分散式的優勢也沒了
2.在MySQL的業務表中，根據業務建立唯一索引，防止資料重複

具體操作

建立唯一索引：

ALTER TABLE table_name ADD UNIQUE index_name (column_list)

程式中處理

如果重複插入，MySQL好像會報資料完整性的錯誤，到spring中後錯誤被封裝為 DuplicateKeyException，只要在程式中捕獲這個異常做相應的處理就可以了。

重複插入語句

建立唯一索引或使用主鍵primary後，還可以使用MySQL的重複插入語句：ignore， replace， ON DUPLICATE KEY UPDATE

INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test@163.com', '99999', '9999');

這樣當有重複記錄就會忽略,執行後返回數字0

REPLACE INTO `table_name`(`col_name`, ...) VALUES (...);

REPLACE的執行與INSERT很相像,但是如果舊記錄與新記錄有相同的值，則在新記錄被插入之前，舊記錄被刪除。

INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=`c`+1;

如果行作為新記錄被插入，則受影響行的值為1；如果原有的記錄被更新，則受影響行的值為2。

這部分具體可參考 http://www.111cn.net/database/mysql/50135.htm

總結

不過實際發現異常處理的時間要比MySQL的重複插入語句慢不少,所以可以的話還是使用MySQL的重複插入語句，不要在程式中去處理；但如果業務原因，可能也不得不在程式中處理，比如重複插入了，也要返回已有資料的主鍵id。

INNODB中NULL欄位使用插曲

在大多數情況下欄位設計應該避免使用default null的使用，而使用空字元來代表空。
因為INNODB的索引中會儲存NULL，如果一個欄位可為NULL，並且在該欄位上有索引，索引中會儲存NULL，每次索引的時候會額外掃更多的欄位。在需要使用唯一索引約束一個欄位，但是需要部分欄位為空時，空字串會引起唯一索引衝突，NULL可以在唯一索引中不產生衝突。
可參考文章：http://tomblog.readthedocs.io/en/latest/mysql/INNODB%E4%B8%ADNULL%E4%BD%BF%E7%94%A8.html
Mysql聯合唯一索引和空值: http://tomblog.readthedocs.io/en/latest/mysql/INNODB中NULL使用.html

參考資料

MySql避免重複插入記錄方法(ignore,Replace,ON DUPLICATE KEY UPDATE)

相關文章