MySQL has The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows, there many useful cases and things to consider when using this attribute. In this article, I'm going to describe most commonly used 3 cases, creating a column with this attribute, reset the auto_increment value, and update auto_increment value to one and multiple rows.

 

1. Creating a table with auto_increment attribute set to one column

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

2. Start or Reset an auto_increment value

CREATE TABLE animals (
...
AUTO_INCREMENT = 100;

Reset

ALTER TABLE tbl AUTO_INCREMENT = 100;

3. Update existing rows auto_increment value

For one row, just do regular update

update animals set id=1 where id=100;

For more rows, use varables, for example, you want reset certain range of ids for animals.

set @rowid:=300;
update animals set id=@rowid:=@rowid+1 where id<10 order by id;

 

For more detail and specific cases, check out the link below

http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html