How to get the next auto-increment id in MySQL?

MySQL has the AUTO_INCREMENT keyword to perform auto-increment. The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record.

To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT.

Creating a table, with "d" as auto-increment.

mysql> create table NextIdDemo
   -> (
   -> id int auto_increment,
   -> primary key(id)
   -> );
Query OK, 0 rows affected (1.31 sec)

Inserting records into the table.

mysql> insert into NextIdDemo values(1);
Query OK, 1 row affected (0.22 sec)

mysql>  insert into NextIdDemo values(2);
Query OK, 1 row affected (0.20 sec)

mysql>  insert into NextIdDemo values(3);
Query OK, 1 row affected (0.14 sec)

To display all the records.

mysql> select *from NextIdDemo;

The following is the output.

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.04 sec)

We have inserted 3 records above. Therefore, the next id must be 4.

The following is the syntax to know the next id.

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "yourDatabaseName"
AND TABLE_NAME = "yourTableName"

The following is the query.

mysql> SELECT AUTO_INCREMENT
    -> FROM information_schema.TABLES
    -> WHERE TABLE_SCHEMA = "business"
    -> AND TABLE_NAME = "NextIdDemo";

Here is the output that displays the next auto-increment.

+----------------+
| AUTO_INCREMENT |
+----------------+
|              4 |
+----------------+
1 row in set (0.25 sec)

Arjun Thakur

Updated on: 14-Sep-2023

26K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started