Techie Weblog

Web Hosting & Network Security Guide

HostGator Web Hosting

How to reset Auto Increment value in MySQL

If you like the post, Please Share

Facebooktwittergoogle_pluspinterestlinkedin

How to reset Auto Increment value in MySQL

How to reset Auto Increment value in MySQL

How to reset Auto Increment value in MySQL

How to reset Auto Increment value in MySQL

AUTO_INCREMENT is an important keyword used in MySQL Database to implement autoincrement feature. It means, the value of the field automatically will be increase by 1. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. When we delete all data from a table, the auto_increment value does not reset to 1 automatically. The AUTO_INCREMENT attribute is used to generate a unique identity for new row.

Lets take the following example:

MySQL> CREATE TABLE emp (
       empid INT NOT NULL AUTO_INCREMENT,
       name CHAR(30) NOT NULL,
       PRIMARY KEY (empid)
       );

MySQL> INSERT INTO emp (name) VALUES
       ('Bob'),('Smith'),('Harry');
   

MySQL> SELECT * FROM emp;

This last SQL command will generate the following result:

+-------+---------+
| empid | name    |
+-------+---------+
|  1    | Bob     |
|  2    | Smith   |
|  3    | Harry   |
+-------+---------+

 

Look! We have not entered any value for the field ’empid’, but values as automatically added and increased by 1. This is the main function of AUTO_INCREMENT, i.e., MySQL assigned sequence numbers automatically. If you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.

Now there will be time when we need to reset the value of the AUTO_INCREMENT to a particular number, say 1. To start with an AUTO_INCREMENT value other than the current one, set that value with ALTER TABLE command. You can try the following command to reset the value.

MySQL> ALTER TABLE emp AUTO_INCREMENT = 1;

This will reset the counter to 1.

***How to reset Auto Increment value in MySQL***

 

 

Updated: September 5, 2016 — 6:00 pm

The Author

Sukanta Dutta

The author is writing technical blog for last few years. He shares his knowledge on Computer Networks, Database Technologies, Security Aspects of Network and Database etc. He also likes to hear from the reader of this blog to learn more, so he welcomes guest writing for this blog.

Leave a Reply

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

Techie Weblog © 2015-2017