
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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)
- Related Articles
- How do I view the auto_increment value for a table in MySQL?
- How do I get the current AUTO_INCREMENT value for a table in MySQL?
- Can we insert records in a MySQL table without auto_increment values?
- Create table query with manual AUTO_INCREMENT start value in MySQL?
- Set AUTO_INCREMENT in a table while creating it in MySQL?
- MySQL INSERT INTO SELECT into a table with AUTO_INCREMENT
- Fix Drop table view #1051 unknown table error in MySQL
- Select the maximum for each value in a MySQL table?
- How to create a table from view in MySQL?
- Setting similar value for a column in a MySQL table?
- MySQL: What is 'AUTO_INCREMENT=5' in a create table query?
- Check if table exist without using “select from” in MySQL?
- How to insert auto_increment in an already created table in MySQL?
- Insertion in a MySQL table with only a single column set as auto_increment?
- Set value only for NULL values in a MySQL table

Advertisements