How to get the next Auto Increment id in MySQL - MySQL Developer Tutorial

How to get the next Auto Increment id in MySQL

When we enable Auto Increment on column in a table in MySQL, every time we insert the record in table, The Auto Increment column value increases by 1 automatically.

Let's say we have created table customer with Auto Increment by using below script.

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)
) ;

Now we are all set to insert the values in table. We don't have to insert anything in idcustomer column as we have set Auto Increment. The first record will have value 1 and then incremented by 1 on each insert.

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

Now let's say you have inserted many records in the customer table and you are interested to know "What will be the next Auto Increment Id" for customer table. you can use below script

SELECT Table_schema,Table_Name,Auto_increment
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "techbrothers"

AND TABLE_NAME = "customer";

In above example, My table exits in database "techbrothers" so I have Table_Schema="techbrothers". You need to update to database/ schema in which your table exits. Also you have to provide Table_Name="YourTableName". In my case I have table "customer".

Get the next Auto Increment value id in MySQL or MairaDB