Introduction

MySQL's AUTO_INCREMENT attribute automatically generates a unique integer for new rows in a table. Resetting it is commonly needed after deleting test data, truncating a table, or consolidating records after a migration. The primary method is ALTER TABLE ... AUTO_INCREMENT = value, but there are important constraints: you cannot set it below the current maximum value in the column, and the behavior differs between storage engines (InnoDB vs MyISAM).

Basic Reset with ALTER TABLE

1-- Reset AUTO_INCREMENT to a specific value
2ALTER TABLE users AUTO_INCREMENT = 1;
3
4-- Reset to start after the current max ID
5ALTER TABLE users AUTO_INCREMENT = 1000;
6
7-- Check the current AUTO_INCREMENT value
8SELECT AUTO_INCREMENT
9FROM information_schema.TABLES
10WHERE TABLE_SCHEMA = 'my_database'
11  AND TABLE_NAME = 'users';

ALTER TABLE ... AUTO_INCREMENT = value sets the next auto-increment value. If value is less than or equal to the current maximum in the column, MySQL silently adjusts it to max + 1. The table is not physically restructured — only the counter metadata is updated.

Reset by Truncating the Table

1-- TRUNCATE resets AUTO_INCREMENT to 1 and deletes all rows
2TRUNCATE TABLE users;
3
4-- This is equivalent to:
5-- DELETE FROM users;
6-- ALTER TABLE users AUTO_INCREMENT = 1;
7-- But TRUNCATE is faster because it drops and recreates the table

TRUNCATE TABLE removes all rows and resets the auto-increment counter in a single operation. It is much faster than DELETE for large tables because it does not generate individual row delete log entries. However, TRUNCATE cannot be used if the table is referenced by foreign keys.

DELETE vs TRUNCATE Behavior

1-- Create a test table
2CREATE TABLE orders (
3    id INT AUTO_INCREMENT PRIMARY KEY,
4    product VARCHAR(100)
5);
6
7INSERT INTO orders (product) VALUES ('Widget'), ('Gadget'), ('Gizmo');
8-- IDs: 1, 2, 3. AUTO_INCREMENT is now 4.
9
10-- DELETE does NOT reset AUTO_INCREMENT
11DELETE FROM orders;
12INSERT INTO orders (product) VALUES ('NewItem');
13-- ID will be 4, not 1
14
15-- TRUNCATE DOES reset AUTO_INCREMENT
16TRUNCATE TABLE orders;
17INSERT INTO orders (product) VALUES ('NewItem');
18-- ID will be 1

InnoDB vs MyISAM Behavior

1-- InnoDB: AUTO_INCREMENT is stored in memory, not on disk (MySQL < 8.0)
2-- After a server restart, InnoDB recalculates it as MAX(id) + 1
3-- This means gaps from deleted rows at the end are "reclaimed"
4
5-- Example with InnoDB (MySQL < 8.0):
6INSERT INTO users (name) VALUES ('Alice');  -- id = 1
7INSERT INTO users (name) VALUES ('Bob');    -- id = 2
8DELETE FROM users WHERE id = 2;
9-- AUTO_INCREMENT is 3 in memory
10-- After MySQL restart: AUTO_INCREMENT becomes MAX(1) + 1 = 2
11-- Next insert gets id = 2 (the gap is reused!)
12
13-- MySQL 8.0+ (InnoDB): AUTO_INCREMENT is persisted in the redo log
14-- Gaps are NOT reclaimed after restart — behavior matches MyISAM

Before MySQL 8.0, InnoDB recalculated the auto-increment counter on restart, which could reuse IDs from deleted rows. MySQL 8.0 fixed this by persisting the counter in the redo log.

Resetting AUTO_INCREMENT After Deleting Rows

1-- Scenario: table has IDs 1-1000, you deleted rows 500-1000
2-- Current AUTO_INCREMENT is 1001
3
4-- Option 1: Reset to the next available value
5ALTER TABLE users AUTO_INCREMENT = 501;
6
7-- Option 2: Find the max and reset
8SET @max_id = (SELECT MAX(id) FROM users);
9-- Cannot use variable directly in ALTER TABLE, use prepared statement:
10SET @sql = CONCAT('ALTER TABLE users AUTO_INCREMENT = ', @max_id + 1);
11PREPARE stmt FROM @sql;
12EXECUTE stmt;
13DEALLOCATE PREPARE stmt;
14
15-- Option 3: Reset to 1 (MySQL adjusts to max + 1 automatically)
16ALTER TABLE users AUTO_INCREMENT = 1;
17-- Actually sets it to MAX(id) + 1, not 1

Checking Current AUTO_INCREMENT

1-- Method 1: information_schema
2SELECT TABLE_NAME, AUTO_INCREMENT
3FROM information_schema.TABLES
4WHERE TABLE_SCHEMA = DATABASE()
5ORDER BY TABLE_NAME;
6
7-- Method 2: SHOW CREATE TABLE
8SHOW CREATE TABLE users;
9-- Output includes: AUTO_INCREMENT=42
10
11-- Method 3: SHOW TABLE STATUS
12SHOW TABLE STATUS WHERE Name = 'users';
13-- Look at the Auto_increment column

Common Pitfalls

• Setting AUTO_INCREMENT below the current max: ALTER TABLE users AUTO_INCREMENT = 1 does not actually set it to 1 if rows exist. MySQL silently adjusts to MAX(id) + 1. This is not an error — it is a safety mechanism to prevent duplicate key violations.
• Expecting DELETE to reset the counter: DELETE FROM table removes rows but leaves the auto-increment counter unchanged. The next insert continues from where it left off. Use TRUNCATE TABLE to reset both rows and the counter, or follow DELETE with an explicit ALTER TABLE.
• TRUNCATE on tables with foreign key references: TRUNCATE TABLE fails with an error if other tables have foreign key constraints referencing the table, even if those tables are empty. Either drop the foreign keys first, use SET FOREIGN_KEY_CHECKS = 0 (risky), or use DELETE + ALTER TABLE instead.
• Relying on gapless ID sequences: AUTO_INCREMENT values can have gaps from rolled-back transactions, deleted rows, and multi-master replication. Never depend on IDs being consecutive — use a separate sequence column if gapless numbering is required.
• InnoDB counter reset on restart (MySQL < 8.0): Before MySQL 8.0, InnoDB recalculated the auto-increment counter as MAX(id) + 1 on server restart. This silently reuses IDs from deleted rows at the end of the table, potentially causing issues with external systems that cached the old IDs. Upgrade to MySQL 8.0+ to avoid this.

Summary

• Use ALTER TABLE table AUTO_INCREMENT = value to reset the counter
• TRUNCATE TABLE deletes all rows and resets AUTO_INCREMENT in one operation
• DELETE does not reset AUTO_INCREMENT — follow it with ALTER TABLE if needed
• MySQL prevents setting AUTO_INCREMENT below MAX(id) + 1 to avoid duplicates
• Check current value via information_schema.TABLES or SHOW TABLE STATUS
• MySQL 8.0+ persists InnoDB auto-increment counters across restarts