MySQL question

[biskoppen]

Let's say I have a table with an auto_increment field called "id"..

This table got 5 rows.. where "id" goes from 1-5..

Now I delete row 4 and 5..

The next row I insert will get "id" = 6...

How do I get it do let the next "id" to start at 4 which is the highest available number?

(Don't ask why I need that )

[budz]

I use navicat.. you can edit anything in your table manually.

but I'd like to know how to automate this because that is annoying when you delete records

[Donkey Punch]

INNODB tables store the auto increment id in the MYI file. Even if you delete rows and restart the server MySQL will not reuse the id. The same is true for innodb.

from gooooogle serps

[k0nr4d]

i think 'OPTIMIZE' does that. google it, im not positive

[biskoppen]

i think 'OPTIMIZE' does that. google it, im not positive

Hmm... actually think I heard about that some years ago... will investigate that

[Alky]

why even use the auto increment feature then? just use your own id #'s

[biskoppen]

why even use the auto increment feature then? just use your own id #'s

Yeah, that was mine conclusion too... changing it right now.. (Thanks though)