MySQL AUTO_INCREMENT with Examples

What is auto increment?

Auto Increment is a function that operates on numeric data types. It automatically generates sequential numeric values every time that a record is inserted into a table for a field defined as auto increment.

The attribute works on any integer type, from TINYINT through to BIGINT. The column must also be indexed, which happens automatically when it is declared as the primary key.

When use auto increment?

In the lesson on database normalization, we looked at how data can be stored with minimal redundancy, by storing data into many small tables, related to each other using primary and foreign keys.

A primary key must be unique, as it uniquely identifies a row in a database. But how can we ensure that the primary key is always unique?

One of the possible solutions would be to use a formula to generate the primary key, which checks for the existence of the key in the table before adding data. This may work, but the approach is complex and not foolproof. Two sessions inserting at the same moment can still read the same maximum value and collide.

In order to avoid such complexity, and to ensure that the primary key is always unique, we can use the MySQL auto increment feature to generate primary keys. Auto increment is used with the INT data type. The INT data type supports both signed and unsigned values. Unsigned data types can only contain positive numbers. As a best practice, it is recommended to define the unsigned constraint on the auto increment primary key.

Auto increment syntax

With the reasoning settled, look at the script used to create the movie categories table.

CREATE TABLE `categories` (
  `category_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_name` varchar(150) DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`category_id`)
);

Notice the “AUTO_INCREMENT” on the category_id field. This causes the category id to be automatically generated every time a new row is inserted into the table. It is not supplied when inserting data into the table, MySQL generates it.

Note: the UNSIGNED keyword doubles the positive range of the column, and the display width once written as int(11) is deprecated from MySQL 8.0.17 onward. Plain int is the current form.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

Let us examine the current contents of the categories table.

SELECT * FROM `categories`;

Executing the above script in MySQL Workbench against the myflixdb gives us the following results.

category_id
category_name
remarks




1
Comedy
Movies with humour


2
Romantic
Love stories


3
Epic
Story acient movies


4
Horror
NULL


5
Science Fiction
NULL


6
Thriller
NULL


7
Action
NULL


8
Romantic Comedy
NULL

Eight rows exist, so the next generated id should be 9. Let us now insert a new category into the categories table, supplying only the name.

INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');

Executing the above script against the myflixdb in MySQL workbench gives us the following results shown below.

category_id
category_name
remarks




1
Comedy
Movies with humour


2
Romantic
Love stories


3
Epic
Story acient movies


4
Horror
NULL


5
Science Fiction
NULL


6
Thriller
NULL


7
Action
NULL


8
Romantic Comedy
NULL


9
Cartoons
NULL

Note that we did not supply the category id. MySQL generated it automatically, because the category id is defined as auto increment.

If you want to get the last insert id that was generated by MySQL, you can use the LAST_INSERT_ID function to do that. The script shown below gets the last id that was generated.

SELECT LAST_INSERT_ID();

Executing the above script gives the last auto increment number generated by the INSERT query. The results are shown below.

Tip: LAST_INSERT_ID() is scoped to your own connection, so a value generated by another user’s insert can never be returned to you by mistake.

How to Set or Reset the AUTO_INCREMENT Start Value

The default sequence begins at 1, but that is not always what a project needs. Invoice numbers may have to continue from a legacy system, and a test table often has to be reset. MySQL exposes the counter directly, so both cases are handled with a single clause. Follow these steps to control the starting number.

1. Set the value at creation time. Append the AUTO_INCREMENT clause to the CREATE TABLE statement. The first row inserted then receives that number instead of 1.
2. Change the value on an existing table. Use ALTER TABLE with the same clause. MySQL accepts the new number only if it is higher than the largest identifier currently stored.
3. Reset a table you have emptied. TRUNCATE TABLE removes all rows and returns the counter to 1 in one operation, which DELETE alone does not do.
4. Confirm the change. Insert a row and read the identifier back with LAST_INSERT_ID() before relying on the new sequence.

-- Start a brand-new table at 1000
CREATE TABLE `invoices` (
  `invoice_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `amount` decimal(10,2),
  PRIMARY KEY (`invoice_id`)
) AUTO_INCREMENT = 1000;

-- Move the counter on an existing table
ALTER TABLE `categories` AUTO_INCREMENT = 100;

-- Empty the table and reset the counter to 1
TRUNCATE TABLE `categories`;

The step size can also be changed with the auto_increment_increment system variable, but it applies to the whole server rather than to one table. It is used mainly in replication, where two servers must not generate the same identifier.

Why Do Gaps Appear in an AUTO_INCREMENT Sequence?

Sooner or later a table shows identifiers such as 1, 2, 5, 6. Nothing is broken. The counter is designed to guarantee uniqueness, not to guarantee an unbroken run of numbers, and it never issues the same value twice.

Gaps appear for the following reasons.

• Deleted rows: when a row is deleted from a table, its auto incremented id is not re-used. MySQL continues generating new numbers sequentially.
• Rolled-back transactions: the number is claimed the moment the insert runs. If the transaction is rolled back, the row disappears but the number is already spent.
• Failed inserts: a statement rejected by a UNIQUE constraint can still consume an identifier before it fails.
• Bulk inserts: InnoDB may reserve a block of numbers for a multi-row insert and discard the ones it does not use.

Trying to close these gaps is a mistake. Renumbering rows breaks every foreign key that points at them, and the value itself carries no business meaning. If a report needs a continuous list, generate the row number in the query instead of rewriting stored data.

FAQs