• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySQL AUTO_INCREMENT column not uniform

 
Rujitha Patel
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
MySQL database AUTO_INCREMENT (INT type) columns are not increasing uniformly. Ther are increasing like 0,1,2,3,7,8,9,16,17,18...

Why this happens? Will there be any problems of having a non uniform of data column as a Primary Key? How can I have a uniformly AUTO_INCREMENT column?

Thank you.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would guess it is because the "missing" ids came when an insert operation failed or was aborted? e.g. the auto_increment functionality was triggered then the insert didn't happen.

I don't know how auto_imcrement works in mysql, but it is common for databases to implement primary key generation mechanisms with spaces between the ids. Oracle sequences for example are not uniform, nor are SQL Server identity columns. In both cases, it is the performance trade off gained by grabing an id a "safe" distance from the last one and the overhead required to keep the values contiguous.

That aside, these are surrogate keys and have no business meaning, so what they are does not matter. All that matters is they are unique and unchanging.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic