If I reset the auto-increment in MYSQL...it won't mess up anything, right?

Question

My auto-incrementing primary keys are getting too high. I'd like to reset it.

1, 2, 3, 4, 5, 6.

If I reset it to 0, the next inserted row will be 7, right?

How do I reset the autoincrement?

Answer 1

An id field that is autoincrementing is a surrogate key that has no meaning. It can never be "too high" or "too low". So don't change it.

Answer 2

ALTER TABLE my_table  ENGINE=MYISAM AUTO_INCREMENT=1;

Answer 3

Define "too high", why would you want to reset it? Unless it's getting close to 2^31-1 there should be no reason to reset it. Even if it did, you should probably just use a larger datatype.

Answer 4

I agree with Lutz, it's not a good idea to change the auto-increment ID. The primary key is important for data integrity and even if you don't have any dependencies to other tables, it's better not to get used fiddling with it.

If you have public facing IDs (e.g. on a web site) that become too high, introduce an alternative column for those.

But to answer the question:

ALTER TABLE theTableInQuestion AUTO_INCREMENT=1234

See the docs on AUTO_INCREMENT on details.

Answer 5

i reset the auto increment every now and then while developing. just for convencience if I call a dynamic page with some GET parameters.