«
»

mysql_insert_id() en InnoDB

Bij gebruik van transacties geeft de php-functie mysql_insert_id() verschillende waarden, afhankelijk van wanneer je het aanroept. Wanneer je mysql_insert_id() aanroept voor de commit, dan is de waarde correct. Echter na de commit is de waarde 0. Mysql “SELECT LAST_INSERT_ID” geeft wel de correcte waarde, maar dan weer niet na een ROLLBACK.

in PHP:

mysql_connect("localhost", "root", "");

mysql_query("CREATE DATABASE insertidtest");
mysql_query("USE insertidtest");
mysql_query("CREATE TABLE lastinsert (id INT PRIMARY KEY AUTO_INCREMENT) TYPE=InnoDB");

echo "insert a value without transaction:\n";
mysql_query("INSERT INTO lastinsert VALUES ('')");
echo "\t mysql_insert_id():".mysql_insert_id()."\n";

echo "insert a value with transaction:\n";
mysql_query("START TRANSACTION");
mysql_query("INSERT INTO lastinsert VALUES ('')");
echo "\t before commit:mysql_insert_id():".mysql_insert_id()."\n";
mysql_query("COMMIT");
echo "\t after commit:mysql_insert_id():".mysql_insert_id()."\n";
$res = mysql_query("SELECT LAST_INSERT_ID()");
$row = mysql_fetch_row($res);
echo "\t SQL LAST_INSERT_ID():".$row[0]."\n";

echo "insert a value with transaction:\n";
mysql_query("START TRANSACTION");
mysql_query("INSERT INTO lastinsert VALUES ('')");
echo "\t before rollback:mysql_insert_id():".mysql_insert_id()."\n";
mysql_query("ROLLBACK");
echo "\t after rollback:mysql_insert_id():".mysql_insert_id()."\n";
$res = mysql_query("SELECT LAST_INSERT_ID()");
$row = mysql_fetch_row($res);
echo "\t SQL LAST_INSERT_ID():".$row[0]."\n";

echo "Content of table:\n";
$res = mysql_query("SELECT * FROM lastinsert");
while ($row = mysql_fetch_row($res)) {
echo "\t$row[0]\n";
}

mysql_query("DROP DATABASE insertidtest");
mysql_close();

This entry was posted on Wednesday, March 22nd, 2006 at 11:58 am and is filed under Mysql, PHP. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply