How to reset the auto_increment
Moderator: General Moderators
How to reset the auto_increment
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
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
Code: Select all
ALTER TABLE `table` AUTO_INCREMENT = 0Do 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
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
ou can use the syntax:
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.
Code: Select all
ALTER TABLE table_name AUTO_INCREMENT = 1;
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.
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.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
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
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.
(#10850)
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
Thanks for all your help,
Regards,
Matt
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US