Database

How can I reset the auto increment in MySQL?

Hello Everyone!

This is just a quick post!

We got a problem here where we needed to reset the auto increment field in MySQL Database. It’s easy. You need just run the following command:

ALTER TABLE my_table_name AUTO_INCREMENT = 1;

Right? Not so fast little samurai! I just ran this command and for my surprise it didn’t work.
Why? I removed all my rows before run this command! I was angry!

This happens because we have used this table before and we can’t reset the increment to a value less or equal than a value that has already been used. But this happens only if you are using InnoDB Engine.

To fix it we changed our Table Engine to MyISAM using the following command:

ALTER TABLE my_table_name ENGINE=MyISAM;

After that you can run the the following command and your count will be reset!

ALTER TABLE my_table_name AUTO_INCREMENT = 1

After that we changed again our table to InnoDB Engine : )

That’s it! See you soon!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s