MySQL Auto Increment and reset

Auto Increment Column is a quite common requirement in the coding. Here are some of the ways to get that in MySQL . we will also check about MySQL Auto Increment reset

Auto Increment Column MySQL database

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows

Example

use techdb;
CREATE TABLE test_lab (
id INT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) ;

INSERT INTO test_lab (name) VALUES
('john'),('scott'),('bill'),
('tom'),('duke');   

SELECT * FROM test_lab;

Few important points to remember

• We can have only one auto increment column in the table
• if you delete the row from the table, the auto increment will not reuse that value
• it is possible to add an auto increment column after table creation

How to find the next auto increment value id in MySQL

We can determine using the below query

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

For our case, it will be like

SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test" AND TABLE_NAME = "test_lab"

MySQL auto increment reset

We can reset the auto increment with a certain condition

Here is the syntax to reset the auto increment value

ALTER TABLE table_name AUTO_INCREMENT = value;

It is just the table name, as only one column is allowed to be auto increment

If the value, you are going to reset is higher than the current value of Autoincrement, then the next sequence starts with the value given

If the specified is less or equal to the maximum value of the auto-increment column, the value will be reset to the current maximum plus one.

If they delete the highest number, then again holds true

Hope you like this post on setting up Auto Increment , MySQL Auto Increment reset, How to find the next auto increment value id in MySQL

Related Articles

Step-by-Step guide for Installing MySQL on Windows
Auto Increment Column – Sequence as Default Value in Oracle and mysql
Top 51 Frequently Asked MySQL Interview Questions And Answers
Step by step guide to build local Apache PHP MySQL development environment on windows
How to reset MySQL root password