mysql into语法 - “INSERT IGNORE”与“INSERT...ON DUPLICATE KEY UPDATE”




insert多条 insert批量 (9)

一些重要的补充:当使用INSERT IGNORE并且确实存在关键违例时,MySQL不会发出警告!

例如,如果您尝试一次插入100条记录,而一条记录错误,则会进入交互模式:

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

正如你所看到的:没有警告! 这种行为甚至在官方的Mysql文档中被错误地描述。

如果你的脚本需要被告知,如果一些记录还没有被添加(由于关键违规),你必须调用mysql_info()并解析它以获得“Duplicates”值。

在执行有多行的INSERT语句时,我想跳过会导致失败的重复条目。 经过一番研究,我的选择似乎是使用以下两种方法之一:

  • ON DUPLICATE KEY UPDATE ,这意味着一些代价不必要的更新,或者
  • INSERT IGNORE暗示其他类型的失败的邀请未经宣布。

我在这些假设中是否正确? 简单地跳过可能导致重复的行并继续到其他行的最佳方法是什么?


我知道这是旧的,但我会添加此笔记,以防其他人(如我)在试图在INSERT..IGNORE上查找信息时到达此页面。

如上所述,如果使用INSERT..IGNORE,则执行INSERT语句时发生的错误将被视为警告。

没有明确提到的一件事是INSERT..IGNORE将导致插入时将无效值调整为最接近的值(而如果不使用IGNORE关键字,无效值将导致查询中止)。


我会建议使用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 ,这听起来就像你正在寻找的那种行为。 只要你知道那些会引起索引冲突的行将不会被插入,并且你相应地计划你的程序,它应该不会造成任何麻烦。


在DUPLICATE KEY UPDATE中并不是真正的标准。 这与REPLACE的标准一致。 请参阅SQL MERGE

基本上这两个命令都是标准命令的替代语法版本。


如果要在表中插入主键或唯一索引的冲突,它将更新有冲突的行,而不是插入该行。

句法 :

插入到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”。

所以,如果你想插入一个新的行,否则更新该行的主键或唯一索引的冲突。 阅读更多关于此链接


如果使用带有SHOW WARNINGS; insert ignore SHOW WARNINGS; 查询集结尾处的语句将显示包含所有警告的表格,其中包括哪些ID是重复的。


如果你想看看这一切意味着什么,这里是一切的一击:

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 INTO TABLE (id, name, age) VALUES (1, "A", 19), (2, "B", 17), (3, "C", 22)
ON DUPLICATE KEY UPDATE
    name = VALUES (name),
    ...




mysql insert