insert - “INSERT IGNORE” vs “INSERT …插入重复

  显示原文与译文双语对照的内容

执行带有许多行的INSERT 语句时,我希望跳过重复的条目,否则会导致失败。 经过一些研究,我的选项似乎是使用:

  • ON DUPLICATE KEY UPDATE 这意味着不必要的更新,或者
  • INSERT IGNORE,它暗示了其他类型的失败在未通知的情况下的邀请。

这些假设是正确的? 跳过可能引起重复的行,然后继续其他行的最佳方法是什么?

时间:

我建议使用 INSERT...ON DUPLICATE KEY UPDATE

如果你使用 INSERT IGNORE,那么如果它产生重复的键,该行将不会被插入。 但该语句不会生成错误。 它生成一个警告。 这些情况包括:

  • 在带有 PRIMARY KEY 或者 UNIQUE 约束的列中插入重复键。
  • 在带有 NOT NULL 约束的列中插入空值。
  • 将一行插入分区表,但插入的值不映射到分区。

如果你使用 REPLACE,MySQL实际上会做一个 DELETE,后面是一个 INSERT,它有一些意想不到的副作用:

  • 分配了一个新的auto-increment标识。
  • 带有外键的依存行可能被删除( 如果使用级联外键) 或者防止 REPLACE
  • DELETE 上激发的触发器不需要执行。
  • 副作用也传播到复制奴隶。

校正:同时REPLACEINSERT...ON DUPLICATE KEY UPDATE 是non-standard特有的专有发明。 ANSI SQL 2003定义了一个 MERGE 语句,它可以解决相同的需求( 等等),但是MySQL不支持 MERGE 语句。


用户试图编辑这里日志( 编辑被版主拒绝) 。 编辑试图添加一个声明 INSERT...ON DUPLICATE KEY UPDATE 导致分配新的auto-increment标识。 确,新的标识是生成的,但在已经更改的行中没有使用。

查看下面的演示,用Percona服务器 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 fooG
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 fooG
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 表示生成了一个标识,但在行中没有使用。

REPLACE 并删除原来的行并插入新的行,生成和存储一个新 auto-increment id:


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

如果你想了解这一切的含义,这里有一个 blow-by-blow:


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

所以你有它。 因为这都是在一个几乎没有数据的新表格上执行的,所以执行的时间是微观和无关的。 任何有真实数据的人都会更愿意贡献它。

重要的补充:当使用INSERT忽略并且你确实有密钥冲突时,MySQL不会引发警告 !

如果你尝试一次插入 100条记录,其中一个记录有一个错误的,那么你将进入交互模式:

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

就像你所见:没有警告 ! 这种行为甚至在官方 MySQL Docoumentation中得到了错误的描述。

于该"重复"相关value,如果你的脚本需要得到通知,如果某些记录还没有添加( 由于密钥冲突) 你必须调用 mysql_info() 然后解析上面,

我经常使用 INSERT IGNORE,它听起来像你正在寻找的那种行为。 只要你知道导致索引冲突的行不会被插入,并且你计划相应的程序,就不会造成任何问题。

...