dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Aug 8, 2019

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


Getting the Last ID in MySQL

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 = '<table_name>' and TABLE_SCHEMA = '<schema_name>'

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 |
+-------------------+
M S Sridhar
 
Thanks for your registration, follow us on our social networks to keep up-to-date