Re^2: Inserting into mysql and getting inserted id at the same time

[Marshall]

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?

[choroba]

> I think what you proposed will indeed work for MySQL. I am curious why you think that it might not?

I didn't say it wouldn't work for MySQL, I said it wouldn't work for DBD::mysql. You can use both the Perl modules for both the databases. But DBD::MariaDB mentions "last_insert_id" as a function of both the database handle and the statement handle, but DBD::mysql only mentions $dbh->{'mysql_insertid'};. See also the Changes of the former and search for "insert_id" to see what's been fixed.

[Marshall]

Thank you!

As I now understand it, DBD::mysql only does this on a per connection basis. The DBD::MariaDB driver can do it on a more refined basis, a per statement handle basis.

If the OP is using one DB connection in multiple threads, the DBD::mysql driver won't work.