Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

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

by Marshall (Canon)
on Mar 25, 2021 at 20:19 UTC ( #11130338=note: print w/replies, xml ) Need Help??


in reply to Re: Inserting into mysql and getting inserted id at the same time
in thread Inserting into mysql and getting inserted id at the same time

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?

Replies are listed 'Best First'.
Re^3: Inserting into mysql and getting inserted id at the same time
by choroba (Archbishop) on Mar 25, 2021 at 20:28 UTC
    > 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.

    map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
      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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://11130338]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (2)
As of 2021-06-15 17:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)












    Results (73 votes). Check out past polls.

    Notices?