3

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!

Kyle Chadha
  • 3,741
  • 2
  • 33
  • 42
  • It's just the way it works. – Barmar Jan 02 '18 at 21:45
  • It's probably because the primary key has to be the auto-increment column, so it's treated specially. – Barmar Jan 02 '18 at 21:46
  • Is the auto increment field the primary key? Or is it part of the primary key and you specify its value as part of the insert? – Shadow Jan 02 '18 at 22:21
  • The auto increment field is the primary key – Kyle Chadha Jan 02 '18 at 23:15
  • My guess would be it checks the PK first, and if it is not violated reserves the next auto-inc value before it checks the remaining unique constraints; perhaps another insert can be processed while all this is happening, preventing the value from being "returned to the pool". – Uueerdo Jan 02 '18 at 23:25
  • 2
    @KyleChadha Shadow's answer is clear, `primary key constraint fails` means user defines the PK, so mysql will not generate a new value for PK(auto increment column), but `unique key constraint is violated` will. – Wang Wen'an Jan 03 '18 at 03:12

2 Answers2

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.

Shadow
  • 33,525
  • 10
  • 51
  • 64
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'
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828