LAST_INSERT_ID() returns the most recent id generated for the current connection by an auto increment column, but how do I tell if that value is from the last insert and not from a previous insert on the same connection?
Suppose I am using a connection from a pool, which may have inserted a row before I got the connection, and I execute an conditional insert:
insert into mytable (colA)
select 'foo' from bar
where <some condition>;
select LAST_INSERT_ID();
I have no way of knowing if the value returned is from my insert.
One way I thought of is:
@previousId := LAST_INSERT_ID();
insert into mytable (colA)
select 'foo' from bar
where <some condition>;
select if(LAST_INSERT_ID() != @previousId, LAST_INSERT_ID(), null);
Is there a way to "clear" the LAST_INSERT_ID() value, so I know it's a fresh value caused by my SQL if a non-zero value is returned?