Access last inserted row in MySQL?

If you are AUTO_INCREMENT with column, then you can use last_insert_id() method. This method gets the ID of the last inserted record in MySQL.

The syntax is as follows

SELECT LAST_INSERT_ID();

To understand the above syntax, let us create a table. The query to create a table is as follows

mysql> create table LastInsertedRow    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > UserName varchar(20),      - > UserAge int    - > ); Query OK, 0 rows affected (0.56 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into LastInsertedRow(UserName,UserAge) values('John',23); Query OK, 1 row affected (0.19 sec) mysql> insert into LastInsertedRow(UserName,UserAge) values('Carol',24); Query OK, 1 row affected (0.16 sec) mysql> insert into LastInsertedRow(UserName,UserAge) values('Bob',24); Query OK, 1 row affected (0.08 sec) mysql> insert into LastInsertedRow(UserName,UserAge) values('Larry',26); Query OK, 1 row affected (0.09 sec) mysql> insert into LastInsertedRow(UserName,UserAge) values('Maxwell',27); Query OK, 1 row affected (0.10 sec) mysql> insert into LastInsertedRow(UserName,UserAge) values('David',22); Query OK, 1 row affected (0.10 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from LastInsertedRow;

The following is the output

+----+----------+---------+ | Id | UserName | UserAge | +----+----------+---------+ |  1 | John     | 23 | |  2 | Carol    | 24 | |  3 | Bob      | 24 | |  4 | Larry    | 26 | |  5 | Maxwell | 27 | |  6 | David | 22 | +----+----------+---------+ 6 rows in set (0.00 sec)

Here is the query to get the last inserted row

mysql> select last_insert_id();

The following is the output

+------------------+ | last_insert_id() | +------------------+ | 6 | +------------------+ 1 row in set (0.00 sec)

If you want the entire row, then use the following query

mysql> select *from LastInsertedRow where Id=(SELECT LAST_INSERT_ID());

The following is the output

+----+----------+---------+ | Id | UserName | UserAge | +----+----------+---------+ | 6 | David | 22 | +----+----------+---------+ 1 row in set (0.05 sec)