Page 1 of 2

How to reset the auto_increment

Posted: Thu Sep 07, 2006 2:59 am
by matth2004
Hi,

Does anyone know how to reset the auto_increment in a column in a mysql database. I have 4 columns and I have auto_increment set on one of the columns. When I delete the data in that table (all of it) and then add a row the number for the auto_increment just keeps going. The only way is to delete and remake the whole table. Does anyone know a quicker way?

Regards,
Matt

Posted: Thu Sep 07, 2006 3:23 am
by Luke

Code: Select all

ALTER TABLE `table`  AUTO_INCREMENT = 0

Posted: Thu Sep 07, 2006 3:23 am
by jmut
Do you get out of values?
In most cases you don't need to reset an auto-increment column.

Look for reset in user comments.
http://dev.mysql.com/doc/refman/5.0/en/ ... ement.html

Posted: Thu Sep 07, 2006 3:24 am
by GM
ou can use the syntax:

Code: Select all

ALTER TABLE table_name AUTO_INCREMENT = 1;
to reset the auto_increment on a particular table.

Further:
There are a couple of system parameters that could be useful to you: auto_increment_offset and auto_increment_increment.

These parameters control the starting point for the auto_increment, and the amount by which the column gets incremented. Beware when using these paramters - make sure you read the manual well.

Posted: Thu Sep 07, 2006 3:34 am
by matth2004
Thanks heaps for all this, works like a charm.

Regards,
Matt

Posted: Thu Sep 07, 2006 7:50 am
by feyd
*sigh*

SQL != PHP.

:roll:

Posted: Thu Sep 07, 2006 6:15 pm
by matth2004
Also,

Would anyone know how to alter it back one? Say its on 73 with 73 people in the database, then I delete one person and want to set the counter back to 72 so the next person I add will get 73 instead of 74.

Regards,
Matt

Posted: Thu Sep 07, 2006 6:26 pm
by wtf
Why would you want to do that anyway? There's a reason why auto_increment is auto_increment! Is any other table related to that table because if it is, you'll end up killing references which will produce unrelated data. Just leave it as it is.

Posted: Thu Sep 07, 2006 6:29 pm
by matth2004
No theres no other table related to it. It's just standalone. So does anyone know how to alter it?

Regards,
Matt

Posted: Thu Sep 07, 2006 6:46 pm
by feyd
It's intended not to be altered. It can be, but you shouldn't.

If you really want this functionality then you don't need auto_increment set on it.

Posted: Thu Sep 07, 2006 6:52 pm
by matth2004
Ok then. I'll change it so that members are entered along with a member number instead of auto_increment so it saves me the hassle.

Regards,
Matt

Posted: Thu Sep 07, 2006 7:01 pm
by wtf
matth2004 wrote:Ok then. I'll change it so that members are entered along with a member number instead of auto_increment so it saves me the hassle.

Regards,
Matt
What if member enters the number that already taken? You'll have to check for that. That means, member could spend hours trying to enter a number thats available. Why force the user to enter thata that he/she doesn't really understand what is it for. You're just looking for trouble but hey, go ahead then write about your experiene and what you've learned.

:D

Posted: Thu Sep 07, 2006 7:09 pm
by Christopher
matth2004, I think you are missing the concept of an auto_increment field. The point is that the value in that field, which is usually the primary key, will always be unique -- e.g. no duplicates. Typically those numbers are not visible to users, but are used as keys for relations. I does not matter whether the numbers are sequential, only that they are unique. If you want sequential values for users to use, and to reuse abandoned values, then you should use a different scheme.

Posted: Fri Sep 08, 2006 2:52 am
by matth2004
Yes I know all of this. That's the thing, it's a football tipping manager. Two members will not have the same number already because the human being who runs the competition will already have every families name written down and a member number for them. They just enter all the members into it. It was a quick system I whipped up in 20 minutes just to make life easier.

Thanks for all your help,

Regards,
Matt

Posted: Fri Sep 08, 2006 3:09 am
by Christopher
I sounds like for that you don't need auto_increment at all.