MySQL AUTO_INCREMENT

Summary: in this tutorial, you will learn how to use the MySQL AUTO_INCREMENT attribute to automatically generate unique integer values for a column.

Introduction to MySQL AUTO_INCREMENT attribute

In MySQL, you use the AUTO_INCREMENT attribute to automatically generate unique integer values for a column whenever you insert a new row into the table.

Typically, you use the AUTO_INCREMENT attribute for the primary key column to ensure each row has a unique identifier.

Creating a table with MySQL AUTO_INCREMENT column

To create a table with an auto-increment column, you use the AUTO_INCREMENT attribute:

CREATE TABLE table_name(
    id INT AUTO_INCREMENT PRIMARY KEY,
    ...
);

For example, the following statement creates a table called contacts to store contact data:

CREATE TABLE contacts(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(320) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

In this example, we assign the AUTO_INCREMENT attribute to the id column to set it as an auto-increment primary key.

This means that when you insert a new row into the contacts table without providing a value for the id column, MySQL will automatically generate a unique number.

Inserting rows with AUTO_INCREMENT column

When inserting rows into the table with an AUTO_INCREMENT column, you don’t need to specify a value for that column. MySQL will automatically generate the value for you. For example:

INSERT INTO contacts(name, email)
VALUES('John Doe', '[email protected]');Code language: SQL (Structured Query Language) (sql)

In the INSERT statement, we don’t specify a value for the id column and only provide the values for the name and email columns. MySQL automatically generated the value 1 for the id column:

SELECT * FROM contacts;Code language: SQL (Structured Query Language) (sql)

Output:

+----+----------+----------------------------+
| id | name     | email                      |
+----+----------+----------------------------+
|  1 | John Doe | [email protected] |
+----+----------+----------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Retrieving the last auto-increment value

To get the AUTO_INCREMENT value that MySQL generated for the most recent insert, you use the LAST_INSERT_ID() function:

SELECT LAST_INSERT_ID();Code language: SQL (Structured Query Language) (sql)

Output:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The query returns the last auto-increment value generated for the ID column, which you can use for other purposes such as inserting into a related table.

Resetting the current auto-increment value

To reset the AUTO_INCREMENT value, you use the ALTER TABLE statement:

ALTER TABLE table_name 
AUTO_INCREMENT = value;

Note that the ALTER TABLE statement takes effect only if the value that you want to reset to is higher than or equal to the maximum value in the AUTO_INCREMENT column of the table_name.

For example, the following statement reset the current auto-increment value to 1:

ALTER TABLE contacts 
AUTO_INCREMENT = 1;Code language: SQL (Structured Query Language) (sql)

Alternatively, you can delete all rows from the table and reset the AUTO_INCREMENT value simultaneously. To do that, you use the TRUNCATE TABLE statement:

TRUNCATE TABLE contacts;Code language: SQL (Structured Query Language) (sql)

The following example illustrates how to reset the value in the AUTO_INCREMENT column to an invalid value:

INSERT INTO contacts(name, email) 
VALUES
   ('John Doe', '[email protected]'),
   ('Jane Doe', '[email protected]');Code language: SQL (Structured Query Language) (sql)

The contacts table now has two rows:

+----+----------+----------------------------+
| id | name     | email                      |
+----+----------+----------------------------+
|  1 | John Doe | [email protected] |
|  2 | Jane Doe | [email protected] |
+----+----------+----------------------------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

If you reset the AUTO_INCREMENT column to any number that is less than or equal to 2 using the ALTER TABLE statement, the operation will have no effects. For example:

ALTER TABLE contacts 
AUTO_INCREMENT = 1;Code language: SQL (Structured Query Language) (sql)

Now, if you insert a new row into the contacts table, MySQL will use the next number 3 for the new row. For example:

INSERT INTO contacts(name, email) 
VALUES('Bob Climo', '[email protected]');Code language: SQL (Structured Query Language) (sql)

The following query returns all rows of the contacts table:

SELECT * FROM contacts;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-----------+-----------------------------+
| id | name      | email                       |
+----+-----------+-----------------------------+
|  1 | John Doe  | [email protected]  |
|  2 | Jane Doe  | [email protected]  |
|  3 | Bob Climo | [email protected] |
+----+-----------+-----------------------------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Adding an AUTO_INCREMENT column to an existing table

To add an AUTO_INCREMENT to an existing table, you use the ALTER TABLE statement. For example:

First, create a new table without an AUTO_INCREMENT column:

CREATE TABLE subscribers(
   email VARCHAR(320) NOT NULL UNIQUE
);Code language: SQL (Structured Query Language) (sql)

Second, add the column id to the subscribers table as an AUTO_INCREMENT column:

ALTER TABLE subscribers
ADD id INT AUTO_INCREMENT PRIMARY KEY;Code language: SQL (Structured Query Language) (sql)

Summary

• Assign the AUTO_INCREMENT attribute to a column to instruct MySQL to automatically generate unique integer values for the column.