Get the new record key ID from MySQL insert query?

We can get new record key with the help of LAST_INSERT_ID() function from MySQL. First, we will create a table and for inserting record, we will use LAST_INSERT_ID().

Let us create a table with the help of create command.

The query is as follows −

mysql> create table LastInsertRecordIdDemo
   -> (
   -> id int auto_increment,
   -> value varchar(100),
   -> primary key(id)
   -> );
Query OK, 0 rows affected (0.52 sec)

After creating a table, we will insert records and set it using LAST_INSERT_ID() function.

mysql> insert into LastInsertRecordIdDemo values(1,'Low');
Query OK, 1 row affected (0.10 sec)

mysql> insert into LastInsertRecordIdDemo values(LAST_INSERT_ID(),'High');
Query OK, 1 row affected (0.11 sec)

Now we can display all the records with the help of select statement.

The query is as follows −

mysql> select *from LastInsertRecordIdDemo;

The following is the output.

+----+-------+
| id | value |
+----+-------+
|  1 | Low   |
|  2 | High  |
+----+-------+
2 rows in set (0.00 sec)

Now to insert records, we need to add 1 in the function LAST_INSERT_ID (). The query is as follows −

mysql> insert into LastInsertRecordIdDemo values(LAST_INSERT_ID()+1,'Medium');
Query OK, 1 row affected (0.08 sec)

Now we can display all records with the help of select statement.

mysql> select *From LastInsertRecordIdDemo;

The following is the output.

+----+--------+
| id | value  |
+----+--------+
|  1 | Low    |
|  2 | High   |
|  3 | Medium |
+----+--------+
3 rows in set (0.00 sec)

George John

Updated on: 2019-07-30T22:30:23+05:30

633 Views

Kickstart Your Career

Get certified by completing the course

Get Started