How to reset AUTO_INCREMENT value in mysql table

Some times we delete the records in the user table. and when we insert new records the id is started next to the id which is deleted. 

As for example, We have table users with column id and have 5 records. We deleted the record number 5 now records are 4. Now, when we insert the new record in this table the id of 5th user will become 6.

If we want the id should be in serial and also the 5th inserted record id will be 5 then we have to follow any one method of the following.

Method 1. 

Use this simple sql code and run the query.

ALTER TABLE tablename AUTO_INCREMENT = 1

Method 2. 

SET  @num := 0;

UPDATE your_table_name SET id = @num := (@num+1);

ALTER TABLE your_table_name AUTO_INCREMENT =1;

Method 3.

There is an  another easy way through phpMyAdmin under the "operations" tab. In the table options you can set autoincrement to the number you want.

Method 4.

The id field is dropped and then added back with previous settings. All the existent fields within the database table are filled in with the new auto increment values. This should also work with InnoDB.

Note that all the fields within the table will be reinitialized and will have other ids.

ALTER TABLE users DROP id

ALTER TABLE users ADD  id BIGINT( 200 ) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (id)

Method 5.

You can simply truncate the table to reset the sequence:

TRUNCATE TABLE TABLE_NAME

Method 6.

ALTER TABLE `tablename` CHANGE `id` `id` INT(10) UNSIGNED NOT NULL;

ALTER TABLE `tablename` CHANGE `id` `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

You can send comment or enquiry if issue not solved.