自动递增5个号码？

I'm using a database for a project where I'm inserting things and I have an auto-increment. However, randomly, the auto-increment ID started counting wrong.

For example, the last one in the table has an ID of 227. When I insert another row, it should auto-assign it as 228, but instead it jumps to 232. How do I fix this?

Auto-increment doesn't mean use the next highest number in the table.

There are a few reasons why the auto-increment number is non-contiguous.

  1. The auto-increment step may not be 1
  2. The previous rows may have been deleted
  3. Transactions inserting into the table may have been rolled back
  4. A record may have updated the auto-increment column
  5. The auto-increment start index may have been changed by a DDL modification.

There's probably a few other scenarios which would cause this, however.

To answer the question of "How do I fix this?", don't bother, the ID is supposed to be unique and nothing else, having IDs contiguous is usually not that useful (unless you're doing paging assuming they are contiguous, which is a bad assumption to have).

AutoIncrement numbers can be collected by the DB Server ahead of time. Instead of getting just the one asked for, a DB will sometimes get say 5 (or 10 or 100) and update the long term store of the next number to be assigned with the next + 5 (or 10 or 100). Then the next time the next one is assigned, it's got it in memory which is much faster, especially as it does not have to store the next to assign to the disk.

Timetable:

  • Asked for next number
  • Hasn't got one in memory so:
    • Go to disk to get the next (call it a)
    • Update disk with a + 100
    • Give the asker a
    • Store a+1 in memory as the next to give out
  • Asked for next number
  • Gives it a+1 and stores a+2 in memory

...and so on.

However if the DB Server gets stopped before it has given out all the numbers, then when it is restarted it will look for the next number and find a+100 - hence the gap. Autonumbers are generally guaranteed to given out as constantly increasing values, and should always be unique (watch what happens when max values are reached though). They are usually but not always sequential.

(Oracle does the same with Sequences. It's a while since I used it, but with a sequence you can specify a cache size. The only way to guarantee sequential assignments is to have a cache size of zero, which is slower.)