MySQL AUTO_INCREMENT

AUTO_INCREMENT is a special column attribute in MySQL that automatically generates a unique, incrementing integer each time a new row is inserted. You never have to manually track or assign ID numbers — MySQL handles it for you.

It is almost always used on the primary key column of a table, making it the standard way to create row identifiers in MySQL.

---

Syntax

column_name INT NOT NULL AUTO_INCREMENT

---

Basic Example

A typical table with an auto-incrementing primary key:

CREATE TABLE categories (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

Now insert a few rows without specifying the id:

INSERT INTO categories (name) VALUES ('Electronics');
INSERT INTO categories (name) VALUES ('Clothing');
INSERT INTO categories (name) VALUES ('Books');

MySQL assigns the IDs automatically:

id	name
1	Electronics
2	Clothing
3	Books

---

Setting a Custom Starting Value

By default, AUTO_INCREMENT starts at 1 and increases by 1. You can change the starting value when creating the table:

CREATE TABLE invoices (
    id INT NOT NULL AUTO_INCREMENT,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id)
) AUTO_INCREMENT = 1000;

The first row inserted will get id = 1000, the next 1001, and so on. This is useful for making IDs look more professional or for starting a sequence from a specific point.

---

Changing the AUTO_INCREMENT Value

You can also change the next auto-increment value on an existing table using ALTER TABLE:

ALTER TABLE invoices AUTO_INCREMENT = 5000;

---

Getting the Last Inserted ID

After an insert, you can retrieve the AUTO_INCREMENT value that was just assigned using the LAST_INSERT_ID() function:

INSERT INTO categories (name) VALUES ('Sports');

SELECT LAST_INSERT_ID();
-- Returns: 4

This is especially useful in application code — for example, after inserting a new order, you can immediately use the returned ID to insert the order items that belong to it.

---

Gaps in the Sequence

When a row is deleted, its ID is not reused. The sequence continues from where it left off, leaving a gap.

-- Rows: 1, 2, 3
DELETE FROM categories WHERE id = 2;

-- Next insert gets id = 4, not 2
INSERT INTO categories (name) VALUES ('Furniture');
-- Rows: 1, 3, 4

---

Resetting AUTO_INCREMENT

To reset the counter back to 1 (for example, after clearing a table), you can use:

TRUNCATE TABLE categories;

TRUNCATE removes all rows and resets the AUTO_INCREMENT counter to its starting value. Note that DELETE FROM categories removes the rows but does not reset the counter.

---

Key Points to Remember

• AUTO_INCREMENT automatically generates a unique, increasing integer for each new row.
• It must be applied to a column that is part of an index — most commonly the PRIMARY KEY.
• A table can only have one AUTO_INCREMENT column.
• The default starting value is 1, but you can change it with AUTO_INCREMENT = n.
• Deleted row IDs are never reused — gaps in the sequence are normal.
• Use LAST_INSERT_ID() to get the ID assigned to the most recently inserted row.
• TRUNCATE resets the counter; DELETE does not.