Page 1 of 1

Changing MySQL autoindex

Posted: Thu Sep 14, 2006 9:37 am
by Bitweiser
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

Posted: Thu Sep 14, 2006 9:51 am
by ody

Code: Select all

alter table table_name auto_increment = 1;

Re: Changing MySQL autoindex

Posted: Thu Sep 14, 2006 10:04 am
by RobertGonzalez
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
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).

Posted: Thu Sep 14, 2006 10:09 am
by GM
We had this question recently, and I think that if the numeration matters so much, you shouldn't be using an auto_increment column. Auto_increment columns are intended to guarantee the uniqueness of each row, not to provide a handy method to count the rows.

Posted: Thu Sep 14, 2006 12:13 pm
by ody
There is nothing wrong with resetting the auto_increment to a value max(N)+1 (N being the column with the auto_increment).

Posted: Thu Sep 14, 2006 12:14 pm
by RobertGonzalez
There is also no need to keep the numbers sequential. They server more as a unique identifier, not so much a patternizer (is that even a word).

Posted: Thu Sep 14, 2006 12:52 pm
by ody
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

Posted: Thu Sep 14, 2006 1:05 pm
by wtf
You can do it! 8O

Posted: Thu Sep 14, 2006 1:24 pm
by Luke
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
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()

Posted: Thu Sep 14, 2006 1:29 pm
by ody
The Ninja Space Goat wrote:
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
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()
Well durrr! ;)

Posted: Fri Sep 15, 2006 10:36 am
by Bitweiser
ody wrote:

Code: Select all

alter table table_name auto_increment = 1;
As the matter of fact, you were all a great help. My problem is solved.
Thanks to ody for the code and thanks to the rest of you for your - apreciated - input.