MySQL Bugs: #2031: Request: SQL INSERT command to return value of auto-increment field

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:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:		OS:
Assigned to:		CPU Architecture:	Any

Description:
I would like the INSERT and REPLACE commands to support an option
to return the value of an auto-increment field
in the inserted or replaced record.

(One can currently fake this feature in an application
with an INSERT followed by a SELECT all enclosed in a transaction,
but this alternative requires a significant amount of database locking
that should be unnecessary)

Thank you for considering my request.
John

How to repeat:
You can fake this missing feature by

CREATE TABLE Customer (
  id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
  name CHAR(50) NOT NULL;
# Note: 'name' is not necessarily unique, but we need a unique 'id' anyway

...

START TRANSACTION;
INSERT INTO Customer (name) VALUES ('John');
SELECT MAX(id) FROM Customer;
COMMIT;

I am requesting an atomic command that would make this transaction locking
unnecessary.

Suggested fix:
I suggest adding one non-standard modifier,
called perhaps RETURN_AUTO_INCREMENT, to the SQL language,
according to the following semantics:

 INSERT  RETURN_AUTO_INCREMENT INTO table (column, ...) VALUES (value, ...);
 REPLACE RETURN_AUTO_INCREMENT INTO table (column, ...) VALUES (value, ...);

I think it would be sufficient for this command to return a fatal error
if there is not exactly one auto-increment field in the table
or if that field is not some flavor of INT or LONG.

Alternately the semantics could be

 INSERT RETURN=fieldname INTO table (column, ...) VALUES (value, ...);

if you believe that that feels more consistent with the SQL language.

I don't know where exactly this auto-increment value should be returned.

Another possible solution would be an atomic INSERT_AND_SELECT command:

 INSERT_AND_SELECT
  INTO table (input_column, ...)
  VALUES (input_value, ...)
  SELECT (output_column, ...);

This has the disadvantages of being a new command and supporting lots of unnecessary junk, but has the advantage of being fully consistent
with the SQL language philosophy and possibly allowing for other
interesting and useful performance enhancements.

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).

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

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

I already solve it,
THANKS!