The World of MySQL & MariaDB

Check, Set, and Reset MySQL AUTO_INCREMENT (SQL Commands)

Table Management
目次

What is MySQL AUTO_INCREMENT?

Basics of AUTO_INCREMENT

MySQL’s AUTO_INCREMENT is a feature that automatically increments numbers in database tables. It is commonly used as a primary key (PRIMARY KEY), and even without manually specifying an ID, a sequential number is assigned with each data insertion.

Common Uses of AUTO_INCREMENT

  • Automatic generation of member IDs: Assign a unique ID when a user registers
  • Order number management: Automatically assign numbers for each order
  • Assignment of product codes: Used as a unique identifier for products
By using AUTO_INCREMENT appropriately, you can maintain data uniqueness while reducing administrative effort.

How to Check AUTO_INCREMENT Values in MySQL – 3 SQL Commands

Method ① to Check AUTO_INCREMENT Using SHOW TABLE STATUS

SHOW TABLE STATUS LIKE 'table_name';
When you run this command, the current value of AUTO_INCREMENT appears in the Auto_increment column.

Method ② to Check AUTO_INCREMENT by Retrieving from INFORMATION_SCHEMA.TABLES

SELECT AUTO_INCREMENT FROM information_schema.tables 
WHERE table_schema = 'database_name' AND table_name = 'table_name';
With this method, you can directly retrieve the AUTO_INCREMENT value set for the target table.

Method ③ to Check AUTO_INCREMENT Using SHOW CREATE TABLE

SHOW CREATE TABLE table_name;
When you run this command, the DDL (Data Definition Language) used to create the table is displayed, and it includes the AUTO_INCREMENT setting.

How to Set and Change AUTO_INCREMENT

Setting AUTO_INCREMENT When Creating a New Table

To set AUTO_INCREMENT on a column 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;
Executing this command will cause the ID of the next inserted row to start from 1000.

Changing the AUTO_INCREMENT Increment (MySQL 8.0 and Later)

By default, the AUTO_INCREMENT value increments by 1, but you can change the increment amount.
SET @@auto_increment_increment = 5;
With this setting, the AUTO_INCREMENT value will increase by 5 each time (e.g., 1, 6, 11, …).

Precautions When Using AUTO_INCREMENT (Prevent Data Corruption)

Maximum Value Limits of AUTO_INCREMENT

AUTO_INCREMENT is determined by the column’s data type. For example, for the INT type, the maximum value is 2,147,483,647. Exceeding this prevents issuing new IDs and results in an error. Countermeasures
  • Using BIGINT allows handling larger values
  • Consider data archiving or resetting as needed

Behavior of AUTO_INCREMENT After Data Deletion

In MySQL, deleting records does not automatically reset the AUTO_INCREMENT value.
DELETE FROM users WHERE id = 100;
Thus, even if you delete a specific ID, the next inserted value continues from where it left off. How to Reset TRUNCATE TABLE can be used to delete all data and reset the AUTO_INCREMENT value.
TRUNCATE TABLE users;
After this operation, the ID restarts from 1 on the next insert.

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. This can cause gaps in IDs.

Frequently Asked Questions (FAQ)

Q1: Why does the AUTO_INCREMENT value skip?

A: Because after an INSERT, a ROLLBACK does not reset the number. Deletions or failed inserts can also cause it.

Q2: How to reset the AUTO_INCREMENT value?

A: Executing TRUNCATE TABLE table_name; resets it.

Q3: How to retrieve the current value of AUTO_INCREMENT?

A: Please use SHOW TABLE STATUS or INFORMATION_SCHEMA.TABLES.

Q4: What happens if the AUTO_INCREMENT value exceeds its maximum?

A: Exceeding the maximum value of INT causes an error, so you need to switch to BIGINT.