AUTO_INCREMENT behavior in MySQL - Step-by-Step Execution

Concept Flow - AUTO_INCREMENT behavior

Create Table with AUTO_INCREMENT

↓

Insert Row without ID

↓

MySQL assigns next AUTO_INCREMENT value

↓

Row inserted with assigned ID

↓

AUTO_INCREMENT counter increments

↓

Next insert uses updated AUTO_INCREMENT value

When you insert a row without specifying the AUTO_INCREMENT column, MySQL automatically assigns the next number, then increases the counter for the next insert.

Execution Sample

MySQL

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

Creates a table with an AUTO_INCREMENT id, then inserts two rows without specifying id, so MySQL assigns ids automatically.

Execution Table

Step	Action	AUTO_INCREMENT value before	Inserted id	AUTO_INCREMENT value after	Notes
1	Create table with AUTO_INCREMENT	1 (default start)	N/A	1	Table created, counter starts at 1
2	Insert 'Alice' without id	1	1	2	MySQL assigns id=1, counter increments to 2
3	Insert 'Bob' without id	2	2	3	MySQL assigns id=2, counter increments to 3
4	Insert with explicit id=10	3	10	11	Explicit id=10 inserted, counter jumps to 11
5	Insert without id again	11	11	12	MySQL assigns id=11, counter increments to 12
6	Insert with explicit id=5 (lower than counter)	12	5	12	Explicit id=5 inserted, counter stays at 12
7	Insert without id again	12	12	13	MySQL assigns id=12, counter increments to 13
8	Stop				No more inserts

💡 Execution stops after last insert; AUTO_INCREMENT counter updates after each insert.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7	Final
AUTO_INCREMENT counter	1	2	3	11	12	12	13	13

Key Moments - 3 Insights

Why does the AUTO_INCREMENT counter jump to 11 after inserting a row with explicit id=10?

Because MySQL sets the AUTO_INCREMENT counter to one more than the highest explicit id inserted, as shown in step 4 of the execution_table.

What happens if you insert a row with an explicit id lower than the current AUTO_INCREMENT counter?

The counter does not change and stays at the higher value, as shown in step 6 where id=5 is inserted but counter remains 12.

How does MySQL assign ids when you do not specify the id column?

It uses the current AUTO_INCREMENT counter value, inserts that as the id, then increments the counter by 1, as shown in steps 2, 3, 5, and 7.

Visual Quiz - 3 Questions

Look at the execution_table, what is the AUTO_INCREMENT counter value after inserting 'Bob'?

A 1

B 2

C 3

D 4

💡 Hint

Check the 'AUTO_INCREMENT value after' column in row 3 of the execution_table.

At which step does the AUTO_INCREMENT counter jump due to an explicit id insertion?

A Step 3

B Step 4

C Step 6

D Step 7

💡 Hint

Look for the step where the inserted id is 10 and the counter changes from 3 to 11.

If you insert a row without specifying id after step 7, what id will MySQL assign?

A 13

B 14

C 12

D 11

💡 Hint

Check the AUTO_INCREMENT counter after step 7 in variable_tracker.

Concept Snapshot

AUTO_INCREMENT behavior in MySQL:
- Define a column with AUTO_INCREMENT to auto-generate unique IDs.
- When inserting without specifying the ID, MySQL assigns the current counter value.
- The counter starts at 1 by default and increments after each insert.
- Inserting an explicit ID higher than the counter moves the counter up.
- Inserting an explicit ID lower than the counter does not change it.

Full Transcript

This visual execution trace shows how MySQL handles AUTO_INCREMENT columns. When a table is created with an AUTO_INCREMENT column, the counter starts at 1. Each insert without specifying the ID uses the current counter value and then increments it. If you insert a row with an explicit ID higher than the current counter, MySQL updates the counter to one more than that ID. If the explicit ID is lower, the counter remains unchanged. This ensures unique, increasing IDs for new rows.