update - python mysql insert




“INSERT IGNORE”與“INSERT... ON DUPLICATE KEY UPDATE” (7)

在執行有多行的INSERT語句時,我想跳過會導致失敗的重複條目。 經過一番研究,我的選擇似乎是使用以下兩種方法之一:

  • ON DUPLICATE KEY UPDATE ,這意味著一些代價不必要的更新,或者
  • INSERT IGNORE暗示其他類型的失敗的邀請未經宣布。

我在這些假設中是否正確? 簡單地跳過可能導致重複的行並繼續到其他行的最佳方法是什麼?


INSERT IGNORE的潛在危險。 如果您嘗試插入VARCHAR值的時間過長,則使用 - 定義列,則該值將被截斷並插入,即使已啟用嚴格模式。


Replace Into看起來像一個選項。 或者你可以檢查

IF NOT EXISTS(QUERY) Then INSERT

這將插入或刪除然後插入。 我傾向於首先進行IF NOT EXISTS檢查。


在DUPLICATE KEY UPDATE中並不是真正的標準。 這與REPLACE的標準一致。 請參閱SQL MERGE

基本上這兩個命令都是標準命令的替代語法版本。


如果你想看看這一切意味著什麼,這裡是一切的一擊:

CREATE TABLE `users_partners` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `pid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`,`pid`),
  KEY `partner_user` (`pid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

主鍵基於此快速參考表的兩列。 主鍵需要唯一值。

讓我們開始:

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...1 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1);
...0 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1) ON DUPLICATE KEY UPDATE uid=uid
...0 row(s) affected

注意,上面通過將列設置為等於自己來節省了太多的額外工作,實際上不需要更新

REPLACE INTO users_partners (uid,pid) VALUES (1,1)
...2 row(s) affected

現在有些多行測試:

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...3 row(s) affected

在控制台中沒有生成其他消息,並且它現在在表格數據中具有這4個值。 我刪除了除(1,1)之外的所有內容,所以我可以從同一個比賽場地進行測試

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4) ON DUPLICATE KEY UPDATE uid=uid
...3 row(s) affected

REPLACE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...5 row(s) affected

所以你有它。 由於這一切都是在一張幾乎沒有數據而沒有製作的新表上進行的,所以執行的時間是微觀的和不相關的。 任何擁有真實世界數據的人都將不勝感激。


如果要在表中插入主鍵或唯一索引的衝突,它將更新有衝突的行,而不是插入該行。

句法 :

插入到table1中設置column1 = a,column2 = b on dulplicate update column2 = c;

現在在這裡,這個插入語句可能與您以前看到的不一樣。 這個插入語句試圖在table1中插入一行,分別將a和b的值插入到column1和column2列中。

讓我們深入理解這個說法:

例如: - 這裡column1被定義為table1中的主鍵。

現在,如果在table1中沒有列1中具有值“a”的行。 所以這個語句將在table1中插入一行。

現在,如果在table1中有一列在第2列中具有值“a”。 因此,此語句將使用“c”更新行的column2值,其中column1值為“a”。

所以,如果你想插入一個新的行,否則更新該行的主鍵或唯一索引的衝突。 閱讀更多關於此鏈接


我會建議使用INSERT...ON DUPLICATE KEY UPDATE

如果使用INSERT IGNORE ,那麼如果該行導致重複鍵,則該行實際上不會被插入。 但該聲明不會產生錯誤。 它會生成警告。 這些情況包括:

  • 在具有PRIMARY KEYUNIQUE約束的列中插入重複鍵。
  • 將NULL插入到具有NOT NULL約束的列中。
  • 將行插入到分區表中,但插入的值不會映射到分區。

如果你使用REPLACE ,MySQL實際上會在內部INSERT DELETEINSERT ,這會產生一些意想不到的副作用:

  • 新的自動增量ID被分配。
  • 與外鍵相關的行可能會被刪除(如果使用級聯外鍵)或者阻止REPLACE
  • DELETE上觸發的觸發器被不必要地執行。
  • 副作用也傳播到復制從站。

更正: REPLACEINSERT...ON DUPLICATE KEY UPDATE都是非標準的,專用於MySQL的專有發明。 ANSI SQL 2003定義了一個MERGE語句,可以解決相同的需求(但更多),但MySQL不支持MERGE語句。

用戶試圖編輯這篇文章(編輯被版主拒絕)。 編輯試圖添加一個聲明,即INSERT...ON DUPLICATE KEY UPDATE導致一個新的自動遞增ID被分配。 確實生成了新的id,但它並未在更改的行中使用。

參見下面的示例,使用Percona Server 5.5.28進行測試。 配置變量innodb_autoinc_lock_mode=1 (默認值):

mysql> create table foo (id serial primary key, u int, unique key (u));
mysql> insert into foo (u) values (10);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   10 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

mysql> insert into foo (u) values (10) on duplicate key update u = 20;
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

上面演示了IODKU語句檢測到重複,並調用更新來更改u的值。 請注意, AUTO_INCREMENT=3表示已生成一個ID,但未在該行中使用。

REPLACE刪除原始行並插入新行,生成存儲新的自動遞增ID:

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  3 |   20 |
+----+------+

我經常使用INSERT IGNORE ,這聽起來就像你正在尋找的那種行為。 只要你知道那些會引起索引衝突的行將不會被插入,並且你相應地計劃你的程序,它應該不會造成任何麻煩。





insert