MySQL ID misunderstanding

[kitus]

Hi guys,

I'm pretty much a new user of MySQL, up to now I got to introduce, update, delete and read data from a database...

I ask you guys for some help with IDs. What are they exactly? are they just the sorting method used by default? In my case ID is set to auto_increment by default and when one row is erased the next introduced, instead of using the just released ID, gets a new one. Is there anyway for giving this ID that has been released to the new rows?

This may sound strange since one can sort the content by the different fields easily therefore it doesn't matter whether this ID is reused or not, but the thing is that in my case i need to give a unique number since this number somehow has to be transformed into a IP address so I can easily run out of IP addresses.

thank you in advance guys,

kitus

 

[TonyGroves]

There is nothing special about a field which happens to be called "id"; it's just a name. Your question is really about any field which is declared as auto_increment. For such a field, the next time a new record is inserted without the field being given a value, the highest existing value of that field is taken and incremented by one and allocated to the new record. You can always override the auto-incrementation by specifically allocating a value to the field. Also, there is no default sort order in MySQL; if you don't specify an order, the sequence is undefined, though in practice it's often the order in which the records were inserted.

 

[kitus]

thanks TonyGroves,

that is what i was afraid of, thanks for the information.. the question now is, is there any command for reusing already released IDs? This is what I'm looking for since I have this IP availability problem... any idea??

 

[TonyGroves]

You could issue a query to select all the ID field values from the table, then get your program to look for gaps in the sequence. You could also write a query to return the first unused id value in the table, but that would be complicated and slow.

 

[r937]

do not reuse auto_increment numbers, you are only asking for a world of hurt

if you use BIGINT you will never run out of numbers

r937.com | rudy.ca

 

[kitus]

Ok, thank you very much guys!!!!!

cheers,

/kitus