Jump to content

Archived

This topic is now archived and is closed to further replies.

LApprenti Sorcier

Help: Reusing auto_incremented rows.

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.