I looked further into the MySQL documentation about this because I am in the planning phase for a new project which will also need the ID of the last row inserted. I plan to use that ID as a key into another table (multiple rows in Table 2 will correspond to a single row in Table 1).

At MySQL Last row inserted, I found: "When a row is inserted into a table in MySQL where there is a column which is set to AUTO_INCREMENT, the ID number is stored temporarily for the current connection."

I take that to mean that MySQL will "keep this ID consistent" on a per connection ($sth) basis. MySQL is a threaded application and multiple simultaneous writers are allowed. That means that another row could have been inserted by another thread (with its own connection) between the time of the INSERT and calling $sth->last_insert_id. However, it appears that you will get the ID of the last insert for this connection, not the ID of the last row inserted for the table as a whole.

In my application, I will be "batching" multiple inserts into one transaction. With:

$sth->begin_work;
...work here ...many inserts ....
$sth->commit;

[download]
A few implications: (1) If I never commit a transaction, there could be "holes" in the auto_increment numbering. And (2) Rows in the table could wind up being actually inserted "out of order" (not guaranteed that the auto_incremented row ID's are sequential. i.e. "sorted") due to actions by other threads.

Anyway, I think what you proposed will indeed work for DBD::mysql (corrected was just MySQL). I am curious why you think that it might not?

In reply to Re^2: Inserting into mysql and getting inserted id at the same time by Marshall
in thread Inserting into mysql and getting inserted id at the same time by bizactuator

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.