Mysql避免重複插入記錄方法

標籤：payment   XML   pre   for   輸入   HERE   utf-8   聲明   插入   

一、mysql replace用法 

1.replace into 
replace into table (id,name)values(‘1‘,‘aa‘),(‘2‘,‘bb‘) 
此語句的作用是向表table中插入兩條記錄。如果主鍵id為1或2不存在 
就相當於 
insert into table (id,name)values(‘1‘,‘aa‘),(‘2‘,‘bb‘) 
如果存在相同的值則不會插入資料 

2.replace(object,search,replace) 

把object中出現search的全部替換為replace 

selectreplace(‘www.jb51.net‘,‘w‘,‘Ww‘)--->WwWwWw.jb51.net 

例：把表table中的name欄位中的aa替換為bb 

update table setname=replace(name,‘aa‘,‘bb‘) 

二、try  catch異常處理機制

declare continue handler for 1062 set done = 1

註：1062錯誤是主鍵欄位插入重複的錯誤。

 

三 、MySql之INSERT INTO…ON DUPLICATE KEY UPDATE

提供了insert into … on duplicate keyupdate的文法，該文法在insert的時候，如果insert的資料會引起唯一索引（包括主鍵索引）的衝突，即這個唯一值重複了，則不會執行insert操作，而執行後面的update操作。

注意：ONDUPLICATE KEY UPDATE只是MySQL的特有文法，並不是SQL標準文法！

例如，現在有表test，test表中有欄位a，在a上有主鍵或者唯一索引，並且表中只有一條a=1, b=1的資料，現在執行如下的sql：

insert into test (a,b) values (1,2) on duplicate key update b = b + 1;

註：直接update = ，不能用完整的update語句。

因為a=1的記錄已存在了，所以不會執行insert，而會在該條記錄上執行update語言`b=b+1`,記錄會變成a=1,b=2

insert into test (a,b) values (2,2) on duplicate key update b = b + 1;

因為a=2的記錄不存在，所以執行insert

如果行作為新記錄被插入，則受影響的行為1；如果原有記錄被更新，則受影響行為2；如果原有記錄已存在，但是更新的值和原有值相同，則受影響行為0。

多唯一索引衝突的情況說明：

為了測試方便，我們建了下面的資料表：

create table test(

a int not null primary key,

b int not null UNIQUE key,

c int not null

)

輸入資料：insert into test values(1,1,1), (2,2,2);

然後執行：insert into test values(1,2,3) on duplicate key update c = c + 1;

因為a和b都是唯一索引，插入的資料在兩條記錄上產生了衝突，然而執行後只有第一條記錄被修改：

mysql> select * from test;

+---+---+---+

| a | b | c |

+---+---+---+

| 1 | 1 | 2 |

| 2 | 2 | 2 |

+---+---+---+

2 rows in set (0.00 sec)

上面的語句等同於：update test set c=c+1 where a=1 or b = 2 limit 1;

如果a=1or b =2匹配多條記錄，只有第一條記錄被更新。所以，一般情況下，我們應該避免在有多個唯一索引的表中使用on duplicate key update。

 

使用values()方法，在update中可以使用values()方法引用在insert中的值，如：

insert into test values(1,3,5) on duplicate key update c = values( c )+ 1;

該語句會使a=1的記錄中c欄位的值更新為6，因為values(c)的值是引用的insert部分的值，在這個例子中就是insert into test values(1,3,5) 中的5，所以最終更新的值為6。

注意：VALUES()函數只在INSERT...UPDATE語句中有意義，其它時候會返回NULL。

last_insert_id()

如果表含有auto_increment欄位，使用insert … on duplicate key update插入或更新後，last_insert_id()返回auto_increment欄位的值。

並發控制

在使用例如MyISAM這樣的表級鎖的分區表上使用insert … on duplicate key update時，會鎖住所有分區表，而在例如使用InnoDB這樣的行級鎖的分區表上則不會鎖住所有分區表。

delayed選項

delayed選項會被忽略，當我們使用on duplicate key update時。

四：使用ignore關鍵字

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

INSERT IGNOREINTO `table_name` (`email`, `phone`, `user_id`) VALUES (‘[email protected]‘,‘99999‘, ‘9999‘);

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

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

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

最後貼上一個練習時的sp作為參考之用。

 

CREATE PROCEDURE Jack_xml_replace(xmlstr LONGTEXT,company_code int)BEGIN                declare parentpoint_count int default 0;   #聲明父節點個數    declare i int default 1;                            #迴圈次數，迴圈結束判定    declare  v_bankCd varchar(20);    declare     v_outletNo varchar(20);    declare     v_payAmount NUMERIC(10,1);    declare     v_paymentDate  varchar(20);    declare     v_paymentNum varchar(20);    declare     v_paymentType varchar(1);    declare     v_paymentUsage varchar(20);    declare     v_sourcecode varchar(20);    declare  v_transactionId varchar(255);    set parentpoint_count = ExtractValue(xmlstr,‘count(/o/payList/e)‘);   #擷取行數        #開始迴圈    while i <= parentpoint_count do         set  v_bankCd = ExtractValue(xmlstr,‘/o/payList/e[$i]/bankCd‘);        set     v_outletNo = ExtractValue(xmlstr,‘/o/payList/e[$i]/outletNo‘);        set     v_payAmount = ExtractValue(xmlstr,‘/o/payList/e[$i]/payAmount‘);        set     v_paymentDate  = ExtractValue(xmlstr,‘/o/payList/e[$i]/paymentDate‘);        set     v_paymentNum = ExtractValue(xmlstr,‘/o/payList/e[$i]/paymentNum‘);        set     v_paymentType = ExtractValue(xmlstr,‘/o/payList/e[$i]/paymentType‘);        set     v_paymentUsage = ExtractValue(xmlstr,‘/o/payList/e[$i]/paymentUsage‘);        set     v_sourcecode = ExtractValue(xmlstr,‘/o/payList/e[$i]/sourcecode‘);        set  v_transactionId = ExtractValue(xmlstr,‘/o/payList/e[$i]/transactionId‘);        update jack_xml a set a.bankCd = v_bankCd,outletNo = v_outletNo ,payAmount = v_payAmount ,paymentDate  = v_paymentDate ,paymentNum = v_paymentNum ,paymentType = v_paymentType ,paymentUsage = v_paymentUsage ,sourcecode = v_sourcecode         where a.company_code=company_code and a.transactionId=v_transactionId;        replace into jack_xml(company_code,bankCd,outletNo,payAmount,paymentDate,paymentNum,paymentType,paymentUsage,sourcecode,transactionId)         value(company_code,v_bankCd,v_outletNo,v_payAmount,v_paymentDate,v_paymentNum,v_paymentType,v_paymentUsage,v_sourcecode,v_transactionId);                set i=i+1;    end while;    select * from jack_xml;                        #查看實體表資料END# 運行參數CALL Jack_xml_replace(‘<?xml version="1.0" encoding="utf-8"?><o>  <payList class="array">    <e class="object">      <bankCd type="string">4</bankCd>      <outletNo type="string">0510020017</outletNo>      <payAmount type="number">200.0</payAmount>      <paymentDate type="string">20161212121212</paymentDate>      <paymentNum type="string">12323</paymentNum>      <paymentType type="string">Y</paymentType>      <paymentUsage type="string">B</paymentUsage>      <sourcecode type="string">CS</sourcecode>      <transactionId type="string">99B2E125-AB09-4AE3-9A05-5FE9876AF799</transactionId>    </e>    <e class="object">      <bankCd type="string">4</bankCd>      <outletNo type="string">0510020017</outletNo>      <payAmount type="number">300.0</payAmount>      <paymentDate type="string">20161112131415</paymentDate>      <paymentNum type="string">23232</paymentNum>      <paymentType type="string">Y</paymentType>      <paymentUsage type="string">B</paymentUsage>      <sourcecode type="string">SFA</sourcecode>      <transactionId type="string">99B2E125-AB09-4AE3-9A05-5FE9876AF798</transactionId>    </e> </payList></o>‘,3006); 

 

Mysql避免重複插入記錄方法