mysql @@identity vs sql-server last_insert_id()

Question

Am I correct in understanding that mysql's LAST_INSERT_ID() function doesn't reset between insert attempts (as @@identity does in sql-server)... that if the immediately preceding insert operation fails, LAST_INSERT_ID() will return the pk of whatever the pk of that connection's last insert to any table with an auto-incrementing primary key was. And if I am correct in this, does this not seem like just about the most retarded behaviour for this function that one could come up with? Is there no mysql function that behaves similarly to sql-server's @@identity? One that will return NULL if the immediately preceding insert attempt creates no new record? Alternatively, how does one know with certainty the primary key of the most recent insert operation?

Answer 1

@@identity is usually wrong in sql server as well. You should be using scope_identity() in most cases instead.

In MySql, last_insert_id() should be safe to use because the only way you can call this function after an insert error is you've already correctly caught and accounted for the insert error. Otherwise you would still be processing commands. In other words, last_insert_id() is not your error-handling mechanism.

Answer 2

You have to check to see if the insert was successful first. If the insert was successful, then you can rely on LAST_INSERT_ID().

edit: in php:

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');

mysql_query('<PUT YOUR INSERT HERE');
if(mysql_affected_rows()>0){
  $last_id=mysql_insert_id();
}else{
  //panic!
}
?>