AUTO_INCREMENT question

[Qwark]

Hello,

I have made a mysql database:
CREATE table links (
id INT NOT NULL AUTO_INCREMENT,
adres VARCHAR(60),
PRIMARY KEY(id));

When i put a new record in de database it get a number automaticly. (1,2,3 ...) But when i remove 2 the records are not reorganize automaticly. Is that possible, maybe without auto_increment. But how??
Can you help me?

Thanks,

Qwark

 

[vcherubini]

auto_increment means that the rows automatically increments when something is submitted into the database, not when something is taken out. That would be something like auto_decrement, but that does not exist.

You can use an UPDATE action to update the auto_increment field when you change something.

For example,

[tt]
UPDATE links SET id = id - 2
[/tt]

Where 2 could also be a variable or how many times you want to change the value of the field.


Hope this helps,

-Vic vic cherubini
malice365@hotmail.com

http://www.epicsoftware.com

====
Knows: Perl, HTML, JavScript, C/C++, PHP, Flash, Director
====

 

[Qwark]

Thanks Vic,

A have already solve the problem in an other way. But maybe your opinion is better.

But now another question.
When I have over a tousandmiljon times add and delete records in my table my id num is maybe 999999999 isn't that to much. Is there a maximum or does the mysql command auto_increment handles this problem.

Thanks,

Qwark

 

[MarcDePoe]

Qwark,

you can use bigint..
this will give room (if unsigned) for up to 2^64-1

hope that helps

MarcDePoe

 

[vcherubini]

Qwark,

MarcDePoe's answer should help.

I have a question for you though. I am wondering how you fixed your problem? Could you just post the mySQL queries that you used?

Thanks,

-Vic vic cherubini
malice365@hotmail.com

http://www.epicsoftware.com

====
Knows: Perl, HTML, JavScript, C/C++, PHP, Flash, Director
====

 

[Qwark]

vic,

I have solve my problem with PHP and a While command. The Id numbers in the database have there auto number id. But a the user of my management program are thinking that the numbers are 1,2,3 etc. Because i use that php command with an counter.
That's not the right way because auto_increment has the maximum of an int or a long int. So i'll gona try your tip.

Qwark.