Mysql的奇技淫巧 - 避免重復插入數據

MySql

---

避免重復插入數據

通常那我們插入一條帶有唯一性字段數據的時候，我們先去查一下數據庫有沒有相同數據，需要操作2次數據庫，總感覺這樣做特別的low，那么咱們來看下怎么操作一次數據庫實現來這樣的需求，當然這個唯一性數據要給他設置上主鍵索引或者唯一索引

1、初始演示表：

(1) 測試表結構

字段類型是否null默認說明idintno主鍵 自增namevarchar(200)no唯一索引urlvarchar(200)no..

(2)擁有數據

mysql> select *from test;+----+------+------+| id | name | url |+----+------+------+| 1 | 1 | 1 |+----+------+------+1 row in set (0.00 sec)

2、使用ignore關鍵詞

mysql> INSERT IGNORE INTO `test` ( `name`, `url`) VALUES ( 'sqc', '6666'),('1','3333');Query OK, 1 row affected (0.03 sec)Records: 2 Duplicates: 1 Warnings: 0受影響的行: 1時間: 0.029s[正常SQL]INSERT INTO `test` ( `name`, `url`) VALUES ( 'sqc11', '6666'),('111','3333');受影響的行: 2時間: 0.042s可以看到ignore效率還是可觀的

結果：

mysql> select *from test+----+------+------+| id | name | url |+----+------+------+| 1 | 1 | 1 || 4 | sqc | 6666 |+----+------+------+2 rows in set (0.00 sec)

可以看到當有重復記錄就會忽略,不進行插入操作，執行后返回數字0 其他還有類型用法如：(復制表,并且避免重復記錄)

INSERT IGNORE INTO `table_1` (`name`) SELECT `name` FROM `table_2`;

3、使用Replace

使用格式：

REPLACE INTO `table_name`(`col_name`, ...) VALUES (...);REPLACE INTO `table_name` (`col_name`, ...) SELECT ...;REPLACE INTO `table_name` SET `col_name`='value',相關的算法說明：REPLACE的運行與INSERT很相像,但是如果舊記錄與新記錄有相同的值，則在新記錄被插入之前，發現主鍵或唯一索引出現數據重復而造成插入失敗時，舊記錄先被刪除，再次嘗試把新行插入到表中 舊記錄與新記錄有相同的值的判斷標準就是：表有一個PRIMARY KEY或UNIQUE索引，否則，使用一個REPLACE語句沒有意義。該語句會與INSERT相同，因為沒有索引被用于確定是否新行復制了其它的行。返回值：REPLACE語句會返回一個數，來指示受影響的行的數目。該數是被刪除和被插入的行數的和受影響的行數可以容易地確定是否REPLACE只添加了一行，或者是否REPLACE也替換了其它行：檢查該數是否為1(添加)或2(替換)。替換過程影響行數會翻倍[SQL]REPLACE INTO `test` ( `name`, `url`) VALUES ( 'sqc', '6666'),('1','3333');受影響的行: 4時間: 0.025s發生了2次替換！[SQL]REPLACE INTO `test` ( `name`, `url`) VALUES ( 'sqc', '6666'),('2','3333');受影響的行: 3時間: 0.025s發生了1次替換！

4、ON DUPLICATE KEY UPDATE 方法

唯一鍵有重復的執行更新

注意：1、應盡量避免對帶有多個唯一關鍵字的表使用ON DUPLICATE KEY子句。2、當您使用ON DUPLICATE KEY UPDATE時，DELAYED選項被忽略。3、ON DUPLICATE KEY UPDATE只是MySQL的特有語法，并不是SQL標準語法！4、特別說明：在MYSQL中UNIQUE索引將會對null字段失效

例如，將上面的test表結構更改下 刪除name(唯一索引)字段 ，列id 為 主鍵 (或 擁有UNIQUE索引)，并且包含值1，則以下兩個語句具有相同的效果：

INSERT INTO test (id,url) VALUES (1,3) ON DUPLICATE KEY UPDATE url=url+1;UPDATE test SET url=url+1 WHERE id=1;如果行作為新記錄被插入，則受影響行的值顯示1；如果原有的記錄被更新，則受影響行的值顯示2。 [SQL]INSERT INTO test (id,url) VALUES (1,3) ON DUPLICATE KEY UPDATE url=url+1;受影響的行: 2時間: 0.030s

這個語法還可以這樣用:

執行前url = 1INSERT INTO test (id,url) VALUES (1,3),(1,444) ON DUPLICATE KEY UPDATE url=url+1;[SQL]INSERT INTO test (id,url) VALUES (1,3),(1,444) ON DUPLICATE KEY UPDATE url=url+1;受影響的行: 4時間: 0.030s執行后url = 3 (第二條與第一條重復, url在原值上又+1).

大家可以看到上面并沒有用到咱們插入是傳給url的值,如 (id,url) VALUES (1,3),(1,444),引用被插入的col的值使用VALUES函數 如：

INSERT INTO test (id,url) VALUES (1,55) ON DUPLICATE KEY UPDATE url=VALUES(url);執行后url的值等于55

總結

• 這三種方法都能避免主鍵或者唯一索引重復導致的插入失敗問題。
• insert ignore能忽略重復數據，只插入不重復的數據。
• replace into和insert ... on duplicate key update，都是替換原有的重復數據， 區別在于replace into是刪除原有的行后，在插入新行，如有自增id，這個會造成自增id的改變； insert ... on duplicate key update在遇到重復行時，會直接更新原有的行，具體更新哪些字段怎么更新，取決于update后的語句。

附：

有人對ON DUPLICATE KEY UPDATE 方法和 使用Replace在效率上的表現進行過研究，這里我直接給大家總結下結論吧:

• 在數據庫數據量很少的時候，　這兩種方式都很快，無論是直接的插入還是有沖突時的更新，都不錯
• 如果只是插入操作，比如直接向表里插入１０００條數據(百萬級的表(innodb引擎))，二者都差不多需要５，６甚至十幾秒；可見插入效率也是半斤八兩
• 更新表的時候replace的操作要比insert on duplicate的效率低很多，因為replace會先執行刪除后執行插入，插入的主鍵id是自增的(不復用舊的)，insert　on duplicate　更新對主鍵索引沒有影響.因此對索引的維護成本就低了一些(如果更新的字段不包括主鍵，那就要另說了)

總結

以上是生活随笔為你收集整理的的table数据重复添加_Mysql的奇技淫巧 - 避免重复插入数据的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。