Auto Increment in MySQL

MySQL autoincrement is used to automatically generate unique numbers for new rows.

Usually, it used for identifiers.

Let’s write SQL statement to create a new table with autoincrement id column.

CREATE TABLE user (
  id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
  name VARCHAR(255) NOT NULL
);

Every time when you insert a new row into the table new id will be generated:

INSERT INTO user (name) VALUES ('John');
INSERT INTO user (name) VALUES ('Dean');
INSERT INTO user (name) VALUES ('Tom');

As you can see I don’t provide any values for id columns.

Let’s take a look what is inside of the table:

SELECT * FROM user;

Output:

idname
1John
2Dean
3Tom

As you can see identifiers generated automatically.

How to Make Existing Column AUTO_INCREMENT in MySQL

If you have a table and you want to set an auto-increment to existing column you can execute the following SQL statement:

ALTER TABLE user CHANGE COLUMN id id INT AUTO_INCREMENT;

Note that this column should contain unique only values, otherwise:

ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '3' for key 'PRIMARY'

How to Reset Auto Increment in MySQL

By default autoincrement column starts with 1 and increments 1 every insert.

If you want to reset an auto-increment counter for MySQL table:

ALTER TABLE user AUTO_INCREMENT = 200;

Now MySQL auto-increment column value is reseted and starts with 200, next is 201 etc.

You can use some graphical tools to do the same like PHPMyAdmin, MySQL Workbench as well.

Do you have any question? Ask me in comments.

Leave a Comment