How to Reset an Auto-Increment Value of a Column in MySql

mysqlHow to reset auto-increment value of a column in MySql.

In MySQL, we can define a column as primary key with an auto-increment index. Every time a new row is inserted it increments the value by 1. By default, an auto-increment column is a primary key of a table.

But there might be a situation in which we want to reset an auto-increment value. Suppose you have inserted some rows and later removed it from a table and you want to reset an auto-increment value from 1 or some other value.

To illustrate this concept, let’s create one dummy table.

Insert some dummy values.

Now we have created a table and inserted three rows successfully.

Let’s delete all rows of a table.

If you run delete query all records of a table will be removed. Let’s insert one more record and notice auto-increment value will be 4. So how to reset an auto-increment value.

How to Reset Auto-Increment Value of a Column in MySql

METHOD 1– Reset auto-increment value of a column through Alter command.

Using Alter Command we can reset an auto-increment value of a column.

The above query will reset an auto-increment value to 1. You can specify any other value as well.

Subscribe Our Tutorials

Get Latest Updates on Facebook

METHOD 2 : Using Truncate.

The TRUNCATE TABLE statement removes all the data of a table and resets an auto-increment value to zero.

NOTE : Truncate command will remove all the data from your table.

METHOD 3 : Drop & Recreate existing table

Use this approach if your table is empty (does not have any data). Remove existing table using Drop command and recreate the table.

How to Reset an Auto-Increment Value of a Column in MySql through phpMyAdmin

You can use phpMyAdmin to reset an auto-increment value of a column in MySql.

i) In phpMyAdmin, click on the table you want to reset the AUTO_INCREMENT value.

ii) After that click on the Operations Tab.

iii) In the table options box find the auto_increment field.

iv) Enter the new auto_increment value.

v) Click on the go button for the table options box.

Reset an auto-increment value of a column in MySql

Reset an auto-increment value of a column in MySql

WebRewrite

About WebRewrite

I am technology lover who loves to keep updated with latest technology. My interest field is Web Development.
Tagged , . Bookmark the permalink.