Is there a way to get MySQL to return the row produced by an INSERT command?

I've done basic MySQL for a while now, but nothing beyond that. I'm wondering if there's a way to get the row echoed back to me, or something, when开发者_JAVA技巧 I insert a new row into a table. Basically I have a table with an autonumbered field, and I want to be able to get that autonumber as quickly and painlessly as possible. If I can't have the row returned to me, is there something else I should do short of just running a second query for the highest autonum? Wouldn't that allow for the (slight, very very slight) chance that the table has been updated by some other process or user, and that is not the right number anymore? Currently I just do a search for a row with exactly the contents that I just input, and even that seems to be causing me problems.

I'm using PHP 5.1, and MySQL 5.0.45 it looks like.

Thanks for your help.

You could use LAST_INSERT_ID() which is works in the scope of current connection.

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

More info here.

Try this function : mysql_insert_id

It returns the ID generated in the last query

To use mysql_insert_id is ok, but it you truly want to avoid race conditions, just do a select after the insert with the values that you've just inserted. If these values could not be unique, insert also a timestamp calculated previously. http://www.php.net/manual/en/function.mysql-insert-id.php#95584:

<?php
$date = date('U');
$db->query("INSERT INTO TABLE table VALUES('', 'a', 'b', 'c', 'd', '". $date ."'");
$res = $db->query("SELECT id FROM table WHERE a_col = 'a' AND b_col = 'b' AND c_col = 'c' AND d_col = 'd' AND temp_id = '".$date."'");
?>