auto_increment works, but how do you decrement?

[Solarpitch]

Hey,

Everytime I add a user to the database the user_id incremts accordingly. However, when I delete a record . . it doesnt seem to decrement it at all.

So if I have 5 records . . ie: id's 1-5 and delete them...

When I add another record it will start at 6!

Just wondering how to fix this really, cheers!

[wildteen88]

This is how auto_increment works and should not be changed.

[fenway]

There's nothing to fix -- why does everyone use UIDs for numbering?

[glennn.php]

you need to flush the table or start a new table to get your increments zeroed out, as far as i know. if you want to keep your records, copy them to a new table, and the increment will start after the last good table, I'M PRETTY SURE. if someone knows i'm wrong, please correct me...

[fenway]

There is a way to change the internal counter, but there's never a need to.

[glennn.php]

it's really kinda funny - of course there's not really a need to, it's just a counter, but i understand what he's talking about (i think) 'cause my OCD (Obssessive-Compulsive Disorder bothers me when there's a bunch of numbers that are "skipped" by the deletion of records before the database is put into operation. :o). it matters none at all, but when i'm about to launch a "new" install of something, i kinda like to know that the table is starting at "0" instead of "9".

at least, i'm just guessin' that's what he's looking at.

You ever watch "Monk"? The PI with OCD? if so, you'll know what i'm talking about.

ain't nuthin'.


they talked of firin' Torre at the end of the season last. i think the world's a better place now that the yankees have been brought to task and back down to this galaxy.

effin' yankees.

[fenway]

Well, yes, you can use TRUNCATE for that.

[glennn.php]

TRUNCATE will kill all existing records? or no?

what the original poster was hoping for is that if he were to delete all but say, the first 5 records, that Auto-Increment would revert to starting at "5" for the next entry instead of at whatever value it had reached before the deletion of said records. or hoping for a way to see that the table does that at some point without having to delete all the records and starting over...

i've always wondered the same thing, but as you pointed out earlier, it's not necessary. i guess that's what you meant.

[AndyB]

Truncate will remove ALL records.

The right way to handle auto-increment ids is to imagine that they don't exist, i.e. they belong to MySQL not to you. Don't plan on using them for anything.

[fenway]

There was some mention of resetting the counter for a "new" install... hence TRUNCATE.

[kid_drew]

I disagree.  auto-increment ids are a great row identifier and can be used for many things.  But DO NOT try to change the values at all, or you risk seriously f-ing something up.  Just let MySQL do it's thing.  It's not like you're going to run out of numbers...

[quote author=AndyB link=topic=116180.msg474127#msg474127 date=1164566942]
Truncate will remove ALL records.

The right way to handle auto-increment ids is to imagine that they don't exist, i.e. they belong to MySQL not to you. Don't plan on using them for anything.
[/quote]

[fenway]

He meant not to use the actual numbers, not the uniqueness.