Bug #2031 | Request: SQL INSERT command to return value of auto-increment field | ||
---|---|---|---|
Submitted: | 6 Dec 2003 12:34 | Modified: | 6 Dec 2003 13:52 |
Reporter: | John Hainsworth | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S4 (Feature request) |
Version: | OS: | ||
Assigned to: | CPU Architecture: | Any |
[6 Dec 2003 12:34]
John Hainsworth
[6 Dec 2003 13:52]
Alexander Keremidarski
John, Thank you for your time and effort, but MySQL already has feature which implements what you request. LAST_INSERT_ID() function. It provides not Atomic only, but also Session safe access to last value inserted in auto_increment column. Works always no matter if it is within transaction or not. It has some important benefits over your proposed syntax: 1. Does not add non-standard modifiers 2. It is avaliable for subsequent queries at server side providing more natural and standard compliant way of using it. 3. It is very old and well tested feature existing since AUTO_INCREMENT columns were implemented. MySQL syntax is: INSERT INTO table1 (column, ...) VALUES (value, ...); INSERT INTO table2 (column, ...) VALUES (LAST_INSERT_ID(), ...); Compare to your proposed syntax and I hope you will agree MySQL one is better. From MySQL Manual http://www.mysql.com/doc/en/Miscellaneous_functions.html LAST_INSERT_ID([expr]) Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. mysql> SELECT LAST_INSERT_ID(); -> 195 The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behaviour ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions. The value of LAST_INSERT_ID() is not changed if you update the AUTO_INCREMENT column of a row with a non-magic value (that is, a value that is not NULL and not 0).
[7 Dec 2003 20:41]
John Hainsworth
Thank you for a prompt and polite response to my post. I agree: the LAST_INSERT_ID() function does exactly what I want, and is much more elegant than anything I thought of. It never even occurred to me to cache the value in the server. Furthermore, there's nothing wrong with the documentation: I did a search for AUTO_INCREMENT, which is what I should have done in the first place, and the LAST_INSERT_ID() function popped right up. John
[24 Jan 2010 0:07]
Juan Pablo Lopez Bergero
Hi, i think is not necesary to open a new tread. I have this problem mysql> SELECT LAST_INSERT_ID (); ERROR 1305 (42000): FUNCTION inventario.LAST_INSERT_ID does not exist mysql> Can somebody help me whit this? thanks
[24 Jan 2010 1:05]
Juan Pablo Lopez Bergero
I already solve it, THANKS!