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.

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

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

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



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:

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

By the way…

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:

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.

Scroll Up