Solutions for MySQL Auto Increment ID Exhaustion

MySQL's auto‑increment ID is a common primary key type, but in large tables or special scenarios it can reach its maximum value. The default BIGINT limit is 2^63‑1 (9223372036854775807), yet using INT or mismanagement can cause exhaustion.

Solutions

1. Change ID column type

Upgrade the column from INT to BIGINT to expand the range.

ALTER TABLE table_name MODIFY id BIGINT UNSIGNED AUTO_INCREMENT;

2. Use UUID instead of auto‑increment

UUID provides a 128‑bit globally unique identifier, avoiding exhaustion but incurs storage and performance overhead.

CREATE TABLE table_name (
    id CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(255)
);
INSERT INTO table_name (name) VALUES ('example_name');

3. Segment ID generation

Maintain multiple ID‑generator tables, each handling a separate segment.

CREATE TABLE id_generator_1 (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    INDEX (id)
);
CREATE TABLE id_generator_2 (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    INDEX (id)
);

4. Use composite primary key

Combine several columns to form a primary key, bypassing single‑column limits.

CREATE TABLE table_name (
    id INT UNSIGNED AUTO_INCREMENT,
    other_column VARCHAR(255),
    PRIMARY KEY (id, other_column)
);

5. Adjust auto‑increment step and offset

Change the auto‑increment start value or step to improve allocation.

ALTER TABLE table_name AUTO_INCREMENT = 1000000;

6. Database sharding

Distribute data across multiple database instances, each with its own ID space.

CREATE TABLE db1.table_name (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);
CREATE TABLE db2.table_name (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

Summary

Change column type to BIGINT for a larger range.

Use UUIDs to avoid exhaustion, considering storage/performance impact.

Segment ID generation with multiple tables.

Employ composite keys to combine columns.

Adjust auto‑increment step and offset.

Apply sharding to spread IDs across instances.