MySQL Auto Increment

Last Updated: 30 June, 2023

In MySQL, the auto-increment feature allows us to automatically generate unique values for a column when new rows are inserted into a table. This is typically used for primary key columns to ensure each row has a unique identifier.

To define a column as auto-increment in MySQL, we can use the AUTO_INCREMENT attribute when creating or altering the table.

Here's an example of creating a table with an auto-increment column:

 CREATE TABLE my_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  age INT
 );

In this example, the id column is defined as an INT and marked as AUTO_INCREMENT. The PRIMARY KEY constraint is added to the id column to ensure it is unique.

When we insert a new row into the table without specifying a value for the auto-increment column, MySQL will automatically generate a unique value for it.

For example:

 INSERT INTO my_table (name, age) VALUES ('John', 25);

In this case, the id column will be automatically assigned a unique value by MySQL.

We can also retrieve the last generated auto-increment value using the LAST_INSERT_ID() function.

For example:

 INSERT INTO my_table (name, age) VALUES ('Jane', 30);
 SELECT LAST_INSERT_ID();

The LAST_INSERT_ID() function will return the value that was assigned to the id column in the previous INSERT statement.

Note that if we want to explicitly specify a value for the auto-increment column during an INSERT statement, we can do so, but MySQL will ignore the specified value and generate its own unique value instead.