查看步长mysql,如何确定MySQL auto_increment步长

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.

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.

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.

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.

thanks!

解决方案

Several suggestions of why this may be happening:

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.