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