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
- Dozens more answers to tough MySQL questions from Mike Hillyer are available here.
- The Best MySQL Web Links: tips, tutorials, scripts, and more.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, Sybase, MySQL, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in May 2004