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;
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? | 