Clearing LAST_INSERT_ID() before inserting to tell if what's returned is from my insert

Question

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?

Answer 1

I agree with @Digital Chris's answer, that you should not be determining whether an insertion succeeded or failed via inspection of the value returned by LAST_INSERT_ID(): there are more direct routes, such as the affected row count. Nevertheless, there might still be some requirement to obtain a "clean" value from LAST_INSERT_ID().

Of course, one problem with your proposed solution (of comparing against the pre-insertion value) is that it might happen that the insertion was successful and that its assigned auto-incremented value is coincidentally the same as that of the previous insertion (presumably on another table). The comparison could therefore lead to an assumption that the insertion failed, whereas it had in fact succeeded.

I recommend that, if at all possible, you avoid using the LAST_INSERT_ID() SQL function in preference for the mysql_insert_id() API call (via your driver). As explained under the documentation for the latter:

mysql_insert_id() returns 0 if the previous statement does not use an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id() immediately after the statement that generates the value.

[ deletia ]

The reason for the differences between LAST_INSERT_ID() and mysql_insert_id() is that LAST_INSERT_ID() is made easy to use in scripts while mysql_insert_id() tries to provide more exact information about what happens to the AUTO_INCREMENT column.

In any event, as documented under LAST_INSERT_ID(expr):

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID().

Therefore, before performing your INSERT, you could reset with:

SELECT LAST_INSERT_ID(NULL);

This also ought to reset the value returned by mysql_insert_id(), although the documentation suggests the call to LAST_INSERT_ID(expr) must take place within an INSERT or UPDATE statement—may require testing to verify. In any event, it ought to be pretty trivial to create such a no-op statement if so required:

INSERT INTO my_table (my_column) SELECT NULL WHERE LAST_INSERT_ID(NULL);

It may be worth noting that one can also set the identity and last_insert_id system variables (however these only affect the value returned by LAST_INSERT_ID() and not by mysql_insert_id()):

SET @@last_insert_id := NULL;