MySQL AUTO_INCREMENT: Generate Unique Numeric IDs

MySQL AUTO_INCREMENT automatically generates a numeric value when a new row is inserted. It is commonly used for surrogate primary keys such as user IDs, order IDs, ticket IDs and student IDs.

CREATE TABLE student (
    student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (student_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

Insert a row without supplying the auto-increment column:

INSERT INTO student
    (name, email)
VALUES
    ('John', 'john@example.com');

MySQL generates the student_id value automatically.

AUTO_INCREMENT Syntax Top ↑

A typical modern definition uses an integer column as the primary key:

CREATE TABLE student (
    student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (student_id)
);

For normal application tables, making the AUTO_INCREMENT column the PRIMARY KEY is the clearest design.

Insert a Row without Supplying the ID Top ↑

Omit the auto-increment column from the INSERT column list:

INSERT INTO student
    (name, email)
VALUES
    ('John', 'john@example.com');

If the table is empty and the counter starts at its normal default, the first generated ID is usually 1, followed by later generated values.

You can inspect the rows with:

SELECT student_id, name, email
FROM student
ORDER BY student_id;

See SQL INSERT for more insert patterns.

Get the Generated AUTO_INCREMENT ID Top ↑

At SQL level, MySQL provides LAST_INSERT_ID() for the most recent automatically generated value in the current session:

INSERT INTO student
    (name, email)
VALUES
    ('Ravi', 'ravi@example.com');

SELECT LAST_INSERT_ID() AS new_student_id;

Application APIs also expose the generated value. With PHP PDO, use lastInsertId() after a successful INSERT.

The related MySQL insert ID tutorial covers retrieving the generated identifier from application code.

AUTO_INCREMENT and PRIMARY KEY Top ↑

An AUTO_INCREMENT column must be indexed appropriately by MySQL. For the common one-ID-per-row design, use it as the primary key:

CREATE TABLE tickets (
    ticket_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    subject VARCHAR(200) NOT NULL,
    PRIMARY KEY (ticket_id)
);

A primary key also guarantees uniqueness and prevents NULL values in the identifier.

Start AUTO_INCREMENT from a Higher Number Top ↑

You can set an initial counter when creating a table:

CREATE TABLE student (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    class VARCHAR(10) NOT NULL,
    mark INT NOT NULL DEFAULT 0,
    gender VARCHAR(10) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB
  AUTO_INCREMENT=100000
  DEFAULT CHARSET=utf8mb4;

The first automatically generated ID will start from the configured counter, assuming no existing row requires a higher next value.

Change the Next AUTO_INCREMENT Value Top ↑

To request a higher next value for an existing table:

ALTER TABLE student
AUTO_INCREMENT = 50000;

If existing rows already contain IDs at or above the requested value, MySQL will not reuse an already occupied key. Do not use AUTO_INCREMENT settings as a mechanism for filling historical gaps.

Why AUTO_INCREMENT Values Can Have Gaps Top ↑

An AUTO_INCREMENT column is designed to generate unique identifiers efficiently. It is not a gapless sequence generator.

For example, a table can contain:

1
2
4
7

without anything being wrong.

Gaps can appear because of:

• deleted rows,
• failed or ignored inserts,
• rolled-back transactions,
• concurrent insert activity,
• explicitly inserted higher values, or
• changes to the AUTO_INCREMENT counter.

AUTO_INCREMENT after DELETE and TRUNCATE Top ↑

A normal DELETE removes rows but should not be expected to restart the AUTO_INCREMENT sequence.

DELETE FROM student
WHERE id = 7;

MySQL does not normally reuse that deleted ID automatically.

TRUNCATE TABLE removes all rows and, for normal InnoDB tables, resets the AUTO_INCREMENT counter:

TRUNCATE TABLE student;

Maximum AUTO_INCREMENT Value Top ↑

The maximum generated value depends on the integer datatype and whether it is signed or unsigned.

Choose the datatype according to realistic lifetime growth, not only the number of rows expected today.

Signed vs Unsigned AUTO_INCREMENT Columns Top ↑

If an identifier never needs negative numbers, an unsigned integer can provide a larger positive range for the same storage size:

id INT UNSIGNED NOT NULL AUTO_INCREMENT

For extremely high-volume tables, consider BIGINT UNSIGNED rather than waiting until an INT counter approaches its limit.

ZEROFILL and Integer Display Width Top ↑

Older MySQL examples often used definitions such as:

-- Legacy style: do not use for new schemas
id INT(5) UNSIGNED ZEROFILL AUTO_INCREMENT

Numeric display width and ZEROFILL are deprecated legacy features in modern MySQL. They should not be used as the default way to format identifiers.

Keep the ID as a numeric value:

id INT UNSIGNED NOT NULL AUTO_INCREMENT

If the user interface must display 00025, format the number for presentation instead of changing the database identifier.

For example, MySQL can format output with LPAD():

SELECT LPAD(
    id,
    5,
    '0'
) AS display_id
FROM student;

The underlying ID remains numeric and can continue to be indexed and compared naturally.

Common Uses of AUTO_INCREMENT Top ↑

• Assigning an internal user ID after signup.
• Generating an employee record key.
• Creating a help-desk ticket key.
• Assigning an internal order or shipment record ID.
• Creating a primary key for child-table relationships.

The generated database ID is excellent as a technical key. If the business needs a public reference with prefixes, check digits, secrecy, or a gapless legal sequence, generate that as a separate business identifier.

Common AUTO_INCREMENT Mistakes Top ↑

Using INT(3) to limit an ID to three digits Top ↑

INT(3) does not mean the integer can store only three digits. The old number in parentheses was a display-width concept, not a storage-range limit.

Using UNIQUE instead of a clear PRIMARY KEY by default Top ↑

For the usual surrogate-ID design, define the AUTO_INCREMENT identifier as the primary key.

Assuming IDs will be gapless Top ↑

Gaps are normal and can occur even without manual deletion.

Assuming DELETE resets the counter Top ↑

Deleting rows does not normally make MySQL restart the sequence or reuse those IDs.

Using ZEROFILL for modern ID formatting Top ↑

Keep the stored key numeric and format leading zeros in SQL output or the application presentation layer.

Choosing a datatype that is too small Top ↑

Once the AUTO_INCREMENT value reaches the datatype's maximum, further automatic values cannot be generated normally. Plan for lifetime growth.

Guessing the generated ID with MAX(id) Top ↑

Do not use MAX(id) to determine which row your session inserted. Use LAST_INSERT_ID() or the database API's insert-ID function.

Related Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: