How AUTO_INCREMENT Works in MySQL

In MySQL, we can include the AUTO_INCREMENT attribute within a column definition in order to create an auto-incrementing column.

Generally, when we do this MySQL will automatically generate a value for us whenever we insert a new row into the table. I say “generally” because we can still explicitly insert our own value if that’s required.

Example

Let’s jump straight in with an example:

CREATE TABLE Idiots (
    IdiotId int NOT NULL AUTO_INCREMENT,
    IdiotName varchar(255) NOT NULL,
    PRIMARY KEY (IdiotId)
);

Here, I applied AUTO_INCREMENT against the IdiotId column. Now whenever I insert a new row, if I don’t provide a value for that column, MySQL will automatically generate one for me.

Let’s insert a couple of rows:

INSERT INTO Idiots ( IdiotName ) 
VALUES 
    ( 'Dumb' ),
    ( 'Dumber' );

Result:

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

In this case I provided a value for the IdiotName column, but not the IdiotId column.

Let’s select all rows from our table:

SELECT * FROM Idiots;

Result:

+---------+-----------+
| IdiotId | IdiotName |
+---------+-----------+
|       1 | Dumb      |
|       2 | Dumber    |
+---------+-----------+

We can see that MySQL automatically inserted values into the IdiotId column. These values are automatically incrementing values. If we inserted another two rows, MySQL would insert 3 and 4 respectively.

That is, unless we provide our own value.

Overriding `AUTO_INCREMENT`

Even though the idea behind AUTO_INCREMENT is to tell MySQL to automatically generate a unique value for our column, we aren’t forced to rely on it. We can still provide our own value if that’s what’s required.

To do this, all we need to do is provide a value like we would provide any other value.

Example:

INSERT INTO Idiots ( IdiotId, IdiotName ) 
VALUES 
    ( 101, 'Dumbest' ),
    ( 102, 'Homer' );

SELECT * FROM Idiots;

Result:

+---------+-----------+
| IdiotId | IdiotName |
+---------+-----------+
|       1 | Dumb      |
|       2 | Dumber    |
|     101 | Dumbest   |
|     102 | Homer     |
+---------+-----------+

So this time I provided 101 and 102 and MySQL had no problem in accepting those values.

When we do this, the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.

Assigning Zero to an `AUTO_INCREMENT` Column

Another way to have MySQL automatically generate a value is to pass a value of 0. So instead of omitting the column from our INSERT statement, we explicitly pass 0:

INSERT INTO Idiots ( IdiotId, IdiotName ) 
VALUES 
    ( 0, 'Peter Griffin' ),
    ( 0, 'Patrick Star' );

SELECT * FROM Idiots;

Result:

+---------+---------------+
| IdiotId | IdiotName     |
+---------+---------------+
|       1 | Dumb          |
|       2 | Dumber        |
|     101 | Dumbest       |
|     102 | Homer         |
|     103 | Peter Griffin |
|     104 | Patrick Star  |
+---------+---------------+

So we can see that AUTO_INCREMENT simply continued the sequence from the previous insert.

However, if your SQL mode has NO_AUTO_VALUE_ON_ZERO enabled, then this won’t work. You can get around this by either disabling NO_AUTO_VALUE_ON_ZERO, explicitly inserting NULL (but only if the column is defined as NOT NULL), or by simply omitting the column from the INSERT operation.

Assigning `NULL` to an `AUTO_INCREMENT` Column

As mentioned, we can alternatively insert NULL to have the column generate the next sequential value:

INSERT INTO Idiots ( IdiotId, IdiotName ) 
VALUES 
    ( NULL, 'Ed' ),
    ( NULL, 'Ralph Wiggum' );

SELECT * FROM Idiots;

Result:

+---------+---------------+
| IdiotId | IdiotName     |
+---------+---------------+
|       1 | Dumb          |
|       2 | Dumber        |
|     101 | Dumbest       |
|     102 | Homer         |
|     103 | Peter Griffin |
|     104 | Patrick Star  |
|     105 | Ed            |
|     106 | Ralph Wiggum  |
+---------+---------------+

Inserting Duplicate Values

If we try to explicitly insert a value that already exists in the AUTO_INCREMENT column, we’ll get an error:

INSERT INTO Idiots ( IdiotId, IdiotName ) 
VALUES ( 1, 'Cosmo' );

Result:

ERROR 1062 (23000): Duplicate entry '1' for key 'idiots.PRIMARY'

However, If we use the IGNORE clause, or disable strict mode, we’ll get a warning:

INSERT IGNORE INTO Idiots ( IdiotId, IdiotName ) 
VALUES ( 1, 'Cosmo' );

Result:

Query OK, 0 rows affected, 1 warning (0.00 sec)

In this case we can simply change the value to zero, NULL, or omit it altogether in order to generate the next sequential value for the column:

INSERT IGNORE INTO Idiots ( IdiotId, IdiotName ) 
VALUES ( 0, 'Cosmo' );

SELECT * FROM Idiots;

Result:

+---------+---------------+
| IdiotId | IdiotName     |
+---------+---------------+
|       1 | Dumb          |
|       2 | Dumber        |
|     101 | Dumbest       |
|     102 | Homer         |
|     103 | Peter Griffin |
|     104 | Patrick Star  |
|     105 | Ed            |
|     106 | Ralph Wiggum  |
|     107 | Cosmo         |
+---------+---------------+

Reset the `AUTO_INCREMENT` Value

As shown in a previous example, one way of resetting the AUTO_INCREMENT value is to explicitly specify a value when performing an INSERT operation.

Another way to reset the AUTO_INCREMENT value is to modify the table:

ALTER TABLE Idiots AUTO_INCREMENT = 1001;

Result:

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

This allows us to reset the value without inserting a new value.

Now when we insert a new value, the AUTO_INCREMENT column will start at that value (unless we explicitly provide a different value):

INSERT IGNORE INTO Idiots ( IdiotId, IdiotName ) 
VALUES ( 0, 'Richard Watterson' );

SELECT * FROM Idiots;

Result:

+---------+-------------------+
| IdiotId | IdiotName         |
+---------+-------------------+
|       1 | Dumb              |
|       2 | Dumber            |
|     101 | Dumbest           |
|     102 | Homer             |
|     103 | Peter Griffin     |
|     104 | Patrick Star      |
|     105 | Ed                |
|     106 | Ralph Wiggum      |
|     107 | Cosmo             |
|    1001 | Richard Watterson |
+---------+-------------------+

We can also use the same technique to set the initial AUTO_INCREMENT value when we create the table.

Get the Latest `AUTO_INCREMENT` Value

MySQL has a LAST_INSERT_ID() function that we can use to retrieve the last inserted AUTO_INCREMENT value:

SELECT LAST_INSERT_ID();

Result:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|             1001 |
+------------------+

We can also use the the mysql_insert_id() C API function.

These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

Shorthands for `AUTO_INCREMENT` Columns

MySQL provides some aliases that allow us to define columns with various attributes that include the AUTO_INCREMENT attribute.

In particular:

Defining a column as a SERIAL type defines the column as BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
SERIAL DEFAULT VALUE can be defined on integer columns as an alias for NOT NULL AUTO_INCREMENT UNIQUE.

Example

Overriding AUTO_INCREMENT

Assigning Zero to an AUTO_INCREMENT Column

Assigning NULL to an AUTO_INCREMENT Column

Inserting Duplicate Values

Reset the AUTO_INCREMENT Value

Get the Latest AUTO_INCREMENT Value

Shorthands for AUTO_INCREMENT Columns

Overriding `AUTO_INCREMENT`

Assigning Zero to an `AUTO_INCREMENT` Column

Assigning `NULL` to an `AUTO_INCREMENT` Column

Reset the `AUTO_INCREMENT` Value

Get the Latest `AUTO_INCREMENT` Value

Shorthands for `AUTO_INCREMENT` Columns