ProblemAfter creating a row that includes a new sequence number, you want to find out what that number is. SolutionIssue a SELECT LAST_INSERT_ID( ) statement. If you're writing a program, your MySQL API may provide a way to get the value directly without issuing a statement. DiscussionMany applications need to determine the AUTO_INCREMENT value of a newly created row. For example, if you get ambitious and write a web-based frontend for entering rows into Junior's insect table, you might have the application display each new row nicely formatted in a new page immediately after you hit the Submit button. To do this, you need to know the new id value so that you can retrieve the proper row. Another common situation in which the AUTO_INCREMENT value is needed occurs when you're using multiple tables: after inserting a row in a master table, typically, you'll need its ID so that you can create rows in other related tables that refer to the master row. (Section 11.13 shows how to relate multiple tables using sequence numbers.) When you generate a new AUTO_INCREMENT value, you can get the value from the server by issuing a statement that invokes the LAST_INSERT_ID( ) function. In addition, many MySQL APIs provide a client-side mechanism for making the value available without issuing another statement. This recipe discusses both methods and provides a comparison of their characteristics. Using LAST_INSERT_ID( ) to obtain AUTO_INCREMENT valuesThe obvious (but incorrect) way to determine a new row's AUTO_INCREMENT value is based on the fact that when MySQL generates the value, it becomes the largest sequence number in the column. Thus, you might try using the MAX( ) function to retrieve it: SELECT MAX(id) FROM insect; This is unreliable because it doesn't take into account the multithreaded nature of the MySQL server. The SELECT statement does indeed return the maximum id value from the table, but it may not be the value that you generated. Suppose that you insert a row that generates an id value of 9. If another client inserts a row before you issue the SELECT statement, MAX(id) returns 10, not 9. Methods for solving this problem include grouping the INSERT and SELECT statements as a transaction or locking the table, but MySQL provides a LAST_INSERT_ID( ) function as a simpler way to obtain the proper value. It returns the most recent AUTO_INCREMENT value that you generated during the time you've been connected to the server, regardless of what other clients are doing. For example, you can insert a row into the insect table and then retrieve its id value like this: mysql> INSERT INTO insect (name,date,origin) -> VALUES('cricket','2006-09-11','basement'); mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 9 | +------------------+ Or you can use the new value to retrieve the entire row, without even knowing what the id is: mysql> INSERT INTO insect (name,date,origin) -> VALUES('moth','2006-09-14','windowsill'); mysql> SELECT * FROM insect WHERE id = LAST_INSERT_ID(); +----+------+------------+------------+ | id | name | date | origin | +----+------+------------+------------+ | 10 | moth | 2006-09-14 | windowsill | +----+------+------------+------------+
Using API-specific methods to obtain AUTO_INCREMENT valuesLAST_INSERT_ID( ) is an SQL function, so you can use it from within any client that understands how to issue SQL statements. On the other hand, you do have to issue a separate statement to get its value. If you're writing your own programs, you may have another choice. Many MySQL interfaces include an API-specific extension that returns the AUTO_INCREMENT value without issuing another statement. Most of our APIs have this capability.
Server-side and client-side sequence value retrieval comparedAs mentioned earlier, the value of LAST_INSERT_ID( ) is maintained on a connection-specific basis on the server side of the MySQL connection. By contrast, the API-specific methods for accessing AUTO_INCREMENT values directly are implemented on the client side. Server-side and client-side sequence value retrieval methods have some similarities, but also some differences. All methods, both server-side and client-side, require that you must access the AUTO_INCREMENT value using the same MySQL connection that was used to generate the value in the first place. If you generate an AUTO_INCREMENT value, and then disconnect from the server and reconnect before attempting to access the value, you'll get zero. Within a given connection, the persistence of AUTO_INCREMENT values can be much longer on the server side of the connection:
|