Inserting into mysql and getting inserted id at the same time

bizactuator has asked for the wisdom of the Perl Monks concerning the following question:

I have a site with lots of people on it, so I need to get the last inserted id at the same time as the post, in case someone else had an insert at the same time, so it does not risk getting someone else's inserted row, or something.

This is my code as it is:


#.... other code for this table is above...
    $_pstmt .= ',' if $_pstmt;
    $_pstmt .= '`all_details` = ?';
    push(@_pplaceholder,"$_alldotnot");

    $_pstmt .= ',' if $_pstmt;
    $_pstmt .= '`admin_notes` = ?';
    push(@_pplaceholder,"Order Details");

    $_pstmt = "INSERT INTO `accttrans` SET $_pstmt";
    $_checkifposted = $dbh->do($_pstmt, undef, @_pplaceholder);
[download]

Is there a way that $_checkifposted would have the last_inserted_id? or is it just true or false?

Is there a way to have it BE the last inserted id for that insert itself?

Thanks,

-Richard

Comment on Inserting into mysql and getting inserted id at the same time Download Code

Replies are listed 'Best First'.
Re: Inserting into mysql and getting inserted id at the same time by choroba (Cardinal) on Mar 24, 2021 at 15:26 UTC
Instead of using $dbh->do, create a statement handle and use `$sth->last_insert_id` [download] Update: It seems you need DBD::MariaDB for this to work, not DBD::mysql. `map{substr$_->[0],$_->[1]\|\|0,1}[\\|\|{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^ARGV,3]`	[reply] [d/l] [select]
Re^2: Inserting into mysql and getting inserted id at the same time by Marshall (Canon) on Mar 25, 2021 at 20:19 UTC
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?	[reply] [d/l] [select]
Re^3: Inserting into mysql and getting inserted id at the same time by choroba (Cardinal) 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]`	[reply] [d/l] [select]
Re^4: Inserting into mysql and getting inserted id at the same time by Marshall (Canon) on Mar 25, 2021 at 21:07 UTC
Re: Inserting into mysql and getting inserted id at the same time by Anonymous Monk on Mar 27, 2021 at 04:39 UTC
You might want to consider generating ID's independently of an autoincrement column. Take a look at DBIx::Sequence and DBIx::MySQLSequence. Yeah, they're older modules, but you might find some advantages in doing it this way.	[reply]
Re: Inserting into mysql and getting inserted id at the same time by Anonymous Monk on Mar 25, 2021 at 14:13 UTC
The SQL server will always serialize the incoming requests – they never literally occur "at the same time." Follow the advice already given to create a statement handle and retrieve the id. Also: if you need to perform a series of SQL statements without the possibility of interference, use explicit SQL "transactions." You begin the transaction, issue your statements, then "commit" to cause all of them to take effect at once.	[reply]