如何确定MySQL auto_increment步长 [英] How is the MySQL auto_increment step size determined
问题描述
我有一个网页表，其主键是URL的哈希，还有一个auto_increment ID列，它也是唯一键.
I have a table of web pages, with the primary key as hash of the URL, and an auto_increment ID column, which is also a unique key.
我有点困惑的是为什么连续的插入不会使ID字段增加1.当我第一次创建表并进行单个插入时，第一个id是1.产生的第二个插入和id是5，第三个是8.
What I'm a bit confused by is why successive inserts don't increment the ID field by 1. When I first created the table and did a single insert, the first id was 1. The second insert produced and id of 5 and the third was 8.
我在表上有一个触发器，该触发器在插入时会计算网页URL的哈希值.不确定是否相关.
I have a trigger on the table which, on insert, computes the hash of the URL of the webpage. Not sure if that's relevant or not.
有间隙不是问题，但我想了解为什么连续的插入不生成步长为1的ID.
It's not a problem to have gaps, but I would like to understand why successive inserts don't generate IDs with a step size of 1.
谢谢！
推荐答案
关于可能发生这种情况的一些建议:
Several suggestions of why this may be happening:
请参见 auto_increment_increment .每次在INSERT过程中请求一个新值时，它都会控制增量.
See auto_increment_increment. This controls the incrementation each time a new value is requested during INSERT.
Also if you use InnoDB tables in MySQL 5.1, they optimized auto-inc allocation so that it locks the table for a shorter duration. This is good for concurrency, but it can also "lose" auto-inc values if the INSERT of a row conflicts with another constraint such as a secondary UNIQUE column or a foreign key. In those cases, the auto-inc value allocated is not pushed back into the queue, because we assume another concurrent thread may have already allocated the next auto-inc value.
当然也会发生回滚，在这种情况下，可以分配但放弃自动增量值.
Of course rollbacks also occur, in which case an auto-inc value may be allocated but discarded.
这篇关于如何确定MySQL auto_increment步长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
