Practical MySQL & MariaDB

What Is MySQL AUTO_INCREMENT? How to Check, Set, and Reset It (Complete Guide)

Table Management
目次

What Is MySQL AUTO_INCREMENT?

Basics of AUTO_INCREMENT

In MySQL, AUTO_INCREMENT is a feature that automatically increments numeric values in a database table. It is commonly used as a primary key (PRIMARY KEY), allowing sequential numbers to be assigned automatically each time a new row is inserted—without manually specifying the ID.

Main Uses of AUTO_INCREMENT

  • Automatic member ID generation: Assigns a unique ID when a user registers
  • Order number management: Automatically assigns a number for each order
  • Product code assignment: Used as a unique identifier for products

By properly utilizing AUTO_INCREMENT, you can maintain data uniqueness while reducing administrative overhead.

How to Check the AUTO_INCREMENT Value in MySQL [3 SQL Commands]

Method 1: Use SHOW TABLE STATUS

SHOW TABLE STATUS LIKE 'table_name';

When you execute this command, the current AUTO_INCREMENT value is displayed in the Auto_increment column.

Method 2: Retrieve from INFORMATION_SCHEMA.TABLES

SELECT AUTO_INCREMENT FROM information_schema.tables 
WHERE table_schema = 'database_name' AND table_name = 'table_name';

This method allows you to directly retrieve the AUTO_INCREMENT value set for the target table.

Method 3: Use SHOW CREATE TABLE

SHOW CREATE TABLE table_name;

When you execute this command, the table creation DDL (Data Definition Language) is displayed, which includes the AUTO_INCREMENT setting.

How to Set and Modify AUTO_INCREMENT

Setting AUTO_INCREMENT When Creating a New Table

To define a column with AUTO_INCREMENT when creating a new table, use the following SQL:

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

Changing the AUTO_INCREMENT Value of an Existing Table

If you want to change the AUTO_INCREMENT value of an existing table, use the following command:

ALTER TABLE users AUTO_INCREMENT = 1000;

After executing this command, the next inserted record will start with ID 1000.

Changing the AUTO_INCREMENT Increment Value (MySQL 8.0 or Later)

By default, the AUTO_INCREMENT value increases by 1. However, you can change the increment value if needed.

SET @@auto_increment_increment = 5;

With this setting, the AUTO_INCREMENT value increases by 5 each time (e.g., 1, 6, 11, …).

Important Notes When Using AUTO_INCREMENT [Prevent Data Issues]

Maximum Value Limit of AUTO_INCREMENT

The maximum value of AUTO_INCREMENT depends on the column’s data type. For example, when using the INT type, the maximum value is 2,147,483,647. If this limit is exceeded, new IDs can no longer be generated, resulting in an error.

Solutions

  • Use BIGINT to support much larger values
  • Consider data archiving or resetting when necessary

Behavior of AUTO_INCREMENT After Deleting Data

In MySQL, deleting records does not automatically reset the AUTO_INCREMENT value.

DELETE FROM users WHERE id = 100;

Even if a specific ID is deleted like this, the next inserted value continues from the current counter without reuse.

How to Reset
Using TRUNCATE TABLE deletes all data and resets the AUTO_INCREMENT value.

TRUNCATE TABLE users;

After performing this operation, the next inserted ID will restart from 1.

Transactions and AUTO_INCREMENT

In MySQL, the AUTO_INCREMENT value does not revert even if a transaction is rolled back (ROLLBACK).

START TRANSACTION;
INSERT INTO users (name) VALUES ('Alice');
ROLLBACK;

In this case, the data is not inserted, but the AUTO_INCREMENT value remains incremented. As a result, gaps in ID numbers may occur.

Frequently Asked Questions (FAQ)

Q1: Why do AUTO_INCREMENT values sometimes have gaps?

A: Because numbers are not reset after an INSERT followed by a ROLLBACK. Deletions and failed inserts can also cause gaps.

Q2: How can I reset the AUTO_INCREMENT value?

A: Execute TRUNCATE TABLE table_name; to reset it.

Q3: How can I check the current AUTO_INCREMENT value?

A: Use SHOW TABLE STATUS or query INFORMATION_SCHEMA.TABLES.

Q4: What happens if AUTO_INCREMENT exceeds its maximum value?

A: If it exceeds the maximum value of INT, an error will occur. You must change the column type to BIGINT.