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