SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard holmescreek's Avatar
    Join Date
    Mar 2001
    Location
    Northwest Florida
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quick question. I have a mysql database and the first field, the primary key "ID" is set to autoincrement. I noticed that, for example, if you delete the record with the ID of 4, then add a new record, shouldn't MySql use the ID of 4? In old Dbase the pack command did this. Suggestions?


    thanks in advance!

  2. #2
    Not Bad, eh? Justin Sampson's Avatar
    Join Date
    Aug 2000
    Location
    N.S., Canada
    Posts
    487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup it should. I now that with older versions of mySQL (sorry, I can't remember the version's) it didn't but it does automaticly now.

  3. #3
    SitePoint Wizard holmescreek's Avatar
    Join Date
    Mar 2001
    Location
    Northwest Florida
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm, weird. Maybe it just does it on some type of random basis rather than after inserting the new record right after the delete.

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually the ID 4 should not be reused, it wouldn't be a true auto-incrementing field if it did. What if you have some relations to this 4 in some other tables and when you delete an create a new record with ID 4 it will screw up your relations. Furthermore an auto-incrementing number should ONLY be used to associate itself with a record and never for counting records. That is why it doesn't fallback to an allready used id number.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Wizard holmescreek's Avatar
    Join Date
    Mar 2001
    Location
    Northwest Florida
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, in theory every time you delete a record the maximum possible records your database can hold will decrease by a factor of 1?

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I suppose in theory yes, but if you are using int(11) that is a whole hell of a lot of records.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  7. #7
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But you can "OPTIMIZE TABLE $tablename" - which will let mysql reclaim space used for deleted records.

  8. #8
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freddydoesphp
    Actually the ID 4 should not be reused, it wouldn't be a true auto-incrementing field if it did. What if you have some relations to this 4 in some other tables and when you delete an create a new record with ID 4 it will screw up your relations.
    From my perspective there appears to be no eloquent theoretic underpinning to the behaviour of the auto_increment feature. For example, the exception to what freddy expains above is when the *last* record added to a table is deleted. The *next* record will have the same auto_increment value as the deleted record. As for foreign keys, hehehe - It would be funny if it wasn't so sad (that MySQL doesn't support them, yet). But you can see that because of this exception I describe you cannot rely on auto_increment to enforce the integrity of foreign keys.

    I found this post made by Fred Lindberg (Monty)to the MySQL mailing list
    http://www.tryc.on.ca/archives/mysql/Jun1997/0201.html
    The 'auto_increment' is actually a very quick hack. It does as
    follows:

    Just before doing a 'write' to a table, the 'handler' code does the
    following:

    - If there is a auto_increment field and the value of this is zero
    - Set the table in 'read_only_keys' mode.
    - Do a 'read_last' key on the table.
    - Update the 'auto_increment' field with the found value+1.
    - Set the table in 'normal' mode.

    I didn't want to change the .ISM format when I added 'auto_increment'
    and there is some issues that the above can handle much better than
    with any other methods. For example:

    - The above is very simple and one can always 'understand' what will happen.
    There is no need to have special commands to reset a 'wrong'
    auto_increment value.
    - If one accidently inserts some wrong entries, one only have to remove the
    entry and auto_increment continues as expected.
    - One can start a new series by simple insert a new record at a new
    bigger range.

    The downside with this is:

    - If one deletes the last record and inserts a new one will get the
    last records id. As long as one first deletes all 'sub records' in
    other tables this isn't really a problem.
    - One can't start with a specific value without some simple hack.

    Personally I think that the 'pro' is far better than the 'cons'

    Monty

  9. #9
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All I can say is:
    *sigh*

    (that is my "Sigh of profound hatred" -- something I usually reserve for horrible bugs in software...)
    Last edited by MattR; May 13, 2001 at 14:54.

  10. #10
    SitePoint Wizard holmescreek's Avatar
    Join Date
    Mar 2001
    Location
    Northwest Florida
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    *sigh* you better fix the photo of Beaker, you sinfull person you!


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
  •