Get Last Inserted Id Of A MySQL Table

Here we will show how you can get the last inserted id of a MySQL table using different ways.

First we are preparing the sample data. We will be creating a table user_info with some data.

Below is sample table .

				
					--
-- Table structure for table `user_info`
--
CREATE TABLE `user_info` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `user_info`
--
INSERT INTO `user_info` (`id`, `name`, `email`, `age`) VALUES
(1, 'Amit', 'amit@gmail.com', 20),
(2, 'Anil', 'anil@hotmail.com', 21),
(3, 'Mahesh', 'mahesh@gmail.com', 20),
(4, 'Arvind', 'arvind@outlook.com', 24),
(5, 'Amit', 'amit@gmail.com', 20),
(6, 'Vedant', 'vedant@yahoo.com', 21),
(7, 'Rajendra', 'rajendra@outlook.com', 23),
(8, 'Arvind', 'arvind@outlook.com', 24),
(9, 'Kuldeep', 'kuldeep@outlook.com', 19);
--
-- Indexes for table `user_info`
--
ALTER TABLE `user_info`
  ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for table `user_info`
--
ALTER TABLE `user_info`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
COMMIT;

1). Using max() function to get last inserted id of a MySQL table

Here we using max () function to retrieve maximum value of particular column.

				
					SELECT MAX(id) FROM `user_info`;

OUTPUT

2). Using ORDER BY DESC with LIMIT get last inserted id of a MySQL table

We use ORDER BY DESC on the returned id values from a select query to get the last inserted id. The query will first select all the values from the id column followed by ordering them in descending order. Finally, only one record is returned as a LIMIT clause is applied.

				
					SELECT `id` FROM `user_info` ORDER BY `id` DESC LIMIT 1;

OUTPUT

3). Using LAST_INSERT_ID() get last inserted id of a MySQL table

We use LAST_INSERT_ID() function to get the last inserted id. This MySQL function returns the value for an insert statement on an auto_increment column.

				
					SELECT LAST_INSERT_ID();
/* If above query not work for you or return 0. So you can use below query.*/
SELECT LAST_INSERT_ID(ID) FROM `user_info` ORDER BY LAST_INSERT_ID(ID) DESC LIMIT 1;

OUTPUT

I hope this article helps you.
Thanks for visiting Inflay.com .