Help: Reusing auto_incremented rows.
Posted 28 December 2003 - 05:33 AM
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?
Posted 28 December 2003 - 11:57 AM
After you hit 99999, no further entries can be made until you change the int max value in MySQL.
Posted 29 December 2003 - 08:15 AM
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.
Posted 29 December 2003 - 07:36 PM
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.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users