How to add auto increment in MySQL table?

In this post we will see how to add auto increment in MySQL. Sometime we have a requirement that row value should be incremented by 1 when there is a new record inserted, and we want to do it automatically.

Most of the time we have one field which is usually a unique key in the table. We want to increment the value automatically when a new row is inserted. So, here we will see how we can auto increment the field value every time when a new record is inserted and we don’t have to increase the value manually every time.

In MySQL, There is an AUTO_INCREMENT keyword. By using AUTO_INCREMENT we can increment the row every time.

Add AUTO_INCREMENT during table creation

AUTO_INCREMENT can be added during new table creation. Below is an example of creating AUTO_INCREMENT on id column.

create table assets(id int PRIMARY KEY AUTO_INCREMENT, EMPID int,AssetName Varchar(50));

Let’s put some data in the Assets table.

insert into assets(empid,assetname) values(101,'laptop');
insert into assets(empid,assetname) values(102,'desktop');

Result

select * from assets;
+----+-------+-----------+
| id | EMPID | AssetName |
+----+-------+-----------+
|  1 |   101 | laptop    |
|  2 |   102 | desktop   |
+----+-------+-----------+

We can see the value is automatically put in the id column because we have added auto_increment keyword in the id column.

Add AUTO_INCREMENT in existing table

We can also add Auto_increment keyword in the existing table.

To add AUTO_INCREMENT keyword we will modify the table by using Alter table statement

Alter table access modify id int AUTO_INCREMENT;