MySQL 自增主键用完了怎么办？

Question

MySQL 自增主键用完了怎么办？

仓乃文 · Accepted Answer

MySQL 自增主键（AUTO_INCREMENT）达到字段类型的上限后，再插入会报 Duplicate entry 错误。INT 类型的上限约 21 亿，看似很大，但在高并发写入、分库分表、或频繁删除+插入的场景下，确实可能用完。

各类型的上限：
```
| 类型       | 有符号上限          | 无符号上限          |
|-----------|--------------------|--------------------|
| TINYINT   | 127                | 255                |
| SMALLINT  | 32,767             | 65,535             |
| MEDIUMINT | 8,388,607          | 16,777,215         |
| INT       | 2,147,483,647      | 4,294,967,295      |
| BIGINT    | 9.2 * 10^18        | 1.8 * 10^19        |
```

用完时的报错：
```sql
INSERT INTO orders (user_id) VALUES (1);
-- ERROR 1062 (23000): Duplicate entry "2147483647" for key "PRIMARY"
```

修复方案：
```sql
-- 方案 1：修改主键类型为 BIGINT（推荐，最彻底）
ALTER TABLE orders MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
-- BIGINT UNSIGNED 上限 1.8*10^19，几乎不可能用完
-- 注意：修改主键类型会锁表重建，大表需用 pt-online-schema-change

-- 方案 2：重置自增起始值（仅适用于有大量删除的场景）
-- 先查看当前最大 id
SELECT MAX(id) FROM orders;
-- 重置自增值
ALTER TABLE orders AUTO_INCREMENT = 1;
-- MySQL 会自动设为 MAX(id)+1

-- 方案 3：预分配自增范围（分库分表场景）
-- 每个分片分配不同的起始值和步长
SET @@auto_increment_offset = 1; -- 分片 1
SET @@auto_increment_increment = 4; -- 4 个分片
```

预防措施：
```sql
-- 1. 建表时直接用 BIGINT UNSIGNED
CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

-- 2. 监控自增值使用率
SELECT AUTO_INCREMENT, TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "your_db"
ORDER BY AUTO_INCREMENT DESC;

-- 3. 不要用自增 ID 做业务含义（如订单号），业务 ID 另外生成
```

补充说明：
- 自增值只增不减，DELETE 不会回收已用的自增值
- ROLLBACK 也不会回收自增值，事务回滚后 ID 会出现空洞
- 规则：新表一律用 BIGINT UNSIGNED 做主键，提前预防
- 分库分表场景用雪花算法生成 ID，不依赖数据库自增