by
SQL Tutorial › Database Design › AUTO_INCREMENT

SQL AUTO_INCREMENT

BeginnerLesson 56 of 698 min read4 queries run on MySQL 8.4

AUTO_INCREMENT makes MySQL generate the next number for a column every time you insert a row, so you never have to work out a new id yourself. It is the standard way to create surrogate primary keys. This lesson shows how it counts, how to get the id you just created, and why the numbers sometimes skip.

Advertisement
In this lesson
  • Create an AUTO_INCREMENT primary key
  • Get the new id with LAST_INSERT_ID()
  • Set or reset the starting value
  • Understand gaps in the sequence

Define an AUTO_INCREMENT column

MySQL
CREATE TABLE tickets (  ticket_id INT AUTO_INCREMENT PRIMARY KEY,  title     VARCHAR(60) NOT NULL);INSERT INTO tickets (title) VALUES ('Login fails on Safari');INSERT INTO tickets (title) VALUES ('Report export is slow'), ('Typo on pricing page');SELECT * FROM tickets;
ResultMySQL 8.4
mysql> CREATE TABLE tickets (
Query OK, 0 rows affected
mysql> INSERT INTO tickets (title) VALUES ('Login fails on Safari')
Query OK, 1 row affected
mysql> INSERT INTO tickets (title) VALUES ('Report export is slow'), ('Typo o…
Query OK, 2 rows affected
mysql> SELECT * FROM tickets
ticket_idtitle
1Login fails on Safari
2Report export is slow
3Typo on pricing page
3 rows in set
How this query works, step by step
  1. ticket_id is not given in the inserts; MySQL fills in 1, 2 and 3.
  2. The column must be a key (usually the primary key), and a table can have only one AUTO_INCREMENT column.
  3. Inserting NULL or 0 into the column also generates the next value.
Advertisement

LAST_INSERT_ID()

After an insert, LAST_INSERT_ID() returns the id that was generated, which you need to insert child rows:

MySQL
CREATE TABLE tickets (ticket_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(60) NOT NULL);CREATE TABLE ticket_comments (comment_id INT AUTO_INCREMENT PRIMARY KEY, ticket_id INT NOT NULL, body VARCHAR(100));INSERT INTO tickets (title) VALUES ('Checkout button missing');INSERT INTO ticket_comments (ticket_id, body) VALUES (LAST_INSERT_ID(), 'Reproduced on Chrome');SELECT t.ticket_id, t.title, c.body FROM tickets t JOIN ticket_comments c ON c.ticket_id = t.ticket_id;
ResultMySQL 8.4
mysql> CREATE TABLE tickets (ticket_id INT AUTO_INCREMENT PRIMARY KEY, title …
Query OK, 0 rows affected
mysql> CREATE TABLE ticket_comments (comment_id INT AUTO_INCREMENT PRIMARY KE…
Query OK, 0 rows affected
mysql> INSERT INTO tickets (title) VALUES ('Checkout button missing')
Query OK, 1 row affected
mysql> INSERT INTO ticket_comments (ticket_id, body) VALUES (LAST_INSERT_ID()…
Query OK, 1 row affected
mysql> SELECT t.ticket_id, t.title, c.body FROM tickets t JOIN ticket_comment…
ticket_idtitlebody
1Checkout button missingReproduced on Chrome
1 row in set
How this query works, step by step
  1. The ticket insert generates ticket_id = 1.
  2. LAST_INSERT_ID() returns 1, so the comment is attached to the right ticket.
  3. The value is per connection: other users inserting at the same time cannot change what your session sees.
  4. For a multi-row insert it returns the id of the first inserted row.
Advertisement

Choose the starting value

MySQL
CREATE TABLE invoices (  invoice_no INT AUTO_INCREMENT PRIMARY KEY,  amount     INT NOT NULL) AUTO_INCREMENT = 1001;INSERT INTO invoices (amount) VALUES (4999), (1250);SELECT * FROM invoices;
ResultMySQL 8.4
mysql> CREATE TABLE invoices (
Query OK, 0 rows affected
mysql> INSERT INTO invoices (amount) VALUES (4999), (1250)
Query OK, 2 rows affected
mysql> SELECT * FROM invoices
invoice_noamount
10014999
10021250
2 rows in set

Change it later with ALTER TABLE invoices AUTO_INCREMENT = 5000; (it cannot be set below the current maximum).

Why there are gaps

Auto-increment values are never reused. Deleted rows, rolled-back inserts and failed inserts all leave gaps:

MySQL
CREATE TABLE tickets (ticket_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(60) NOT NULL UNIQUE);INSERT INTO tickets (title) VALUES ('A'), ('B'), ('C');DELETE FROM tickets WHERE ticket_id = 3;INSERT INTO tickets (title) VALUES ('D');INSERT IGNORE INTO tickets (title) VALUES ('A');INSERT INTO tickets (title) VALUES ('E');SELECT * FROM tickets;
ResultMySQL 8.4
mysql> CREATE TABLE tickets (ticket_id INT AUTO_INCREMENT PRIMARY KEY, title …
Query OK, 0 rows affected
mysql> INSERT INTO tickets (title) VALUES ('A'), ('B'), ('C')
Query OK, 3 rows affected
mysql> DELETE FROM tickets WHERE ticket_id = 3
Query OK, 1 row affected
mysql> INSERT INTO tickets (title) VALUES ('D')
Query OK, 1 row affected
mysql> INSERT IGNORE INTO tickets (title) VALUES ('A')
Query OK, 0 rows affected, 1 warning
mysql> INSERT INTO tickets (title) VALUES ('E')
Query OK, 1 row affected
mysql> SELECT * FROM tickets
ticket_idtitle
1A
2B
4D
6E
4 rows in set
How this query works, step by step
  1. A, B, C get 1, 2, 3. Deleting 3 does not free the number: D gets 4.
  2. INSERT IGNORE of the duplicate 'A' fails the unique check but has already used up number 5.
  3. E therefore gets 6.
Tip

Treat auto-increment ids as unique labels, not as a count or an order of events. If you need gap-free invoice numbers for legal reasons, generate them separately inside a transaction.

Advertisement

TRUNCATE resets the counter

DELETE FROM t keeps the counter; TRUNCATE TABLE t resets it to 1 (see DELETE & TRUNCATE).

In other databases

DatabaseSyntax
MySQLid INT AUTO_INCREMENT PRIMARY KEY
PostgreSQLid INT GENERATED ALWAYS AS IDENTITY (or SERIAL)
SQL Serverid INT IDENTITY(1,1) PRIMARY KEY
SQLiteid INTEGER PRIMARY KEY (auto-assigned)
Oracle 12c+id NUMBER GENERATED ALWAYS AS IDENTITY

Quick quiz

1. How do you get the id generated by your last insert in MySQL?

2. A row with id 7 is deleted. Is 7 reused?

3. Why is SELECT MAX(id) a bad way to find your new id?

Advertisement

AUTO_INCREMENT FAQ

What is AUTO_INCREMENT in MySQL?

A column attribute that makes MySQL assign the next integer automatically when a row is inserted without a value for that column.

How do I reset AUTO_INCREMENT in MySQL?

TRUNCATE TABLE t empties the table and resets it, or ALTER TABLE t AUTO_INCREMENT = n sets the next value (at least the current maximum + 1).

Why does my AUTO_INCREMENT skip numbers?

Deletes, rolled-back transactions, failed inserts and INSERT IGNORE / ON DUPLICATE KEY all consume values without leaving rows.