Get the new record primary key ID from MySQL insert query - MySQL Developer Tutorial

Get the new record primary key ID from MySQL insert query

Often as MySQL developers we want to get the Primary key ID after inserting the record. We can use Last_insert_id() function to returned the last value for Primary Key Auto Incremented for the session in which we executed the inserted.

SELECT LAST_INSERT_ID();

Example :

Let's create a sample table and then insert couple of records and then run the functions to see what value is returned by LAST_INSERT_ID( ) function.

CREATE TABLE `customer` (
  `idcustomer` int(11) NOT NULL auto_increment,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL,
  Constraint pk_idcustomer Primary key (idcustomer)
) ;

Insert sample records in customer table.

insert into customer
(firstname,lastname,age,phonenumber,dob,gender)
values
('Aamir1',
'Shahzad1',
39,
'505-4141969',
'1980-01-01',
'M');

If you run the insert couple of time and then run "SELECT LAST_INSERT_ID();" you will get the last id that was inserted in that sessions.

If there are other sessions open for insert, you are not going to get the last id but only the last id which was inserted in specific session. Remember LAST_INSERT_ID() returns the last primary key auto increment value by session.

To understand details, you can watch the video demo below.