为什么mysql跳过一些自动增量ID? [英] Why does mysql skip some auto increment ids?

问题描述

我已经看到许多解释，为什么mysql跳过一些自动增量值(并且这不是bug，因为mysql从未声明它们应该是连续的).我不确定为什么其中任何一个都适用于这个简单的测试用例.我不知道结果是否在所有最新版本的mysql上都相同.还添加:

I have seen many explanations why mysql skips some auto increment values (and that it is not a bug as mysql never states that they should be consecutive). I am not sure why any of them would be applicable to this simple test case. I don't know if the results would be the same on all recent versions of mysql or not. Also adding:

ALTER TABLE test_table2 AUTO_INCREMENT = 1;

在2条INSERT INTO test_table2行之间按预期顺序进行.

between the 2 INSERT INTO test_table2 lines makes the order as expected.

有人知道为什么这种简单的情况会跳过ID 6和7吗?

Does anyone know why this simple case would skip ids 6 and 7?

CREATE TABLE test_table1 (
  `id` INT NOT NULL AUTO_INCREMENT,
  `test` TEXT NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO test_table1(`test`) VALUES('value 1');
INSERT INTO test_table1(`test`) VALUES('value 2');
INSERT INTO test_table1(`test`) VALUES('value 3');
INSERT INTO test_table1(`test`) VALUES('value 4');
INSERT INTO test_table1(`test`) VALUES('value 5');
CREATE TABLE test_table2 (
  `id` INT NOT NULL AUTO_INCREMENT,
  `test` TEXT NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO test_table2(`test`) SELECT `test` FROM test_table1;
INSERT INTO test_table2(`test`) SELECT `test` FROM test_table1;
SELECT * FROM test_table2;

我的mysql版本上的结果:

Results on my version of mysql:

'1', 'value 1'
'2', 'value 2'
'3', 'value 3'
'4', 'value 4'
'5', 'value 5'
'8', 'value 1'
'9', 'value 2'
'10', 'value 3'
'11', 'value 4'
'12', 'value 5'

谢谢.

推荐答案

这是InnoDB中自动增量锁定的一个示例:当您在同一会话中同时执行2条语句时:自动增量锁定是通过第一个查询获得的，并且自动增量值的生成不会在语句之间交错-这是整个交易的重点.

It's an example of auto increment locking in InnoDB: As you are executing 2 statements concurrently in the same session: the auto inc lock is obtained by the first query, and the autoincrement value generation is not interleaved between the statements - that is the whole point of transaction.

这总是设计使然:如果不这样做，InnoDB中的事务正常工作的方式将行不通.在OLTP类型的负载下，可伸缩性将是可怕的，因为每个插入都必须等待其他每个插入完成，提交或更糟的情况-回滚.

This will always happen by design: If it didn't, the way the transactions in InnoDB work, well, wouldn't work. Scalability under OLTP type loads would be horrible as every insert would have to wait for every other insert to finish, be committed, or worse - rolled back.

即:如果您的第一个插入程序比第二个插入程序运行时间长5倍，并且失败并回滚，则第二个插入程序仍会完成并已提交.否则，您将不得不等待ea.查询要一个接一个地完成.

I.e.: If your first insert runs 5x longer than your second, and fails and is rolled back, the second one still completes and has been committed. Otherwise, you would have to wait for ea. query to be complete after the other.

如果您需要顺序且绝对唯一的ID号，请从另一个来源获取它们. AutoInc列只是保证了唯一的值-不一定是单序列-这是序列化和瓶颈所在.

If you require sequential and absolutely unique ID #s, pull them from another source. AutoInc columns simply guarantee a unique value - not necessarily a monosequence - that is a point of serialization and a bottleneck.

如果没有其他要求，可以采用以下方法解决此问题:

One way around this if otherwise required:

设置innodb_autoinc_lock_mode = 0

InnoDB和选项中自动公司锁定的说明

这篇关于为什么mysql跳过一些自动增量ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！