SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re-using deleted IDs?

    Hello,

    Like most people all my tables use a unique ID number to identify every record. I have this set to auto_increment.

    My question is this:

    Is there a way to set up the database so that it uses the lowest available ID number?

    That is, say I put 100 records in one day, but they get used and deleted.

    The next day, if I put another 100 records in they would use the IDs 101-200. Is there not a setting to just have it re-use 1-100 since those records no longer exist?
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    short answer: no

    long answer: you don't really want to do this, no

    don't pay any attention to the gaps in the numbers

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm just worried about the numbers getting too large for it to handle. I have a few tables that updated and emptied so often that these numbers are going to grow large quite quickly.

    Is this something I should be concerned with?
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i would be most happy to dispel, clear up, eliminate, remove, and erase all your worries

    two questions: what DATATYPE are your ids, and how many rows do you add to the table per day?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    At the moment they are int(11).

    In some of the tables it could get up to a several thousand entries per day on a busier day
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    let's say 10,000 per day, okay?

    with INTEGER UNSIGNED, you will run out of numbers in AD 3182

    want to place a small wager about whether your app will still be running?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    May 2005
    Location
    London, ON
    Posts
    360
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol ok, you win this round
    Ryan Price
    Subscribe to my blog for regular tips and tricks

  8. #8
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there's also BIGINT


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •