MySQL AUTO_INCREMENT column not uniform
Rujitha Patel
Greenhorn
Posts: 8
posted 9 years ago
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.
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.
posted 9 years ago
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.
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.
| It is sorta covered in the JavaRanch Style Guide. |