Help: Reusing auto_incremented rows.

[LApprenti Sorcier]

I don\'t quite know where to start, but here it goes...

 

I have a table, in which the first column, let\'s call it, ID, is an int(5) type field, is the primary key, and is set to auto_increment.

 

I will be adding and adding rows... But at some point, old rows will be deleted...

 

Now, I have two (may be 3) questions:

 

If I delete an older row, and I add a new one, will this new one have the ID number of the older one, thus taking up it\'s place?

 

I don\'t think so, so here comes the \"may be 3rd\" question:

So how do I get it to reuse that ID number and that space?

 

And the 2nd question (Odd ordering nah?):

 

What happens when it get to ID number: 99999? I mean, its int (5), right? is that 5 digits?, then, what will it do? add another one? or reuse the first ones? maybe, cause an error?

 

So if anyone can answer that, great!, or someone could also summarize everything and tell me this:

 

How do I keep reusing \"unique_id-ed, auto incremented\" rows from 00000 to 99999 after (notice this) deleting them in no particular order, and always assigning them a number automatically?

 

Thanks all!

[Hokus]

Hi, this thread discusses the same isue:

 

http://www.phpfreaks.com/forums/topic12635.php

 

After you hit 99999, no further entries can be made until you change the int max value in MySQL.

[gizmola]

If you were absolutely in love with the idea of reusing keys you could implement a custom reuse system. What this would involve is that you would need a seperate table in which you stored keys everytime you deleted an item (hence freeing it\'s ID number).

 

When you needed to insert a new row you would:

 

LOCK TABLE reuseidtable

select id limit 0,1

 

If you get a row, you use would delete it from the reuseidtable, and unlock the table.

 

Then when you went to do the insert into the table, you will specify this as the key value. The interesting thing about AUTO_INCREMENT to realize, is that it only kicks in if you do NOT specify a value for the column. You can override AUTO_INCREMENT by manually specifying a value if you choose.

 

If you didn\'t get a key then you\'d do your typical insert without specifying a value for the PK column, and auto_increment will work as usual.

 

This is a lot of complication in order to save what typically is not a particularly precious commodity. Simply specify a large number type for your key and you shouldn\'t have to worry about running out of numbers for the lifetime of your application.

[LApprenti Sorcier]

Oddly, i\'ve been convince by your last argument...

 

Simply specify a large number type for your key and you shouldn\'t have to worry about running out of numbers for the lifetime of your application.

 

LOL

 

Thanks anyway.

[gizmola]