Search code examples
mysqlsqlauto-increment

MySQL is it safe to use static numbers in an auto increment column

I have an auto-incremented ID field in a table. It is at 13321. If I have some products I want to insert and use the rows at 15,000 to 15,500.

I have queries in a web application that can insert new data. The queries all allow the system to generate the "id" field like:

INSERT INTO mytable ('','value1', 'value2','value3')

If I insert my static data using 15000 to 15500 then reset my auto-increment id back down to 13322 will it be safe when the system gets to 15000? Will MySQL know to skip to 15501 ?

Solution

  • Once you enter the values 15000 to 15500 the AUTO_INCREMENT property of the table will be set to 15501. Afterwards you cannot reset the property back to 13322 (or any other value less than or equal to 15500). Quote from MySQL manual:

    You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

    The Will MySQL know to skip to 15501 portion of your question therefore becomes irrelevant.