MySQL autoincrement problem

I have a table with autoincrement field, I have for example 1,2,3 values, when I insert 4 and delete it the next will normally be 5. I delete 5 and after a long time , say 1 week when I insert new record the next is again 4.

Can somebody tell me why this happen , why when I insert a new record after a long time开发者_JAVA百科 its getting the last inserted id of this table, and it is not taking into consideration the deleted records.

PS: This happens just after a long time,it is working ok when I am doing it in a short period of time

The last auto increment value for a table column is only stored in memory. It's not written to a table or something else. So if you restart your server (i.e. for a backup) the last increment value (5) is lost and mysql scans the table for the the last value which exists in the table (4).