Why are there auto increment gaps in MySQL when vi

Question

I understand that when using INSERT ... ON DUPLICATE KEY UPDATE in MySQL there are auto increment gaps when an insert fails. However -- I've noticed that the gaps only occur when a unique key constraint is violated. If the primary key constraint fails, no auto increment gap occurs.

What's the reason for the difference between the two?

Thanks!

Answer 1

A new auto-increment value is generated only if you don't specify a value for the AI column.

mysql> create table MyTable (id int auto_increment primary key, x int, unique key(x));
mysql> insert into MyTable values (1, 100), (2, 200);

mysql> insert into MyTable (id) values (2);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

In this example, I can create a duplicate id value only if I specify a value in my INSERT. In this case, it skips allocating a new AI value.

Whereas if I create a duplicate value conflicting with my secondary unique column, I might not give a specific value for the id. This would cause the AI to generate a new value, but ultimately the INSERT fails because of the duplicate in the secondary column, and the AI value generated is lost.

mysql> insert into MyTable (x) values (200);
ERROR 1062 (23000): Duplicate entry '200' for key 'x'

Answer 2

If the auto increment field is the primary key, then duplication in the primary key can only happen if you explicitly set the auto increment field's value in the insert statement to an already existing value in that field (why you would do this is beyond me).

As mysql documentation on using auto increment says:

When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.

The point is, that in this case mysql does not try to generate a value for the auto increment column, but will use the value specified in the insert. Moreover, the sequence is also reset to the max value in the field, therefore the next insert will not have any gaps.

However, if another unique index constraint fails when you attempt the insert, then mysql has already generated the value for the auto increment field, therefore a gap is created in the auto increment sequence.