AUTO_INCREMENT

AUTO_INCREMENT makes MySQL generate the next number whenever you insert a row without giving that column a value. It is the usual way to create primary keys.

1
2
3
4
5
6
7

CREATE TABLE tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

INSERT INTO tickets (title) VALUES ('Login fails'), ('Typo on homepage'), ('Slow report');
SELECT * FROM tickets;

1
2
3
4
5
6
7
8

+----+------------------+
| id | title            |
+----+------------------+
|  1 | Login fails      |
|  2 | Typo on homepage |
|  3 | Slow report      |
+----+------------------+
3 rows in set

You did not provide id. MySQL gave 1, 2 and 3.

Rules

• A table can have only one AUTO_INCREMENT column.
• That column must be indexed, normally the PRIMARY KEY.
• It must be an integer type. (Since MySQL 8.4, FLOAT and DOUBLE are no longer allowed.)
• You may still insert your own value. MySQL continues from the highest value.

1
2
3

INSERT INTO tickets (id, title) VALUES (100, 'Imported ticket');
INSERT INTO tickets (title) VALUES ('Next one');
SELECT id, title FROM tickets WHERE id >= 100;

1
2
3
4
5
6
7

+-----+-----------------+
| id  | title           |
+-----+-----------------+
| 100 | Imported ticket |
| 101 | Next one        |
+-----+-----------------+
2 rows in set

Get the new ID: LAST_INSERT_ID()

After an insert, your application usually needs the new row’s ID, for example to insert related rows. Use LAST_INSERT_ID():

1
2

INSERT INTO tickets (title) VALUES ('Printer offline');
SELECT LAST_INSERT_ID() AS new_id;

1
2
3
4
5
6

+--------+
| new_id |
+--------+
|    102 |
+--------+
1 row in set

LAST_INSERT_ID() is per connection. Another user inserting at the same moment does not change your value. Never use SELECT MAX(id) for this: it can return someone else’s row. Programming languages expose the same value (PDO::lastInsertId() in PHP, for example).

Gaps are normal

An AUTO_INCREMENT value, once handed out, is not reused, even if the insert fails or is rolled back:

1
2
3
4
5

START TRANSACTION;
INSERT INTO tickets (title) VALUES ('Will be rolled back');
ROLLBACK;
INSERT INTO tickets (title) VALUES ('After rollback');
SELECT id, title FROM tickets ORDER BY id DESC LIMIT 2;

1
2
3
4
5
6
7

+-----+-----------------+
| id  | title           |
+-----+-----------------+
| 104 | After rollback  |
| 102 | Printer offline |
+-----+-----------------+
2 rows in set

The rolled-back row used an ID, so After rollback skipped one number. A failed insert (a duplicate key, for example) can also burn a number. Never rely on IDs being consecutive. Do not use them as invoice numbers if gaps are not allowed.

Starting value and step

1
2
3
4
5
6

CREATE TABLE invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    note VARCHAR(20)
) AUTO_INCREMENT = 1000;
INSERT INTO invoices (note) VALUES ('first');
SELECT id FROM invoices;

1
2
3
4
5
6

+------+
| id   |
+------+
| 1000 |
+------+
1 row in set

Change the counter later with ALTER TABLE invoices AUTO_INCREMENT = 5000;. MySQL will not set it below the current maximum.

Choosing the size

A signed INT stops at 2,147,483,647. When the counter reaches the limit, the next insert fails with Duplicate entry on the maximum value. For a table that could hold more than a billion rows over its life, use BIGINT UNSIGNED.

When not to use it

• Public identifiers. /orders/1042 tells the world how many orders you have and invites guessing. Add a second random identifier (a UUID) for outside use, and keep the integer as the internal key.
• Merging data from several servers. Two servers will produce the same numbers. Use UUIDs or a distributed scheme (UUIDs as Keys).