Why is MySQL auto_increment skipping numbers.

Question

We are running an import of an existing product table into a new table of our own. The import script we've written runs perfectly and inserts the right amount of rows (6000 or so). However, after the import the next auto incremented primary key/id is 1500 entries (or so) above the number of rows in the table.

We can't understand why MySQL is doing this and we'd like it to stop. I've done the usual searches online looking for help but I am drawing a blank - any ideas?

Answer 1

Difficult to comment without seeing the script for table creation or data migration.

Are you:

• Importing from a different database?

• Importing from a different database technology?

Also what version of MySQL are you using?

Are you saying that all auto-increment fields (presumably your table primary key) are correct upto row 6000 (where the entry is say 6000) and then at row 6001 has a value of 7500 in the same field?

Answer 2

If you perform this command:

show create table Foo

Then you will see what the AUTO_INCREMENT= is set too.
My guess is that it is not set to start with 0.

You should then create your new table to have the AUTO_INCREMENT set to 0 (or 1, I cannot remember from the top of my head). This should do the trick.

Answer 3

Let's take this simple table for example:

CREATE TABLE `products` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(64) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

And import file that look like:

"id","name"
1,"product 1"
2,"product 2"
5,"product 3"
102,"product 4"

Then you are importing data to both columns, so auto incrementing mechanism does not work. After importing all rows, autoincrement value for table is set to MAX(id)+1 [103 in this case] to ensure next autoincremented id is unique. If it was equal to number of rows inserted, then next autincrement value would be 5 and would colide with row #3.

If you want to have clean start and last id equal to number of rows you have to either get rid of "id" column from .csv file, or create table without AUTO_INCREMENT for id, import data and run this simple sql:

SET @i=0;
UPDATE `products` SET id=@i:=@i+1 ORDER BY id;
ALTER TABLE `products`  CHANGE COLUMN `id` `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT FIRST;

First query sets auxiliary variable, that will be incremented before updating the record. Second one updates record to have id equal to row number. Third will change id column to be autoincremented and set proper value for next autoindex.

But before changing any primary keys ensure that they are not used in any other tables as foreign keys!