防止 InnoDB 自增 ON DUPLICATE KEY

Question

我目前遇到了一个主键为ID的问题，该主键已被设置成自动增长auto increment。但是它在执行ON DUPLICATE KEY时仍然会不断递增。

例如：

ID | field1     | field2

1  | user       | value

5  | secondUser | value

86 | thirdUser  | value

从上面的描述中，您会注意到在该表中有3个输入，但由于每次更新时自动递增，第三个输入的ID为86。

有没有办法避免这种情况？

下面是我的MySQL查询代码：

INSERT INTO table ( field1, field2 ) VALUES (:value1, :value2)
            ON DUPLICATE KEY
            UPDATE field1 = :value1, field2 = :value2 

这是我的表格外观；

CREATE TABLE IF NOT EXISTS `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field1` varchar(200) NOT NULL,
  `field2` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `field1` (`field1`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

- 0x0

---

不应该强制要求顺序ID必须是连续的，只需要保证唯一即可......如果您需要顺序ID，则说明您的设计存在问题。 - Mark Baker

@MarkBaker 我已经编辑了帖子，包括我的表设计结构。 - 0x0

在您的表格上创建另一列，您可以手动添加一个整数，然后您可以保持该列连续，例如sID列，然后您可以找到最大值，再加1以用于最新插入。 （您可能可以在SQL查询中自动执行此操作） - Martin

@youngbobby 如果有一个答案对你有帮助，请选择它（如果愿意，也可以点赞）。研究、测试和撰写这些答案需要时间。问完问题就走人不太好哦。 - BeetleJuice

Answer 1

你可以将 innodb_autoinc_lock_mode 配置选项设置为 "0" 以使用“传统”自增锁定模式，该模式保证所有的 INSERT 语句将为 AUTO_INCREMENT 列分配连续的值。

但是，请不要在应用程序中依赖自增 ID 的连续性，它们的目的是提供唯一标识符。

- Eugene Yarmash

---

谢谢提供链接。我不知道这是个选择。 - BeetleJuice

我正在使用共享主机 :( - 0x0

Answer 2

以下是使用默认设置 innodb_autoinc_lock_mode = 1（“连续”锁模式）时的行为表现。请参考名为 InnoDB中的AUTO_INCREMENT处理 的详细手册页面。将此值更改为“传统”锁定模式下的设置= 0将降低并发性和性能，因为它使用表级别的AUTO-INC锁定。

尽管如此，默认设置= 1的以下内容也说明了创建间隔有多容易。 示例1：

create table x
(   id int auto_increment primary key,
    someOtherUniqueKey varchar(50) not null,
    touched int not null,
    unique key(someOtherUniqueKey)
);
insert x(touched,someOtherUniqueKey) values (1,'dog') on duplicate key update touched=touched+1;
insert x(touched,someOtherUniqueKey) values (1,'dog') on duplicate key update touched=touched+1;
insert x(touched,someOtherUniqueKey) values (1,'cat') on duplicate key update touched=touched+1;

select * from x; 
+----+--------------------+---------+
| id | someOtherUniqueKey | touched |
+----+--------------------+---------+
|  1 | dog                |       2 |
|  3 | cat                |       1 |
+----+--------------------+---------+

由于INNODB引擎的一些操作、怪癖和紧张的抽搐，间隙（id=2被跳过）会出现。在其默认的高并发性能模式下，它为发送给它的各种查询执行范围间隙分配。除非你有充分的理由更改此设置，否则这样做会影响性能。MySQL的后续版本提供了您需要的功能，并且您可以关闭它们以便专注于打印纸张上的空隙（以及那些说“我们为什么有空隙”的老板）。

在插入重复键更新（IODKU）的情况下，它假定有1行新数据并为其分配一个插槽。请记住，这是并发操作，而且您的同行也可能同时进行数百个操作。当IODKU变成更新时，那么您的连接和其他人使用的ID为2的未使用和未插入的行就没用了。

在“Insert ... Select From”中也会发生同样的事情，如我在这个答案中所示。在此答案中，我故意使用MyISAM进行报告计数、最小值、最大值，否则范围间隙怪癖将分配但不填充所有内容。而且数字看起来很奇怪，因为该答案涉及实际数字。所以旧的引擎（MyISAM）对于没有间隙的紧密情况工作得很好。请注意，在那个答案中，我试图做一些快速而安全的事情，并且可以在事后使用ALTER TABLE将表转换为INNODB。如果我一开始就在INNODB中完成了这个示例，那么默认模式下会有大量的间隙。如果我在“Insert ... Select From”中使用INNODB，那么会在答案中创建间隙是由于计数的不确定性和引擎选择用于安全（不确定）范围分配的机制。INNODB引擎自然知道操作，知道它必须创建一个安全的AUTO_INCREMENT ID池，具有并发性（需要考虑其他用户），并且间隙繁荣。这是一个事实。尝试使用INNODB引擎进行示例2，看看你能得到什么样的最小值、最大值和计数。最大值将不等于计数。

Percona网站记录了导致INNODB间隙的各种情况，例如在1452错误图像中看到的由于外键约束而导致的插入失败，或者在1062错误图像中的主键错误。

请记住，INNODB间隙是系统性能和安全引擎的副作用。一个人真的想关闭它（性能、更高的用户满意度、更高的并发性、缺少表锁），为了更紧密的ID范围？在删除时也会留下空洞。对于我的实现，我建议不要关闭它，并且默认设置与性能相当好。

- Drew

Answer 3

我目前遇到了一个主键问题，即设置为自动递增的ID。在ON DUPLICATE KEY时它会不断递增。

我们中有人可能误解了问题，或者您描述不准确。 ON DUPLICATE KEY UPDATE永远不会创建新行，因此它不会递增。从文档中可以看出：

如果指定了ON DUPLICATE KEY UPDATE，并且插入了一行，该行将导致UNIQUE索引或PRIMARY KEY中出现重复值，则MySQL将更新旧行。

现在可能的情况是，在插入时发生自动递增，但没有找到重复键。如果我假设这就是问题所在，那么我的问题是：这有什么问题吗？

如果您绝对想要控制主键的值，请更改您的表结构以删除auto-increment标志，但保持它是一个必需的非空字段。这将强制您自己提供密钥，但我敢打赌，这将成为您更大的头痛。

我真的很好奇：您为什么需要填补所有ID值中的漏洞呢？

- BeetleJuice

---

你不能在真实系统中填补漏洞。因为在某个时刻，你会删除一行。而且你不能重新调整所有子表中的PK和FK（或者至少将一个ID作为热替换插入新的空洞）。不能就是不能，同时还要保持理智。所以那些没有强迫症的人都会放任自流。 - Drew

Answer 4

您可以更改查询语句为

INSERT INTO table ( f1, f2 ) VALUES (:v1, :v2) ON DUPLICATE KEY UPDATE f1 = :v1, f2 = :v2

到

insert ignore into table select (select max(id)+1 from table), :v1, :v2 ;

这将尝试

• 使用最后一个未使用的ID（非自动增量）插入新数据
• 如果在唯一字段中发现重复条目，则忽略它
• 否则正常插入新数据
  
  （但是，如果发现重复条目，则此方法不支持更新字段）

- a55

Answer 5

我在这里回答了这个问题： 为了解决自动递增问题，在插入/更新之前使用以下代码，并将它们一起执行：

    SET @NEW_AI = (SELECT MAX(`the_id`)+1 FROM `table_blah`);
    SET @ALTER_SQL = CONCAT('ALTER TABLE `table_blah` AUTO_INCREMENT =', @NEW_AI);
    PREPARE NEWSQL FROM @ALTER_SQL;
    EXECUTE NEWSQL; 

一起并在一个语句中应该是像下面这样的：

    SET @NEW_AI = (SELECT MAX(`the_id`)+1 FROM `table_blah`);
    SET @ALTER_SQL = CONCAT('ALTER TABLE `table_blah` AUTO_INCREMENT =', @NEW_AI);
    PREPARE NEWSQL FROM @ALTER_SQL;
    EXECUTE NEWSQL; 
    INSERT INTO `table_blah` (`the_col`) VALUES("the_value")
    ON DUPLICATE KEY UPDATE `the_col` = "the_value";

- ali asghar tofighian