View the auto_increment value for a table in MySQL without using SHOW TABLE?

To view the auto_increment value for a table, you can use INFORMATION_SCHEMA.TABLES.

Let us first create a table −

mysql> create table viewtheauto_incrementValueForATableDemo
   -> (
   -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> StudentName varchar(20)
   -> );
Query OK, 0 rows affected (0.84 sec)

Following is the query to insert some records in the table using insert command −

mysql> insert into viewtheauto_incrementValueForATableDemo(StudentName)
values('Ramit');
Query OK, 1 row affected (0.23 sec)

mysql> insert into viewtheauto_incrementValueForATableDemo(StudentName) values('Bob');
Query OK, 1 row affected (0.14 sec)

mysql> insert into viewtheauto_incrementValueForATableDemo(StudentName) values('Chris');
Query OK, 1 row affected (0.15 sec)

mysql> insert into viewtheauto_incrementValueForATableDemo(StudentName)
values('Robert');
Query OK, 1 row affected (0.12 sec)

mysql> insert into viewtheauto_incrementValueForATableDemo(StudentName) values('John');
Query OK, 1 row affected (0.46 sec)

Following is the query to display all records from the table using a select statement −

mysql> select * from viewtheauto_incrementValueForATableDemo;

This will produce the following output −

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 1         | Ramit       |
| 2         | Bob         |
| 3         | Chris       |
| 4         | Robert      |
| 5         | John        |
+-----------+-------------+
5 rows in set (0.00 sec)

Following is the query to view the auto_increment value for a table −

mysql> SELECT `AUTO_INCREMENT` AS 'NextAutoIncrementValue'
   -> FROM `information_schema`.`TABLES`
   -> WHERE `TABLE_SCHEMA` = 'test'
   -> AND `TABLE_NAME` = 'viewtheauto_incrementValueForATableDemo';

This will produce the following output −

+------------------------+
| NextAutoIncrementValue |
+------------------------+
| 6                      |
+------------------------+
1 row in set (0.00 sec)

Anvi Jain

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• Set auto increment initial value for MySQL table using ALTER command
• Truncate a MySQL table and then set a custom value to auto increment
• How to create a table with auto-increment column in MySQL using JDBC?
• How to make MySQL table primary key auto increment?
• How do I view the auto_increment value for a table in MySQL?
• How to auto increment with 1 after deleting data from a MySQL table?
• Passing NULL to MySQL for auto increment?
• How to set auto-increment to an existing column in a table using JDBC API?
• Select the maximum for each value in a MySQL table?
• Changing the current count of an Auto Increment value in MySQL?
• Change the Auto Increment counter in MySQL?
• Add a column to a MySQL table which is the result of concatenation of text and value from another auto increment column?
• Fix Drop table view #1051 unknown table error in MySQL
• MySQL query to set my auto increment column ( id ) to zero or reset the value of auto increment field?
• How to set initial value and auto increment in MySQL?