MYSQL中無重復插入更新幾種方法

我們在mysql中有時為了防止重復插入記錄我們會要利用程序來判斷，當然我們也可以直接使用mysql一些語句來實現防止重復插入記錄的方法了，希望此方法對各位朋友有幫助。

第一種解決方案：

如果你指定了ON DUPLICATE KEY UPDATE命令語句，那麼在唯一索引或者主索引的作用下將不插入與數據庫記錄重復的內容，但同時會更新數據庫中的舊記錄。例如，字段a被聲明為唯一索引並且裡面只包含有值為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; 

一、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;

受影響的是a=1的行，當插入時c的值加1。
如果字段b也是唯一的話，這個插入語句將和以下語句的效果一樣：
UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2匹配了不止一行，也只是第一行被更新。一般地，如果表中有多個唯一索引的話，你應該避免在使用用ON DUPLICATE KEY子句。
你可以在插入更新語句 INSERT … UPDATE 中使用 VALUES(字段名) 函數去關聯某一行記錄。也就是說， VALUES(字段名) 可以用在UPDATE語句中去更新某字段的值而不會出現重復鍵。這個函數在多行插入中尤其有用。但是函數 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),(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; 

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;

如果表中包含有一個自動遞增字段AUTO_INCREMENT，並用 INSERT … UPDATE 插入一行，函數 LAST_INSERT_ID()會返回AUTO_INCREMENT的值，如果這個語句更新某一行， LAST_INSERT_ID() 就沒有意義了。但是，你可以通過用 LAST_INSERT_ID(expr)使它變得有意義，假如id字段是自動遞增欄的話，使 LAST_INSERT_ID() 對更新語句有意義的方法如下：

 代碼如下 復制代碼

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

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

如果你使用 ON DUPLICATE KEY UPDATE 語句的話，延遲執行選項 DELAYED 將被忽略。

第二種解決方案

這種解決方案比較通用，不過個人感覺性能不是很好（沒有測試）
示例一：插入多條記錄
假設有一個主鍵為 client_id 的 clients 表，可以使用下面的語句：

 代碼如下 復制代碼

INSERT INTO clients  
(client_id, client_name, client_type)  
SELECT supplier_id, supplier_name, 'advertising' 
FROM suppliers  
WHERE not exists (select * from clients  
where clients.client_id = suppliers.supplier_id); 

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);

示例二：插入單條記錄

 代碼如下 復制代碼

INSERT INTO clients  
(client_id, client_name, client_type)  
SELECT 10345, 'IBM', 'advertising' 
FROM dual  
WHERE not exists (select * from clients  
where clients.client_id = 10345); 

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

使用 dual 做表名可以讓你在 select 語句後面直接跟上要插入字段的值，即使這些值還不存在當前表中。

第三種解決方案

REPLACE語法

replace的語法格式為：

 代碼如下 復制代碼

1. replace into table_name(col_name, …) values(…)

2. replace into table_name(col_name, …) select …

3. replace into table_name set col_name=value, …

算法說明：

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

1. 嘗試把新行插入到表中

2. 當因為對於主鍵或唯一關鍵字出現重復關鍵字錯誤而造成插入失敗時：

從表中刪除含有重復關鍵字值的沖突行

再次嘗試把新行插入到表中

舊記錄與新記錄有相同的值的判斷標准就是：表有一個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′