MySQL supports AUTO_INCREMENT column types where the value of a particular column can be auto incremented as, and when, the records are inserted.
Many instances would need the last value that was inserted to be used in reporting or some computations.
This can be achieved in the following ways and the results also differ. So be careful in choosing what you need and then use the appropriate method.
1. LAST_INSERT_ID() This method gives you the LAST_INSERT_ID across the database connection and is not specific to a table.
So, if there are 2 or more tables on which you have performed INSERT operations in the same connection and if you use this query, the output will be of the last value in the second table. The problem arises when the tables are not related and number of records do not match. If you need the value of the first table, this query has to be executed before you insert records into any other table that uses the AUTO_INCREMENT feature.
Syntax:
SELECT LAST_INSERT_ID();
Output:
+-------------------+| LAST_INSERT_ID() |+-------------------+| 5 |+-------------------+
2. Customized query
SELECT (AUTO_INCREMENT - 1) AS LAST_INSERT_ID FROM INFORMATION_SCHEMA.TABLEs WHERE TABLE_NAME = '' and TABLE_SCHEMA = ' '
Replace the table_name and schema_name of the respective tables and execute the query.
Output:
If there are 2 table which are using AUTO_INCREMENT feature, and they have 2 records and 5 records inserted, the following will be the results of the respective tables.
+-------------------+| LAST_INSERT_ID() |+-------------------+| 2 |+-------------------++-------------------+| LAST_INSERT_ID() |+-------------------+| 5 |+-------------------+
