mySQL auot_increment and deletes

Anon

I know this is more for a mySQL forum... but I know some people here can answer this for me.

I have a table that has an auto_incrementing field. Now I can add rows to it no problem. But when I delete a row, the next row entered after it doesn't receive the deleted rows' number. Even though it is available. It skips it. For example:

I have this table:

Auto_ID | data
1 | apple
2 | orange

3 | pear

Then I delete the pear. This should free up the Auto_ID "3", and the next value should be "4". But when I enter new data (a banana for example), I get this:

Auto_ID | data
1 | apple
2 | orange

4 | banana

What if I set the Auto_ID field to support 99,999 entries. I will never have 99,999 entries for this table, but if the number is never freed up, will the field get full? Or will mySQL understand to use the free numbers in the Auto_ID field?

I hope I explained this right, and I hope someone can help.

Thanks in advance!
chadT

paulsim

You do not seem to have got an answer to your query.

Did you solve it?

I do somehting similar.

I delete five records in a row.

Then I put five records back in again.

In this case they get higher IDs as you say.

When I retrieve them they actually come out in reverse order. They seem to have gone into memory where the old ones were, but in the memory space they used the higher one first and then the lower ones.

This was a bit of a nuisance, but whenI retrieved them I had to put ORDER by relid. That did the trick.

I too wonder why it does not use up old IDs.

Did you come to nay more conlusions since your message last month---- oops not last month - it was a year ago.???

Paul