00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL Auto Increment

Last updated:

AUTO_INCREMENT makes MySQL generate a new number for each row you insert: 1, 2, 3 and so on. It is the standard way to create primary keys, so you never have to work out the next ID yourself, even when many users insert at the same time.

1. Creating an AUTO_INCREMENT Column

create.sql 
CREATE TABLE members (
  member_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(40) NOT NULL,
  joined_on DATE NOT NULL DEFAULT (CURRENT_DATE)
);
INSERT INTO members (name) VALUES ('Aarti'), ('Bhavesh'), ('Celine');
SELECT member_id, name FROM members;

Result: 3 rows

member_idname
1Aarti
2Bhavesh
3Celine
  • Only one AUTO_INCREMENT column per table, and it must be indexed (normally the primary key).
  • Use an integer type; UNSIGNED doubles the positive range.
  • Leaving the column out, or inserting NULL or 0, generates the next value.

2. Inserting Rows

insert.sql 
INSERT INTO members (member_id, name) VALUES (NULL, 'Dev');      -- NULL → generated
INSERT INTO members (name) VALUES ('Esha');                      -- omitted → generated
INSERT INTO members (member_id, name) VALUES (100, 'Faraz');     -- explicit value is used
INSERT INTO members (name) VALUES ('Gauri');                     -- continues after 100
SELECT member_id, name FROM members;

Result: 7 rows

member_idname
1Aarti
2Bhavesh
3Celine
4Dev
5Esha
100Faraz
101Gauri
If you insert an explicit value larger than the counter, the counter jumps past it, as with 100 → 101 above.

3. Getting the New ID: LAST_INSERT_ID()

last-insert-id.sql 
INSERT INTO members (name) VALUES ('Harsh');
SELECT LAST_INSERT_ID() AS harsh_id;
INSERT INTO members (name) VALUES ('Ira'), ('Jatin'), ('Kavya');
SELECT LAST_INSERT_ID() AS first_id_of_batch;

Result: 1 row

harsh_id
102

Result: 1 row

first_id_of_batch
103
  • It is per connection: other users' inserts never change your value.
  • After a multi-row insert it returns the first generated ID.
  • In Java use Statement.RETURN_GENERATED_KEYS and getGeneratedKeys(); JPA does this for @GeneratedValue(strategy = GenerationType.IDENTITY).

4. Why Gaps Appear

AUTO_INCREMENT is a counter that never goes backThe table's counter hands out 1, 2, 3. Row 2 is deleted and a failed insert uses 4. The next successful insert gets 5. Numbers are unique and increasing, but gaps are normal.1inserted2deleted3inserted4insert failed5insertedcounter: next value = 6IDs are unique and always increase, but gaps (2, 4) are normalnever reuse or 'fill' them
Figure 1: deleted rows and failed or rolled-back inserts leave gaps. That's by design.
gaps.sql 
DELETE FROM members WHERE name = 'Bhavesh';
START TRANSACTION;
INSERT INTO members (name) VALUES ('Rolled Back');
ROLLBACK;
INSERT INTO members (name) VALUES ('Lalit');
SELECT member_id, name FROM members WHERE member_id > 100;

Result: 6 rows

member_idname
101Gauri
102Harsh
103Ira
104Jatin
105Kavya
107Lalit

The rolled-back insert consumed an ID that was never used. MySQL never reuses numbers, which keeps inserts fast and safe under concurrency. Never rely on IDs being consecutive.

5. Setting the Start Value

start.sql 
CREATE TABLE invoices (
  invoice_no INT AUTO_INCREMENT PRIMARY KEY,
  amount     DECIMAL(9,2)
) AUTO_INCREMENT = 5001;
INSERT INTO invoices (amount) VALUES (1200), (450);
SELECT * FROM invoices;

Result: 2 rows

invoice_noamount
50011200.00
5002450.00

Change the next value later with ALTER TABLE. You can't set it below the current maximum (MySQL quietly uses max + 1 instead):

alter-start.sql 
ALTER TABLE invoices AUTO_INCREMENT = 9000;
INSERT INTO invoices (amount) VALUES (780);
SELECT invoice_no FROM invoices ORDER BY invoice_no DESC LIMIT 1;

Result: 1 row

invoice_no
9000

6. Checking the Next Value

query.sql 
SELECT table_name, auto_increment
FROM information_schema.tables
WHERE table_schema = 'library_app';

Result: 2 rows

TABLE_NAMEAUTO_INCREMENT
invoices9001
members108
information_schema values can be cached; run ANALYZE TABLE or set information_schema_stats_expiry = 0 for an up-to-date number.

7. Resetting the Counter

TRUNCATE TABLE empties the table and resets the counter to 1; DELETE does not:

reset.sql 
CREATE TABLE temp_ids (id INT AUTO_INCREMENT PRIMARY KEY, v CHAR(1));
INSERT INTO temp_ids (v) VALUES ('a'), ('b'), ('c');
DELETE FROM temp_ids;
INSERT INTO temp_ids (v) VALUES ('d');
SELECT id FROM temp_ids;
TRUNCATE TABLE temp_ids;
INSERT INTO temp_ids (v) VALUES ('e');
SELECT id FROM temp_ids;

After DELETE: continues

id
4

After TRUNCATE: starts again

id
1
Since MySQL 8.0 the counter is persisted, so it survives a server restart (in 5.7 it could drop back to max + 1).

8. Step and Offset

The system variables auto_increment_increment (step) and auto_increment_offset (start) are mostly used in multi-source replication, so two servers never generate the same ID (one makes odd numbers, the other even):

step.sql 
SET SESSION auto_increment_increment = 2, auto_increment_offset = 1;
CREATE TABLE odd_ids (id INT AUTO_INCREMENT PRIMARY KEY, v CHAR(1));
INSERT INTO odd_ids (v) VALUES ('a'), ('b'), ('c');
SELECT id FROM odd_ids;
SET SESSION auto_increment_increment = 1, auto_increment_offset = 1;

Result: 3 rows

id
1
3
5

9. Running Out of Numbers

When the column's maximum is reached, inserts fail. With TINYINT that's quick to show:

overflow.sql 
CREATE TABLE tiny_ids (id TINYINT AUTO_INCREMENT PRIMARY KEY, v CHAR(1)) AUTO_INCREMENT = 127;
INSERT INTO tiny_ids (v) VALUES ('x');
INSERT INTO tiny_ids (v) VALUES ('y');
Output 
Query OK, 0 rows affected
Query OK, 1 row affected
ERROR 1062 (23000): Duplicate entry '127' for key 'tiny_ids.PRIMARY'

Once the counter is at the type's maximum, MySQL keeps generating that same maximum, so the next insert fails with a duplicate-key error.

TypeMax (signed)Max (UNSIGNED)
INT2,147,483,6474,294,967,295
BIGINT9.2 × 10181.8 × 1019
Use BIGINT UNSIGNED for tables that may receive billions of rows, such as events or logs.

10. Summary

  • AUTO_INCREMENT generates increasing integer IDs; one per table, indexed.
  • Omit the column (or pass NULL) to get the next value; LAST_INSERT_ID() returns it.
  • Gaps from deletes and rollbacks are normal; never reuse IDs.
  • Set the start with AUTO_INCREMENT = n; TRUNCATE resets it.
  • Choose INT or BIGINT UNSIGNED with room to grow.