Jump to content


Photo

Help: Reusing auto_incremented rows.


  • Please log in to reply
4 replies to this topic

#1 LApprenti Sorcier

LApprenti Sorcier
  • Members
  • Pip
  • Newbie
  • 2 posts

Posted 28 December 2003 - 05:33 AM

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!

#2 Hokus

Hokus
  • Members
  • PipPip
  • Member
  • 23 posts
  • LocationAlameda, CA, USA

Posted 28 December 2003 - 11:57 AM

Hi, this thread discusses the same isue:

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

After you hit 99999, no further entries can be made until you change the int max value in MySQL.
[!--sizeo:2--][span style="font-size:10pt;line-height:100%"][!--/sizeo--][!--fonto:Arial--][span style="font-family:Arial"][!--/fonto--][!--coloro:blue--][span style="color:blue"][!--/coloro--]"Experience is a wonderful thing. It enables you to recognize a mistake when you make it again".
[!--sizec--][/span][!--/sizec--][!--colorc--][/span][!--/colorc--][!--fontc--][/span][!--/fontc--]

#3 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,667 posts
  • LocationLos Angeles, CA USA

Posted 29 December 2003 - 08:15 AM

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.

#4 LApprenti Sorcier

LApprenti Sorcier
  • Members
  • Pip
  • Newbie
  • 2 posts

Posted 29 December 2003 - 07:36 PM

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.

#5 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,667 posts
  • LocationLos Angeles, CA USA

Posted 29 December 2003 - 11:51 PM

:lol:




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users