Professional Web Applications Themes

LAST_INSERT_ID() - MySQL

Hi all, I have a user table in a mysql db, when a user is inserted, I need the generated keyfield from their entry to enter data into another table. To accomplish this I insert the user and immediately after, run another query using the LAST_INSERT_ID() command. This seems to work, but isn't there a possibility that inbetween these two queries, another user could register and the LAST_INSERT_ID return the wrong id? If so, is there a better way of doing this? I tried executescaler on the insert, but it always returned nothing. Any help would be appreciated!...

  1. #1

    Default LAST_INSERT_ID()

    Hi all,

    I have a user table in a mysql db, when a user is inserted, I need the
    generated keyfield from their entry to enter data into another table.
    To accomplish this I insert the user and immediately after, run
    another query using the LAST_INSERT_ID() command. This seems to work,
    but isn't there a possibility that inbetween these two queries,
    another user could register and the LAST_INSERT_ID return the wrong
    id?
    If so, is there a better way of doing this? I tried executescaler on
    the insert, but it always returned nothing.

    Any help would be appreciated!

    Gef.Mongoose@gmail.com Guest

  2. #2

    Default Re: LAST_INSERT_ID()

    com wrote: 

    LAST_INSERT_ID() is connection specific. You will get the value from your
    insert whilst someone else would get the value from their insert.

    Indeed the book commonly known as the MySQL Reference Manual, has this to
    say on the subject:

    The ID that was generated is maintained in the server on a per-connection
    basis. This means that the value returned by the function to a given client
    is the first AUTO_INCREMENT value generated for most recent statement
    affecting an AUTO_INCREMENT column by that client. This value cannot be
    affected by other clients, even if they generate AUTO_INCREMENT values of
    their own. This behavior ensures that each client can retrieve its own ID
    without concern for the activity of other clients, and without the need for
    locks or transactions.


    Paul Guest

  3. #3

    Default Re: LAST_INSERT_ID()

    On 4 Feb, 18:08, "Paul Lautman" <com> wrote: 


    >
    > LAST_INSERT_ID() is connection specific. You will get the value from your
    > insert whilst someone else would get the value from their insert.
    >
    > Indeed the book commonly known as the MySQL Reference Manual, has this to
    > say on the subject:
    >
    > The ID that was generated is maintained in the server on a per-connection
    > basis. This means that the value returned by the function to a given client
    > is the first AUTO_INCREMENT value generated for most recent statement
    > affecting an AUTO_INCREMENT column by that client. This value cannot be
    > affected by other clients, even if they generate AUTO_INCREMENT values of
    > their own. This behavior ensures that each client can retrieve its own ID
    > without concern for the activity of other clients, and without the need for
    > locks or transactions.[/ref]

    Thanks for answering this. I'm using c# asp.net with mysql and am
    curious if connection pooling utilised by .net would effect this
    command in any way?

    Gef.Mongoose@gmail.com Guest

  4. #4

    Default last_insert_id()

    Anyone out there have a working snippet of code using last_insert_id()
    to grab the last inserted record id? I've been trying to for the last
    hour and get nuttin but errors so far. I'm using coldfusion too btw, I
    have song_id which is my PK and I can pull the ids using SELECT * just
    fine, but the minute I try to do last_insert_id(song_id) it bombs. I
    guess I'm just not understanding how to use it....seeing the proper
    syntax would be great if possible.
    TIA

    Bewildered Bob

    Bob Guest

  5. #5

    Default Re: last_insert_id()

    On 10 Mar 2007 18:43:48 -0800, Bob Imperial wrote: 

    Show the error.

    --
    "It's 106 light-years to Chicago, we've got a full chamber of anti-matter,
    a half a pack of cigarettes, it's dark, and we're wearing visors."
    "Engage."
    Peter Guest

  6. #6

    Default Re: last_insert_id()

    >Anyone out there have a working snippet of code using last_insert_id() 

    Example:
    SELECT LAST_INSERT_ID();

    OR
    INSERT INTO tablea (....) VALUES (....); Do the insert.
    INSERT INTO tableb (parent_id, detail 1, detail2) VALUES
    ( last_insert_id(), 'XYZ', '1234'); Refer to tablea.
     

    Don't use an argument for last_insert_id(). I think that's mostly
    used for replaying binary logs from an old backup and transaction logs.

    Note: you don't get to select which table. It's the last inserted
    id for your connection (you don't have to worry about someone else
    doing inserts at the same time) for *any* table. If you want the
    id for a table you just inserted to, grab the value before doing
    more inserts.

    Gordon Guest

  7. #7

    Default Re: last_insert_id()

    Bob Imperial wrote:
     


    http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

    Scroll down to LAST_INSERT_ID()


    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

Similar Threads

  1. LAST_INSERT_ID() performance??
    By Tim in forum MySQL
    Replies: 9
    Last Post: January 26th, 01:57 AM
  2. SELECT last_insert_id() not working
    By yawnmoth in forum MySQL
    Replies: 3
    Last Post: October 8th, 11:28 AM
  3. last_insert_id()
    By lingo_user in forum Macromedia ColdFusion
    Replies: 0
    Last Post: June 29th, 07:26 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139