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

来源:http://www.cnblogs.com/kings-9/archive/2017/10/05/7630101.html
-Advertisement-
Play Games

本文參考鏈接:http://blog.csdn.net/jbboy/article/details/46828917 【本人認為改文章比較適合學習和參考,值得一看】 本文章來給大家提供三種在mysql中避免重覆插入記錄方法,主要是講到了ignore,Replace,ON DUPLICATE KEY ...


本文參考鏈接:http://blog.csdn.net/jbboy/article/details/46828917

【本人認為改文章比較適合學習和參考,值得一看】

 

本文章來給大家提供三種在mysql中避免重覆插入記錄方法,主要是講到了ignore,Replace,ON DUPLICATE KEY UPDATE三種方法,各位同學可嘗試參考。

案一:使用ignore關鍵字

如果是用主鍵primary或者唯一索引unique區分了記錄的唯一性,避免重覆插入記錄可以使用:

 代碼如下 複製代碼
1 INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('[email protected]', '99999', '9999');

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

還有個應用就是複製表,避免重覆記錄:

 代碼如下 複製代碼
1 INSERT IGNORE INTO `table_1` (`name`) SELECT `name` FROM `table_2`;

方案二:使用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(添加)或更大(替換)。

示例:
# eg:(phone欄位為唯一索引)

 代碼如下 複製代碼

REPLACE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test569', '99999', '123');

另外,在 SQL Server 中可以這樣處理:

 代碼如下 複製代碼

if not exists (select phone from t where phone= '1')   insert into t(phone, update_time) values('1', getdate()) else    update t set update_time = getdate() where phone= '1'

更多信息請看:http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#replace

方案三:ON DUPLICATE KEY UPDATE

如‍上所寫,你也可以在INSERT INTO…..後面加上 ON DUPLICATE KEY UPDATE方法來實現。如果您指定了ON DUPLICATE KEY UPDATE,並且插入行後會導致在一個UNIQUE索引或PRIMARY KEY中出現重覆值,則執行舊行UPDATE。

例如,如果列a被定義為UNIQUE,並且包含值1,則以下兩個語句具有相同的效果:

 代碼如下 複製代碼

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

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

註釋:如果列b也是唯一列,則INSERT與此UPDATE語句相當:

 代碼如下 複製代碼

UPDATE `table` SET `c`=`c`+1 WHERE `a`=1 OR `b`=2 LIMIT 1;

如果a=1 OR b=2與多個行向匹配,則只有一個行被更新。通常,您應該儘量避免對帶有多個唯一關鍵字的表使用ON DUPLICATE KEY子句。

您可以在UPDATE子句中使用VALUES(col_name)函數從INSERT…UPDATE語句的INSERT部分引用列值。換句話說,如果沒有發生重覆關鍵字衝突,則UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函數特別適用於多行插入。VALUES()函數只在INSERT…UPDATE語句中有意義,其它時候會返回NULL。

 代碼如下 複製代碼

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

本語句與以下兩個語句作用相同:

 代碼如下 複製代碼

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

註釋:當您使用ON DUPLICATE KEY UPDATE時,DELAYED選項被忽略。

示例:
這個例子是我在實際項目中用到的:是將一個表的數據導入到另外一個表中,數據的重覆性就得考慮(如下),唯一索引為:email:

 代碼如下 複製代碼

INSERT INTO `table_name1` (`title`, `first_name`, `last_name`, `email`, `phone`, `user_id`, `role_id`, `status`, `campaign_id`) 
    SELECT '', '', '', `table_name2`.`email`, `table_name2`.`phone`, NULL, NULL, 'pending', 29 FROM `table_name2` 
    WHERE `table_name2`.`status` = 1 
ON DUPLICATE KEY UPDATE `table_name1`.`status`='pending'

再貼一個例子:

 代碼如下 複製代碼

1 INSERT INTO `class` SELECT * FROM `class1` ON DUPLICATE KEY UPDATE `class`.`course`=`class1`.`course`

其它關鍵:DELAYED  做為快速插入,並不是很關心失效性,提高插入性能。 
IGNORE  只關註主鍵對應記錄是不存在,無則添加,有則忽略。

更多信息請看:  http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert

特別說明:在MYSQL中UNIQUE索引將會對null欄位失效,也就是說(a欄位上建立唯一索引):

 代碼如下 複製代碼

1 INSERT INTO `test` (`a`) VALUES (NULL);

是可以重覆插入的(聯合唯一索引也一樣)。

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • CATiledLayer 有些時候你可能需要繪製一個很大的圖片,常見的例子就是一個高像素的照片或者是地球錶面的詳細地圖。iOS應用通暢運行在記憶體受限的設備上,所以讀取整個圖片到記憶體中是不明智的。載入大圖可能會相當地慢,那些對你看上去比較方便的做法(在主線程調用UIImage的-imageNamed: ...
  • CAReplicatorLayer CAReplicatorLayer的目的是為了高效生成許多相似的圖層。它會繪製一個或多個圖層的子圖層,併在每個複製體上應用不同的變換。看上去演示能夠更加解釋這些,我們來寫個例子吧。 重覆圖層(Repeating Layers) 清單6.8中,我們在屏幕的中間創建了 ...
  • 1.安裝JDK 下載: Oracle官網上下載jdk,需要點擊accept licence的才能下載,使用下麵的命令,直接可以下載。 wget --no-check-certificate --no-cookies --header "Cookie: oraclelicense=accept-sec... ...
  • oracle約束狀態有幾個項目,會讓人迷惑,分別是: 所以,通過簡單的實驗,來確認它們之間的區別。 以下實驗在12.2.0.1中進行。 通過試驗可以獲得幾點: 在實際情況中,常常會遇到那麼一些人有一些習慣:不對欄位或者表做任何約束。 好處是:節約了表設計時間,減少了變更說需要耗費的時間。 壞處是:終 ...
  • 圖示全連接層 如上圖所示,該全鏈接層輸入n * 4,輸出為n * 2,n為batch 該層有兩個參數W和B,W為繫數,B為偏置項 該層的函數為F(x) = W*x + B,則W為4 * 2的矩陣,B 為 1 * 2 的矩陣 從公式理解全連接層 假設第N層為全連接層,輸入為Xn,輸出為Xn+1,其他與 ...
  • 用workbench導入csv數據,只能導入數據的第一行,也就是標註每一列的列名的那一行。但問題是,每次導入完成時,系統提示已經導入了500條記錄(這個文件中的確有500條記錄),可是刷新資料庫後打開這張表,裡面還是只有上面說的那一行。 鄙人還發現在導入設置【field separator】那一欄, ...
  • 設置PL/SQL語句快捷輸入的方法,讓你成為高效率的人。 1.打開PL/SQL,輸入用戶並登錄 2.並打開Tools->Preferences->Editor->AutoReplace->選上Enabled 3.打開Editor,輸入下麵設置語句: sf=select * from scf=sele ...
  • 0.目錄 1. "前言" 2. "通過SSMS修改數據" 3. "通過SQL語句修改數據" 3.1 "修改單列數據" 3.2 "修改多列數據" 1.前言 增刪改查都是對數據的操作,其中“改”對應的SQL語句便是“update”,也就是“更新”的意思。 本篇主要介紹數據的修改,分別使用SSMS/SQL ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...