Do AUTO_INCREMENTS have to be sequential?

  • We have converted several tables in MySQL to InnoDB for performance reasons. (Note to others, not saying this will suit your environment, it just does for us).

    Question: will setting innodb_autoinc_lock_mode=2 cause problems? To put it another way, do AUTO_INCREMENT values need to be guaranteed and sequential, or if one is missing, will it matter?

    Bit more info: This will not perform a table lock when doing an INSERT… ON DUPLICATE KEY, and therefore auto-increment values can not be guanateed when the SQL query is made. It’s rare it’s needed, but just want to make sure.

    Thanks

    [Mod: Moved to WP-Advanced]

Viewing 4 replies - 1 through 4 (of 4 total)

  • auto_increment doesn’t need to be sequential. If you have a table with auto-incs in them, and delete a entry in the middle it doesn’t break the whole table, does it? ??

    Thread Starter RobbieLePommie

    (@robbielepommie)

    Thanks for the response. I’ll change the setting and see how it goes.

    But the reason for the question is also INSERTS, not just DELETE. What some applications do is that they reply on the table locks to guarantee the auto-increment value in order to update another table with that value. Deleting a record may or may not matter, but this is only part of the question.

    (Yes – I simplified the subject, but if set the subject as “Anyone know if I can set innodb_autoinc_lock_mode=2” no-one would have read it!)

    Cheers – we’ll see how we go.

    MySQL stores each auto-increment value. It cannot be set to a value lower than it currently is. The only way to reset it is to truncate (or drop+create). Usually it is combined with a PRIMARY or UNIQUE key which stops the same value being re-used. However, you can set the value to be anything you like.

    Anders

    (@seananders1227)

    You can usually reconfigure the last auto increment value if you are so inclined.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Do AUTO_INCREMENTS have to be sequential?’ is closed to new replies.