MySQL - MySQL last insert id

In MySQL you can retrieve the value of the last auto increment field upped by an insert using the LAST_INSERT_ID() function. Example:

INSERT INTO foo (auto,text)
    VALUES(NULL,'text');         # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
    VALUES(LAST_INSERT_ID(),'text');  # use ID in second table

In PHP you can use the 'mysql_insert_id()' function to do the same :

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());

However the php variant will return incorrect values when the db field is typed as BIGINT.

Use the SQL function LAST_INSERT_ID() instead when adressing fields of this type.

rendered @ Thu Mar 27 13:01:38 CET 2014