Changing MySQL autoindex

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Bitweiser
Forum Newbie
Posts: 11
Joined: Sun Jul 30, 2006 9:27 am

Changing MySQL autoindex

Post 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
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

Code: Select all

alter table table_name auto_increment = 1;
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Changing MySQL autoindex

Post 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).
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post 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).
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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).
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post 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
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Post by wtf »

You can do it! 8O
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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()
Last edited by Luke on Thu Sep 14, 2006 1:30 pm, edited 2 times in total.
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post 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! ;)
Bitweiser
Forum Newbie
Posts: 11
Joined: Sun Jul 30, 2006 9:27 am

Post 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.
Post Reply