Getting the autonumber value

How do you get the autonumber after an insert?

The easiest way to get the AUTO_INCREMENT value produced by an INSERT statement is to use the LAST_INSERT_ID() function.

Here's an example:

INSERT INTO mytable(col1, col2) VALUES(NULL, 'This is a test');

#We insert a NULL into col1 as in this example col1 is an AUTO_INCREMENT column.

INSERT INTO myOtherTable(colA, colB) VALUES(LAST_INSERT_ID(), 'Another Test');
This will insert the value that was auto-generated into the second table. LAST_INSERT_ID() is connection-specific, meaning that you do not have to worry about other users making INSERTs before you can retrieve the value. It will always return the last AUTO_INCREMENT value generated by your session.

For More Information

This was first published in May 2004

Dig deeper on Open source databases

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: