How Long Can MySQL AUTO_INCREMENT IDs Last? INT vs BIGINT Explained

MySQL auto‑increment primary keys are typically defined as INT or BIGINT. Both types usually satisfy most applications, but when the maximum value is reached new inserts fail with an error such as

ERROR 167 (22003): Out of range value for column 'id' at row 1

.

INT

INT occupies 4 bytes. Signed range: –2,147,483,648 to 2,147,483,647. Unsigned range: 0 to 4,294,967,295 (about 4.3 billion). Assuming a workload of 100 rows per second, the lifespan before overflow can be calculated:

# INT max value
max_int_value = 4294967295
records_per_second = 100
records_per_year = 365 * 24 * 60 * 60
result = max_int_value/(records_per_second * records_per_year)
print(result)  # 1.3619251950152207

The result shows roughly 1.36 years of capacity, which is insufficient for long‑term growth.

BIGINT

BIGINT occupies 8 bytes. Signed range: –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Unsigned range: 0 to 18,446,744,073,709,551,615 (about 1.84 × 10¹⁹). To see how many rows per second can be stored over a 100‑year period:

# BIGINT max value
max_bigint = 18446744073709551615
seconds_in_100_years = 365*24*60*60*100
result = max_bigint / seconds_in_100_years
print(result)  # 5849424173.55072

The calculation yields about 5.85 billion rows per second, far exceeding even peak traffic such as Taobao’s Double‑11 concurrency (illustrated below).

Thus, using BIGINT removes any realistic concern about running out of auto‑increment IDs.

Changing INT to BIGINT

If an existing table uses INT for its primary key, you can convert it to BIGINT with the following SQL commands:

-- Create table
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_data VARCHAR(255)
);
-- View structure
DESCRIBE orders;
-- Modify column type
ALTER TABLE orders MODIFY id BIGINT UNSIGNED AUTO_INCREMENT;
-- Verify change
DESCRIBE orders;

Precautions

Backup data before altering the table to prevent data loss.

The table may be locked during the alteration; perform the change during low‑traffic periods.

If foreign keys reference the column, drop those constraints first, modify the column, then recreate the constraints.

Summary: INT is sufficient for most scenarios, but when massive growth is expected, switching to BIGINT ensures the auto‑increment key will not be exhausted within any realistic system lifespan.