您現在的位置: 365建站網 > 365學習 > 批量insert into時防止更新插入重復數據去重的方法

批量insert into時防止更新插入重復數據去重的方法

文章來源:365jz.com     點擊數:4225    更新時間:2020-06-08 19:24   參與評論

mySQL中批量insert into時防止更新插入重復數據去重的方法,主要是講到了ignore,Replace,ON DUPLICATE KEY UPDATE三種方法


方案一:使用ignore關鍵字

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

INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('www@365jz.com', '13112345678', '9999');

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

還有個應用就是復制表,避免重復記錄:

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 ('www@365jz.com', '13112345678', '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'


方案三: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'


再貼一個例子:

復制代碼代碼如下:

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

其它關鍵:DELAYED  做為快速插入,并不是很關心失效性,提高插入性能。 

IGNORE  只關注主鍵對應記錄是不存在,無則添加,有則忽略。

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


復制代碼代碼如下:

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

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


在插入大量數據時可能同時遇到兩個問題:

如果每條更新執行一次sql性能很低,也容易造成阻塞;

批量插入時又有可能遇到主鍵 或者 唯一索引重復的問題,導致整個批量插入失敗

使用 ON DUPLICATE KEY UPDATE 一條sql解決批量插入和主鍵或者唯一索引重復問題

舉例如下:

表結構如下:

create table mytable(
id int not null,
code char(6) not null,
description char(50) null,
number int not null,
name char(50) not null,
primary key id,
unique key uk_mytable_code (code));

批量插入如下:

INSERT INTO mytable(id, code, description, number, name)
VALUES (?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?)
ON DUPLICATE KEY UPDATE description = VALUES(description), number = VALUES(number)

//description=VALUES(description),number = VALUES(number) 表示出現在values中某列的id字段值與表中已有id字段值重復

(或者code的值與表中已有code字段值重復)時,會更新對應已存在記錄的這兩個字段

//還可以指定其它值或進行運算:number=number+ 1,number=number- 1

//因為這里未指定列name, 所以遇到重復id(或者code)列,表中該列的name字段不會更新

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


Sqlite 批量操作時防止插入重復數據的方法:

SQLITE中,可以用以下方法防止插入重復數據,

1.設置表的聯合主鍵

2.在INSERT語句中添加OR IGNORE,即可(重復數據會跳過,且不影響后續數據插入)

示例如下:

insert OR IGNORE into PDA_Test (DeviceID,MethodName) values('1112','測試原有');

 

sqlite避免重復插入數據。

SQLite 數據庫常用約束如下:

NOT NULL - 非空
UNIQUE - 唯一
PRIMARY KEY - 主鍵
FOREIGN KEY - 外鍵
CHECK - 條件檢查
DEFAULT - 默認

用insert語句插入數據,為避免重復插入又不打斷數據處理。

首先要避免重復插入,就必須在插入時引發沖突。在表中設置了id字段,該字段為UNIQUE屬性,當插入的id已存在時引發沖突。

引發沖突后insert會做一些處理,處理方式由OR字句定義。包含如下:

ROLLBACK當發生約束沖突,立即ROLLBACK,即結束當前事務處理,命令中止并返回SQLITE_CONSTRAINT代碼。若當前無活動事務(除了每一條命令創建的默認事務以外),則該算法與ABORT相同。
ABORT當發生約束沖突,命令收回已經引起的改變并中止返回SQLITE_CONSTRAINT。但由于不執行ROLLBACK,所以前面的命令產生的改變將予以保留。缺省采用這一行為。
FAIL當發生約束沖突,命令中止返回SQLITE_CONSTRAINT。但遇到沖突之前的所有改變將被保留。例如,若一條UPDATE語句在100行遇到沖突100th,前99行的改變將被保留,而對100行或以后的改變將不會發生。
IGNORE當發生約束沖突,發生沖突的行將不會被插入或改變。但命令將照常執行。在沖突行之前或之后的行將被正常的插入和改變,且不返回錯誤信息。
REPLACE當發生UNIQUE約束沖突,先存在的,導致沖突的行在更改或插入發生沖突的行之前被刪除。這樣,更改和插入總是被執行。命令照常執行且不返回錯誤信息。當發生NOT NULL約束沖突,導致沖突的NULL值會被字段缺省值取代。若字段無缺省值,執行ABORT算法


Sqlite執行insert or ignore 或insert or replace語句。

insert or replace into cardlog (mid,type) values (7,0);
insert or ignore into cardlog (mid,type) values (7,0);

 上面的第一條語句是每次執行時,如果不存在,則添加,如果存在,則更新。

上面的第二條語句是每次執行時,如果不存在,則添加,如果存在,則不操作。

在MSSQL中,你可以使用諸如:

IF NOT EXISTS(SELECT * FROM Book WHERE ….) THEN INSERT INTO ... ELSE UPDATE SET ...


如對本文有疑問,請提交到交流論壇,廣大熱心網友會為你解答??! 點擊進入論壇


發表評論 (4225人查看,0條評論)
請自覺遵守互聯網相關的政策法規,嚴禁發布色情、暴力、反動的言論。
用戶名: 驗證碼: 點擊我更換圖片
最新評論
------分隔線----------------------------
自拍偷拍福力视频,偷拍国际精品,麻豆一区福利电影,国产网红视频午夜福利,se视频大全,久久国产AV影院 欧美40老熟妇| 夜夜欢| 日本熟妇牲交视频| 成为人视频免费视频免费观看| baoyu.tv.777尤物| 一道本久在线久久综合| 电影在线观看2021av| 十八禁啪啦拍无遮拦视频| 和风韵犹存的熟妇乱爱| 亚洲大尺度无码无码专线| jiZz中国jiZZ| 国产人人看人人拍视频| 成年无码av片在线观看| 在线v片免费观看视频| 日本系列有码字幕中文字幕| 小草在线视频观看免费观看| 少妇无码精品12p| 老司机导航亚洲精品导航| 真实处破女刚成年| gogo销魂裸体艺术| 乱伦大杂烩| 人妻无码av中文系列久久免费| 山东熟女啪啪哦哦叫| 国产亚洲欧美在线专区| 久草视频| 被窝福利最新线视频| 动态图出处第900期| 老司机视频精品| 老熟妇大胆性开放图| china国语对白刺激videos| 欧美高潮抽搐喷水大叫| http://www.maruokashoten.com