How do I view the auto_increment value for a table in MySQL?

In order to view the auto_increment value for a table, you can use SHOW TABLE command.

The syntax is as follows

SHOW TABLE STATUS LIKE 'yourTableName'\G

The syntax is as follows

SELECT `AUTO_INCREMENT`
   FROM `information_schema`.`TABLES`
   WHERE `TABLE_SCHEMA` = ‘yourDatabaseName’
   AND `TABLE_NAME` =’yourTableName';

To understand the above syntaxes, let us create a table. The query to create a table is as follows

mysql> create table viewAutoIncrementDemo
   -> (
   -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> UserName varchar(20)
   -> );
Query OK, 0 rows affected (0.59 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into viewAutoIncrementDemo(UserName) values('John');
Query OK, 1 row affected (0.18 sec)
mysql> insert into viewAutoIncrementDemo(UserName) values('Carol');
Query OK, 1 row affected (0.14 sec)
mysql> insert into viewAutoIncrementDemo(UserName) values('Bob');
Query OK, 1 row affected (0.08 sec)
mysql> insert into viewAutoIncrementDemo(UserName) values('Sam');
Query OK, 1 row affected (0.12 sec)
mysql> insert into viewAutoIncrementDemo(UserName) values('Mike');
Query OK, 1 row affected (0.14 sec)
mysql> insert into viewAutoIncrementDemo(UserName) values('David');
Query OK, 1 row affected (0.16 sec)
mysql> insert into viewAutoIncrementDemo(UserName) values('Larry');
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from viewAutoIncrementDemo;

The following is the output

+--------+----------+
| UserId | UserName |
+--------+----------+
|     1 | John      |
|     2 | Carol     |
|     3 | Bob       |
|     4 | Sam       |
|     5 | Mike      |
|     6 | David     |
|     7 | Larry     |
+--------+----------+
7 rows in set (0.00 sec)

Here is the query to view the auto_increment value for a table

mysql> SHOW TABLE STATUS LIKE 'viewAutoIncrementDemo'\G

The following is the output

*************************** 1. row ***************************
           Name: viewautoincrementdemo
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 7
 Avg_row_length: 2340
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 8
    Create_time: 2019-03-02 04:05:20
    Update_time: 2019-03-02 04:06:11
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.08 sec)

The following is the second query

mysql> SELECT `AUTO_INCREMENT`
   -> FROM `information_schema`.`TABLES`
   -> WHERE `TABLE_SCHEMA` = 'sample'
   -> AND `TABLE_NAME` = 'viewAutoIncrementDemo';

The following is the output

+----------------+
| AUTO_INCREMENT |
+----------------+
| 8              |
+----------------+
1 row in set (0.00 sec)