Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Sep 2002
    Posts
    459
    Thanks
    0
    Thanked 20 Times in 20 Posts

    autoincrement...is this a problem

    My datatable auto increments the id's. When I just added to new entries and checked datatble using phpmyadmin things were all out of whack!

    Last page (7) id's read:
    183,184,185,186,187

    When I went looking for the new entries I found them on page 3 which read like:
    77,78,79,188,189,81,82,83

    Is this a problem or is mysql supposed to do that?
    NO Limits!! DHCreationStation.com
    ------------------------------------------------------------
    Broken items wanted for tinkerin'! PostItNow@BrokenEquipment.com
    Global Complaint Dept.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    I have no idea what you mean by "went looking for new entries" or, for that matter, what "page 3" is.

    But here's a clue for you: If you do *NOT* specify an ORDER BY clause in a SQL SELECT, then MySQL (and *any* database) is allowed to return the records to you in ANY ORDER IT WANTS TO, including totally random order.

    *MOST* databases will simply return the records to you in the order they are found on the disk. And you need to remember that databases *WILL* RE-USE space caused by deleted records.

    If I had to guess, I'd say you deleted your record with auto_number value 80 and it was a big record (longer than average VARCHAR values??). So MySQL was able to fit *TWO* records (auto-number values 188 and 189) into the space opened up when you deleted 80.

    But that is just a guess. Once again, THE ORDER OF AN AUTO_INCREMENT COLUMN MAY OR MAY NOT BE the order of the records on disk and MAY OR MAY NOT BE the order you get the records if you just do a SELECT without any ORDER BY.

    If you want to see that latest records, by auto_increment value, the best thing to do is usually
    Code:
    SELECT * FROM yourtable ORDER BY auto_increment_field DESC LIMIT 20
    or similar.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by c1lonewolf View Post
    Is this a problem or is mysql supposed to do that?
    It is definitely supposed to do that.

    1. The records will be fit in as best as they can be in the actual file without regard to their key values.

    2. All an auto-increment value represents is a unique value to identify each record. There is no particular reason why they have to be in order - it is just easier to generate them that way.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #4
    Regular Coder
    Join Date
    Sep 2002
    Posts
    459
    Thanks
    0
    Thanked 20 Times in 20 Posts
    Thanks guys that's what I needed to know!
    NO Limits!! DHCreationStation.com
    ------------------------------------------------------------
    Broken items wanted for tinkerin'! PostItNow@BrokenEquipment.com
    Global Complaint Dept.


  •  

    Posting Permissions

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