• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Web development » mysql » MySQL Auto Increment and reset

MySQL Auto Increment and reset

May 1, 2022 by techgoeasy Leave a Comment

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

MySQL Auto Increment reset


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.

MySQL Auto Increment reset

If they delete the highest number, then again holds true

MySQL Auto Increment reset

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

Filed Under: mysql, Web development

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to generate sql monitor report in Oracle
  • How to find the long running (longops session) in Oracle
  • How to find concurrent request id from Sid or spid
  • how to check recycle bin in oracle: Purge, enable/disable
  • how to check fra size in oracle: Location, usage, change size

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us