Is your MySQL auto increment skipping numbers? Our MySQL Support team is here to help you with your questions and concerns.

MySQL auto increment skipping numbers | Causes & Fixes

The auto-increment feature in MySQL allows us to automatically assign a unique, numerical value to a column in a table each time a new row is inserted. This auto-increment value increments by 1 for each new row inserted by default.

However, in some cases, it is possible for auto-increment to skip numbers in case rows are deleted or if an INSERT statement explicitly mentions a value for the auto-increment column. This leads to gaps in the sequence of auto-increment values, which is undesirable in some cases.

Let’s take a look at some situations where auto-increment tends to skip numbers:

• If we delete a row with an auto-increment value, the next auto-increment value increments by 1 from the last value. This is regardless of whether we use the or not value.
• When we insert a row with an auto-increment value and roll back the transaction, the auto-increment value increments. This results in a gap in the sequence.
• If you mention a value for the auto-increment column in an INSERT statement, this value gets priority rather than the next auto-increment value, leading to a gap in the sequence.

Our experts have together the following preventive measures to avoid gaps in the sequence of auto-increment values:

• We can create a trigger to reset the auto-increment value to the next available value when a row is deleted.
• Rather than relying on auto-increment, we can use a custom function to generate unique values for a column.
• In most cases, gaps in auto-increment values are not a problem. Since the values are still unique, we can use them to identify rows in a table.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

To sum up, our Support Techs demonstrated common scenarios where MySQL auto increment can skip numbers and how to avoid these situations.