“INSERT IGNORE”vs“INSERT ... ON DUPLICATE KEY UPDATE”

10 回答

• 17

  Replace进入似乎是一种选择 . 或者您可以查看

  IF NOT EXISTS(QUERY) Then INSERT
  

  这将插入或删除然后插入 . 我倾向于首先进行 IF NOT EXISTS 检查 .

  回复于 2024-04-29T20:31:52+08:00
• 8

  如果在查询集的末尾使用带有 SHOW WARNINGS; 语句的 insert ignore 将显示包含所有警告的表，包括哪些ID是重复项 .

  回复于 2024-04-29T20:31:52+08:00
• 161

  我知道这是旧的，但是我会添加这个注释，以防其他人（比如我）在尝试查找有关INSERT..IGNORE的信息时到达此页面 .

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

  未明确提及的一件事是INSERT..IGNORE将导致无效值在插入时将被调整为最接近的值（而如果未使用IGNORE关键字，则无效值将导致查询中止） .

  回复于 2024-04-29T20:31:52+08:00
• 2

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

  如果您使用 INSERT IGNORE ，则该行赢得't actually be inserted if it results in a duplicate key. But the statement won' t会生成错误 . 它会生成警告 . 这些案件包括：

  • 在具有 PRIMARY KEY 或 UNIQUE 约束的列中插入重复键 .

  • 将NULL插入到具有 NOT NULL 约束的列中 .

  • 将行插入分区表，但插入的值不会映射到分区 .

  如果您使用 REPLACE ，MySQL实际上会在内部执行 DELETE INSERT ，这会产生一些意想不到的副作用：

  • 分配新的自动增量ID .

  • 可能会删除带有外键的相关行（如果使用级联外键）或者阻止 REPLACE .

  • 在 DELETE 上触发的触发器被不必要地执行 .

  • 副作用也传播到复制从属 .

  correction: REPLACE 和 INSERT...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 |
  +----+------+
  

  回复于 2024-04-29T20:31:52+08:00
• 930

  我经常使用 INSERT IGNORE ，这听起来就像你导致任何麻烦的那种行为 .

  回复于 2024-04-29T20:31:52+08:00
• 6

  ON DUPLICATE KEY UPDATE不是真正的标准 . 它和REPLACE一样标准 . 见SQL MERGE .

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

  回复于 2024-04-29T20:31:52+08:00
• 2

  如果你想看看这一切意味着什么，这里是一切的吹嘘：

  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
  

  所以你有它 . 由于这一切都是在几乎没有数据且没有 生产环境 的新 table 上进行的，因此执行的时间是微观的，无关紧要的 . 任何拥有真实数据的人都非常欢迎您提供 .

  回复于 2024-04-29T20:31:52+08:00
• 17

  INSERT IGNORE的潜在危险 . 如果您尝试插入更长的VARCHAR值，则定义列为 - 将截断并插入值即使启用严格模式也是如此 .

  回复于 2024-04-29T20:31:52+08:00
• 3

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

  Syntax:

  insert into table1 set column1 = a, column2 = b on duplicate update column2 = c;
  

  现在，这个insert语句看起来可能与前面看到的不同 . 此insert语句尝试将table1中的行与a和b的值插入列column1和column2中分别 .

  让我们深入理解这个陈述：

  例如：此处column1被定义为table1中的主键 .

  现在，如果在table1中，第1列中没有值为“a”的行 . 所以这个语句会在table1中插入一行 .

  现在，如果在table1中，在column2中有一行值为“a” . 因此，此语句将使用“c”更新行的column2值，其中column1值为“a” .

  因此，如果要插入新行，则在主键或唯一索引的冲突上更新该行 .
  Read more on this link

  回复于 2024-04-29T20:31:52+08:00
• 38

  要添加的重要内容：使用INSERT IGNORE并确实存在密钥违规时，MySQL不会发出警告！

  如果您尝试一次插入100条记录，一条有故障，则可以进入交互模式：

  查询正常，99行受影响（0.04秒）记录：100重复：1警告：0

  如你所见：没有警告！在官方的Mysql文档中甚至错误地描述了这种行为 .

  如果需要通知您的脚本，如果没有添加某些记录（由于密钥违规），您必须调用mysql_info（）并解析它的“Duplicates”值 .

  回复于 2024-04-29T20:31:52+08:00