Introduction

MySQL's auto-increment feature generates a unique sequential integer for each new row. Sometimes you need to know the next auto-increment value before inserting — for logging, pre-generating file paths, or coordinating with external systems. The main approaches are querying INFORMATION_SCHEMA.TABLES, using SHOW TABLE STATUS, and checking LAST_INSERT_ID() after an insert. Each method has limitations, especially in concurrent environments where the "next" ID can change between the time you read it and the time you insert.

Method 1: INFORMATION_SCHEMA.TABLES

1SELECT AUTO_INCREMENT
2FROM INFORMATION_SCHEMA.TABLES
3WHERE TABLE_SCHEMA = 'my_database'
4  AND TABLE_NAME = 'orders';

This returns the next auto-increment value that will be assigned to a new row. The value comes from the table's metadata, not a lock, so it may be stale in concurrent environments.

Method 2: SHOW TABLE STATUS

SHOW TABLE STATUS FROM my_database LIKE 'orders';

The Auto_increment column in the output shows the next value. This is equivalent to the INFORMATION_SCHEMA query but returns additional table metadata.

-- Extract just the auto-increment value
SHOW TABLE STATUS FROM my_database WHERE Name = 'orders'\G
-- Look for: Auto_increment: 1042

Method 3: LAST_INSERT_ID() After Insert

INSERT INTO orders (customer_id, total) VALUES (1, 99.99);
SELECT LAST_INSERT_ID();
-- Returns the ID that was just assigned

LAST_INSERT_ID() is connection-specific and thread-safe. It returns the auto-increment value generated by the most recent INSERT on the current connection. This is the most reliable way to get the ID that was actually assigned.

Method 4: INSERT ... RETURNING (MySQL 8.0.21+)

1-- Not available in MySQL — use LAST_INSERT_ID() instead
2-- PostgreSQL and MariaDB support INSERT ... RETURNING
3
4-- In MySQL, the pattern is:
5INSERT INTO orders (customer_id, total) VALUES (1, 99.99);
6SELECT LAST_INSERT_ID() AS new_order_id;

MySQL does not support INSERT ... RETURNING. The standard pattern is to insert first, then call LAST_INSERT_ID().

Using LAST_INSERT_ID() in Application Code

1import mysql.connector
2
3conn = mysql.connector.connect(
4    host='localhost', database='my_database',
5    user='root', password='password'
6)
7cursor = conn.cursor()
8
9cursor.execute(
10    "INSERT INTO orders (customer_id, total) VALUES (%s, %s)",
11    (1, 99.99)
12)
13conn.commit()
14
15# Get the auto-generated ID
16new_id = cursor.lastrowid
17print(f"New order ID: {new_id}")
18
19cursor.close()
20conn.close()

1// Java with JDBC
2PreparedStatement stmt = conn.prepareStatement(
3    "INSERT INTO orders (customer_id, total) VALUES (?, ?)",
4    Statement.RETURN_GENERATED_KEYS
5);
6stmt.setInt(1, 1);
7stmt.setDouble(2, 99.99);
8stmt.executeUpdate();
9
10ResultSet rs = stmt.getGeneratedKeys();
11if (rs.next()) {
12    long newId = rs.getLong(1);
13    System.out.println("New order ID: " + newId);
14}

Most database drivers provide access to the generated ID through lastrowid (Python), getGeneratedKeys() (Java), or equivalent methods. This is always preferred over querying INFORMATION_SCHEMA.

Setting and Resetting Auto-Increment

1-- Set the next auto-increment value
2ALTER TABLE orders AUTO_INCREMENT = 1000;
3
4-- Check current value
5SELECT AUTO_INCREMENT
6FROM INFORMATION_SCHEMA.TABLES
7WHERE TABLE_SCHEMA = 'my_database'
8  AND TABLE_NAME = 'orders';
9
10-- Reset to max + 1 (useful after deleting rows)
11ALTER TABLE orders AUTO_INCREMENT = 1;
12-- MySQL automatically adjusts to MAX(id) + 1
13
14-- Create table with custom start value
15CREATE TABLE tickets (
16    id INT AUTO_INCREMENT PRIMARY KEY,
17    title VARCHAR(255)
18) AUTO_INCREMENT = 10000;

ALTER TABLE ... AUTO_INCREMENT cannot set the value lower than the current maximum ID. MySQL silently adjusts it to MAX(id) + 1.

Auto-Increment with Bulk Inserts

1-- Inserting multiple rows
2INSERT INTO orders (customer_id, total) VALUES
3    (1, 10.00),
4    (2, 20.00),
5    (3, 30.00);
6
7-- LAST_INSERT_ID() returns the FIRST auto-generated ID
8SELECT LAST_INSERT_ID();  -- e.g., 100
9
10-- The three rows got IDs 100, 101, 102
11-- Calculate: LAST_INSERT_ID() through LAST_INSERT_ID() + ROW_COUNT() - 1
12SELECT LAST_INSERT_ID() AS first_id, ROW_COUNT() AS num_rows;

For multi-row inserts, LAST_INSERT_ID() returns the ID of the first row, not the last. The IDs are guaranteed to be consecutive, so you can calculate the range.

InnoDB vs MyISAM Auto-Increment Behavior

1-- InnoDB (default): auto-increment counter is stored in memory
2-- After restart, InnoDB recalculates: SELECT MAX(id) FROM table
3-- This means deleted IDs at the end may be reused after restart
4
5-- MyISAM: counter stored in table metadata file
6-- Never reuses IDs, even after restart
7
8-- MySQL 8.0+: InnoDB persists the counter in the redo log
9-- Restart no longer reuses deleted trailing IDs

In MySQL 5.7 and earlier with InnoDB, if you insert row ID=10, delete it, and restart MySQL, the next insert gets ID=10 again. MySQL 8.0 fixed this by persisting the counter.

Common Pitfalls

• Reading the next ID before inserting is unreliable: In concurrent environments, another connection can insert between your read and your insert, giving you a different ID. Always use LAST_INSERT_ID() after inserting instead of pre-reading the next value.
• LAST_INSERT_ID() returns 0 when no auto-increment insert occurred: If you call LAST_INSERT_ID() without a prior auto-increment insert on the current connection, it returns 0. It does not reflect other connections' inserts.
• Gaps in auto-increment sequences: Rolled-back transactions, failed inserts, and InnoDB's pre-allocation all create gaps. Auto-increment IDs are not guaranteed to be sequential. Do not use them for row counting or business logic that assumes no gaps.
• ALTER TABLE AUTO_INCREMENT on large tables: This operation may lock the table briefly. On production databases with millions of rows, run it during maintenance windows.
• Mixing REPLACE INTO with auto-increment: REPLACE INTO deletes the existing row and inserts a new one, consuming a new auto-increment ID each time. Use INSERT ... ON DUPLICATE KEY UPDATE to preserve the original ID.

Summary

• Use LAST_INSERT_ID() after inserting to get the assigned auto-increment value reliably
• Query INFORMATION_SCHEMA.TABLES or SHOW TABLE STATUS for the next value (unreliable under concurrency)
• Application code should use driver-specific methods (lastrowid, getGeneratedKeys())
• Multi-row inserts return the first generated ID via LAST_INSERT_ID()
• MySQL 8.0+ persists the InnoDB auto-increment counter across restarts
• Never rely on pre-reading the next auto-increment value in concurrent applications