auto_increment

I have database connected to my site (MySQL).
I have table with messages there and first value is idn where I store number that is autoincrementing for each message.

Here is my problem:
when I have messages with number 1, 2, 3 and I delete message with number 3 and then I add new message its number is 4 and there is no message with number 3. Same problem is when I delete messages form middle of my database.

It is not big problem, but I do not like it. Do you have idea how to solve it?

Well, you can't AFAIK. The only way to change MySQL's auto_increment counter is to delete everything from the table (as in "DELETE FROM table"), which sets it back to 1.

The only other option you have is to check the IDs manually, but that is slow..

Thanks for answer.

I hoped there will be some hidden why how to "reincrement" this one column time by time by some sql syntax.

Actually, if you delete the last entry, it can be reused:
ALTER TABLE table AUTO_INCREMENT=1;

If you delete a non-final ID, it will not get picked up, though. It seems the formula is max(id)+1.

you can also set the value of the auto_inc column when inserting/updating row, but you would have to set to the non-existing one...



(asuming row with the id of 17 was deleted, or wasn'e created yet...)

Actually it seems that is could be possible to delete column with idn after deleting non-final news and then add it and autincrement it. However I do not think I will do it, it is not worth work, it is working well without it.