Changing MySQL autoindex
Moderator: General Moderators
Changing MySQL autoindex
Hello,
I have a table where the first colom (fieldname=number) is my index. It is set to autoindex and is used as a sequence number. I know...It can be done otherwise but, that's life.
Now my question: When I delete some rows my rowcount doesn't match my next autoindex. Is there any way to change the autoindex value ?
Tia
I have a table where the first colom (fieldname=number) is my index. It is set to autoindex and is used as a sequence number. I know...It can be done otherwise but, that's life.
Now my question: When I delete some rows my rowcount doesn't match my next autoindex. Is there any way to change the autoindex value ?
Tia
Code: Select all
alter table table_name auto_increment = 1;- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: Changing MySQL autoindex
You don't want to do this. The only reason for doing it is so numbers look pretty in the database. Resetting your autoincrement value will hose your table when you try to add another row (think of autoincrement=1, next insert makes it 2, but 2 is already there... problem if it is a primary key).Bitweiser wrote:Hello,
I have a table where the first colom (fieldname=number) is my index. It is set to autoindex and is used as a sequence number. I know...It can be done otherwise but, that's life.
Now my question: When I delete some rows my rowcount doesn't match my next autoindex. Is there any way to change the autoindex value ?
Tia
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
True but I've had occasions where an import of data for what ever reason has created a million rows of data that turns out is wrong and needs to be deleted, now for the sake of cleaness, readability from a console and keeping a big chunk of int space back I would reset the auto_increment, doesnt take any time to do and makes the difference between:
1
2
3
3466667
3466668
3466669
and:
1
2
3
4
5
6
Anyway, that's just my opinion, and everyone has a right to it ;p
1
2
3
3466667
3466668
3466669
and:
1
2
3
4
5
6
Anyway, that's just my opinion, and everyone has a right to it ;p
In that case it's fine... but if you are just trying to keep the numbers sequential, it's not. That's not what it is for. If you're trying to count how many rows there are use COUNT()ody wrote:True but I've had occasions where an import of data for what ever reason has created a million rows of data that turns out is wrong and needs to be deleted, now for the sake of cleaness, readability from a console and keeping a big chunk of int space back I would reset the auto_increment, doesnt take any time to do and makes the difference between:
1
2
3
3466667
3466668
3466669
and:
1
2
3
4
5
6
Anyway, that's just my opinion, and everyone has a right to it ;p
Last edited by Luke on Thu Sep 14, 2006 1:30 pm, edited 2 times in total.
Well durrr!The Ninja Space Goat wrote:In that case it's fine... but if you are just trying to keep the numbers sequential, it's not. That's not what it is for. If you're trying to count how many rows there are use COUNT()ody wrote:True but I've had occasions where an import of data for what ever reason has created a million rows of data that turns out is wrong and needs to be deleted, now for the sake of cleaness, readability from a console and keeping a big chunk of int space back I would reset the auto_increment, doesnt take any time to do and makes the difference between:
1
2
3
3466667
3466668
3466669
and:
1
2
3
4
5
6
Anyway, that's just my opinion, and everyone has a right to it ;p
As the matter of fact, you were all a great help. My problem is solved.ody wrote:Code: Select all
alter table table_name auto_increment = 1;
Thanks to ody for the code and thanks to the rest of you for your - apreciated - input.