SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Thread: Insert in MySQL

  1. #1
    SitePoint Evangelist dmsuperman's Avatar
    Join Date
    Feb 2005
    Location
    A box
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Insert in MySQL

    I have a table with a column called comid, which is the primary key. It is auto-increment. I have no problems with it, except one. When I delete a row, then insert another, it inserts it in the spot of the delete one. The id still increments to the highest one plus 1, but it goes in the wrong order. Is there a way to make sure it gets inserted into the last row? Is primary key the wrong thing I want to do? Do I even need it to be primary key?
    <(^.^<) \(^.^\) (^.^) (/^.^)/ (>^.^)>
    Core 2 Duo E8400 clocked @ 3.375GHz, 2x2GB 800MHz DDR2 RAM
    5x SATA drives totalling 2.5TB, 7900GS KO, 6600GT

  2. #2
    SitePoint Evangelist
    Join Date
    Sep 2004
    Location
    Oregon
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    odd... Whats the field type? (VarChar, INT, Etc.)

  3. #3
    SitePoint Evangelist
    Join Date
    Sep 2004
    Location
    Oregon
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And Table Type?

  4. #4
    SitePoint Evangelist dmsuperman's Avatar
    Join Date
    Feb 2005
    Location
    A box
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    INT and as for table type, the default (I think it's MyISAM or whatever that is)
    <(^.^<) \(^.^\) (^.^) (/^.^)/ (>^.^)>
    Core 2 Duo E8400 clocked @ 3.375GHz, 2x2GB 800MHz DDR2 RAM
    5x SATA drives totalling 2.5TB, 7900GS KO, 6600GT

  5. #5
    SitePoint Enthusiast jpp's Avatar
    Join Date
    Nov 2003
    Location
    Arnhem, The Netherlands
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want to use auto-increment then that column should be defined as primary key, or at least as unique, i think (There can only be one auto column and it must be defined as a key... is the message if you try to create an auto-increment column that is not a pr. key or not unique).
    The normal behaviour is when you have for instance 4 records with keys 1, 2, 3 and 4 and you delete the last record (id = 4) and insert another then that one will get id = 5. The 4-spot will not be automatically be re-used.
    You can do this manually by getting the max-id and adding 1 to this and then insert the new record with that id: SELECT MAX(comid_id) AS max_id FROM comid;

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no, don't use the select max(id)+1 technique, unless you also have a mechanism to throttle (single-thread) your transactions so that you actually perform the insert before any other thread can use the same table

    i.e. not recommended


    dmsuperman, the auto_increment number should be used by your application for uniqueness only, and perhaps for sequential sorting, although a date or datetime is preferred

    but the gaps should make no difference to anything

    if the gaps matter, then you are probably misusing the idea of the numbers, and need to re-examine your application design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist dmsuperman's Avatar
    Join Date
    Feb 2005
    Location
    A box
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Um, not sure what you just said lol. Basically my only problem is I need to keep the order and insert to the end. I have no problem with the auto_increment or anything, or keeping the values what I want. But my project relies on the order that the things are in, and I'd like the newest inserted in the bottom. Let me show you:

    1
    2
    3
    4 ->deleted
    5

    Then I have:
    1
    2
    3
    5
    Then after I insert another one, I get it alright, but it goes in like this:
    1
    2
    3
    6
    5
    Which is not good. I need it to go:
    1
    2
    3
    5
    6
    <(^.^<) \(^.^\) (^.^) (/^.^)/ (>^.^)>
    Core 2 Duo E8400 clocked @ 3.375GHz, 2x2GB 800MHz DDR2 RAM
    5x SATA drives totalling 2.5TB, 7900GS KO, 6600GT

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    actually, i think you will be surprised to learn that rows are not inserted into a particular position in a table, because there is no such concept

    if you want to see the rows in a particular sequence, you must use ORDER BY

    otherwise, you get them in arbitrary sequence

    granted, the arbitrary sequence appears to be in the same order as the auto_increment, but this is not the actual sequence that they are stored in, because there is no such concept as the "sequence" of rows in a table

    the only concept of sequence in sql is the ORDER BY in a SELECT statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist dmsuperman's Avatar
    Join Date
    Feb 2005
    Location
    A box
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So what do you suggest I do to keep them in order by time? I'd rather not have to use time() as the primary field, I'd prefer just to use a single or double digit number. What I really need to do is just insert it at the end, because if you don't use any ORDER BY in the select then it just orders them as it gets them.
    <(^.^<) \(^.^\) (^.^) (/^.^)/ (>^.^)>
    Core 2 Duo E8400 clocked @ 3.375GHz, 2x2GB 800MHz DDR2 RAM
    5x SATA drives totalling 2.5TB, 7900GS KO, 6600GT

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dmsuperman
    So what do you suggest I do to keep them in order by time?
    i suggest you always use ORDER BY when retrieving rows


    Quote Originally Posted by dmsuperman
    What I really need to do is just insert it at the end, because if you don't use any ORDER BY in the select then it just orders them as it gets them.
    actually, you cannot insert the new row at the end, because there is no such concept!!

    rows in a table are stored any old place on the disk

    you have no control whatsoever over physical placement of rows in a table, and this is as it should be -- it is the database's job to decide where it wants to store rows, not yours

    again: if you want to see rows in a particular sequence, use ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can do it, but it is stupid.

    just do OPTIMIZE TABLE after every delete

  12. #12
    SitePoint Evangelist dmsuperman's Avatar
    Join Date
    Feb 2005
    Location
    A box
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, then I'm going to insert a date when I insert so I can order it.
    <(^.^<) \(^.^\) (^.^) (/^.^)/ (>^.^)>
    Core 2 Duo E8400 clocked @ 3.375GHz, 2x2GB 800MHz DDR2 RAM
    5x SATA drives totalling 2.5TB, 7900GS KO, 6600GT

  13. #13
    SitePoint Evangelist
    Join Date
    Sep 2004
    Location
    Oregon
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Very, interesting. But; I've always used a sort when displaying information with dates, and phpmyadmin automatically displays in order. I think you have recieved proper answers. Good Luck. (and BTW - Don't put your "15" in your signature, I'm only 13 :-P)

  14. #14
    SitePoint Evangelist ldivinag's Avatar
    Join Date
    Jan 2005
    Location
    N37 33* W122 3*
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql follows relational database rules.

    so as "r937" says, there are no "insertion" order as like we humans think.

    even coming from the world of dBase to ACCESS was a "shock" to me.

    i would suggest checking out a book on relational DB to see how the theories by CODD came to today... may he rest in peace...

    http://www.research.ibm.com/resource...passaway.shtml
    leo d.


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
  •