Getting generatedauto-increment ID without second query (MySQL)

Question

I have been searching for a while on how to get the generated auto-increment ID from an "INSERT . INTO ... (...) VALUES (...)". Even on stackoverflow, I only find the answer of using a "SELECT LAST_INSERT_ID()" in a subsequent query. I find this solution unsatisfactory for a number of reasons:

1) This will effectively double the queries sent to the database, especially since it is mostly handling inserts.

2) What will happen if more than one thread access the database at the same time? What if more than one application accesses the database at the same time? It seems to me the values are bound to become erroneous.

It's hard for me to believe that the MySQL C++ Connector wouldn't offer the feature that the Java Connector as well as the PHP Connector offer.

What is the return value of the `insert` API call? I think it might be helpful. — John Dvorak, Aug 14 '13 at 18:40
Use transactional queries (Innodb). That will avoid your second issue. — Sablefoste, Aug 14 '13 at 18:41
If you're sending an INSERT to the server and want the inserted ID back, can't you just `INSERT....; SELECT LAST_INSERT_ID();` as a multi-statement query and make a single trip? Note: I'm a SQL Server guy, and this is trivial there, but I would be surprised if this isn't possible on MySQL as well. If it isn't, I'll drop-comment, and am genuinely curious. — WhozCraig, Aug 14 '13 at 18:44
Hallo client server architecture.... you need to do that query to fetch it 1) don't worry SELECT LAST_INSERT_ID will come from the memory should return almost instant. 2) If you are using MyISAM as storage engine don't worry, isn't multithreaded annyway.. because off the table locking... — Raymond Nijland, Aug 14 '13 at 19:06
@SableFoste: Unfortunately, that's not possible. We use the C++ connector, but the underlying database is MemSQL, which doesn't support transactions. — awfm9, Aug 14 '13 at 20:38
@RaymondN: As I mentioned, we use MemSQL as underlying database, so there is no table locking at all. The fact that it returns quickly doesn't change the fact that there are two round-trips instead of one. — awfm9, Aug 14 '13 at 20:40
@WhozCraig: Is it really as simple as that? I could only find an article on the MySQL C API, not the C++ API...which is of course better than nothing. I'll have to do some testing. — awfm9, Aug 14 '13 at 20:43
@awishformore ok you mentioned more then an 1 hour after i made the comment.. Next time dont use MySQL in the title if you mean MemSQL.. You probably really need a second query to fetch the last inserted id after insert, so implement that in your application sounds to me you put too much time in "premature optimization". But iam out dont know if the inserted id is thread safe in MemSQL.. — Raymond Nijland, Aug 14 '13 at 21:22
@RaymondN: It's because I'm currently still using MySQL in the development, I didn't think MemSQL would have such big implications with this question. My apologies. — awfm9, Aug 14 '13 at 21:45

Rahul · Answer 1 · 2013-08-17T21:15:53.570

An example taken from http://forums.mysql.com/read.php?167,294960,295250

sql::Statement* stmt = conn->createStatement(); 
 sql::ResultSet* res = stmt->executeQuery("SELECT @@identity AS id"); 
 res->next(); 
 my_ulong retVal = res->getInt64("id");

In nutshell, if your ID column is not an auto_increment column then you can as well use

SELECT @@identity AS id

EDIT:

Not sure what do you mean by second query/round trip. First I thought you are trying to know a different way to get the ID of the last inserted row but it looks like you are more interested in knowing whether you can save the round trip or not?

If that's the case, then I am completely agree with @WhozCraig; you can punch in both your queries in a single statement like inser into tab value ....;select last_inserted_id() which will be a single call

OR

you can have stored procedure like below to do the same and save the round trip

create procedure myproc
as 
begin
insert into mytab values ...;

select last_inserted_id();
end

Let me know if this is not what you are trying to achieve.

Getting generatedauto-increment ID without second query (MySQL)

1 Answers1