社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

MySQL 中 AUTO_INCREMENT 列达到最大值时会发生什么?

鸭哥聊Java • 5 月前 • 172 次点击  

订单创建接口突然开始 500,业务日志看着很干净,没空指针,没超时,最后一层异常在 MySQL。

### Error updating database
Cause: java.sql.SQLException:
Failed to read auto-increment value from storage engine

看到这个错误,我一般不会先翻业务代码。

先看表。

show create table t_order;
show table status like 't_order'\G

如果表是这种:

CREATE TABLE t_order (
  id INT NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(64) NOT NULL,
  user_id BIGINT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

那就要小心了。

INT 如果没有写 UNSIGNED,最大值是:

2147483647

也就是 21 亿多。

很多人第一反应是:21 亿还不够用?

这地方我一般不太信这个判断。因为自增 ID 不是“真实业务数据量”。

插入失败会消耗 ID。

事务回滚会消耗 ID。

唯一键冲突可能也会把自增值往前推。

批量导入、历史迁移、压测环境复制数据,都可能让这个值跑得比你想象快。

等 AUTO_INCREMENT 的下一个值超过字段类型能存的最大值,MySQL 不会帮你绕回 1,也不会自动找被删除的空洞继续用。

它会直接让插入失败。

原来的数据还在,查询也正常,只有需要生成新自增值的 insert 开始炸。

这也是它麻烦的地方。不是数据库挂了,是某张表突然不能写了。

我以前排这种问题,第一条 SQL 基本就是这个:

SELECT 
    table_schema,
    table_name,
    auto_increment
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name = 't_order';

再查字段类型:

SELECT 
    column_name,
    column_type,
    extra
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND table_name = 't_order'
  AND extra LIKE '%auto_increment%';

如果看到:

column_type: int
auto_increment: 2147483601

那就不用怀疑业务了。

它快顶到头了。

Java 里最好不要等炸了才知道。这个检查我一般会放到一个后台巡检任务里,不用太复杂,能提前报警就行。

@Component
public class MysqlAutoIncrementWatcher {

    private final JdbcTemplate jdbcTemplate;

    public MysqlAutoIncrementWatcher(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Scheduled(cron = "0 10 * * * ?")
    public void scan() {
        String sql = """
            SELECT t.table_name,
                   c.column_name,
                   c.column_type,
                   t.auto_increment
            FROM information_schema.tables t
            JOIN information_schema.columns c
              ON t.table_schema = c.table_schema
             AND t.table_name = c.table_name
            WHERE t.table_schema = DATABASE()
              AND c.extra LIKE '%auto_increment%'
              AND t.auto_increment IS NOT NULL
            "
"";

        jdbcTemplate.query(sql, rs -> {
            String table = rs.getString("table_name");
            String column = rs.getString("column_name");
            String columnType = rs.getString("column_type");
            long nextValue = rs.getLong("auto_increment");

            long maxValue = maxValueOf(columnType);
            long left = maxValue - nextValue;

            if (left 1_000_000L) {
                System.err.printf(
                    "[DB_AUTO_INC_DANGER] table=%s column=%s type=%s next=%d left=%d%n",
                    table, column, columnType, nextValue, left
                );
            }
        });
    }

    private long maxValueOf(String columnType) {
        String type = columnType.toLowerCase();

        boolean unsigned = type.contains("unsigned");

        if (type.startsWith("tinyint")) {
            return unsigned ? 255L : 127L;
        }
        if (type.startsWith("smallint")) {
            return unsigned ? 65535L : 32767L;
        }
        if (type.startsWith("mediumint")) {
            return unsigned ? 16777215L : 8388607L;
        }
        if (type.startsWith("int")) {
            return unsigned ? 4294967295L : 2147483647L;
        }
        if (type.startsWith("bigint")) {
            return unsigned ? Long.MAX_VALUE : Long.MAX_VALUE;
        }

        return Long.MAX_VALUE;
    }
}

这段代码有个细节:BIGINT UNSIGNED 的最大值其实超过 Java long 正数范围。

但线上巡检一般不需要真的算到那个极限。能识别出 int、int unsigned 这种危险类型,已经够用了。

真到了线上修复,别直接拍脑袋执行:

ALTER TABLE t_order MODIFY id BIGINT NOT NULL AUTO_INCREMENT;

小表可以。

大表不一定。

这条 SQL 可能触发表结构重建,占 IO,拿元数据锁,主从延迟也可能被它拉爆。尤其订单表、流水表、日志表这种一直写的表,直接改字段类型,我看着就不踏实。

比较稳的处理方式是先评估表大小和写入量,再决定是在线 DDL,还是用类似影子表迁移的方式慢慢切。

字段改了,Java 代码也别漏。

这种代码很常见:




    
public class OrderRecord {
    private Integer id;
    private String orderNo;
}

表字段都改成 BIGINT 了,Java 里还留着 Integer,后面一样会在别的地方炸。应该改成:

public class OrderRecord {
    private Long id;
    private String orderNo;
}

还有一种临时“妙招”:把 AUTO_INCREMENT 调小。

ALTER TABLE t_order AUTO_INCREMENT = 1;

这个东西在生产上基本别碰。

如果表里已经有更大的 ID,MySQL 不会老老实实从 1 开始让你覆盖插入。并且就算某些场景能重置,也会把主键冲突、关联关系、历史数据查找全搅乱。

删除老数据也不是解法。

DELETE FROM t_order WHERE id 1000000;

删掉了旧行,不代表自增值自动回到前面。

AUTO_INCREMENT 看的是下一个要生成的值,不是看表里还剩多少行。

所以这个问题最靠谱的做法其实很朴素:

建表时,增长型业务主键别省那几个字节。

订单、交易、流水、消息、任务、审计日志,能用 BIGINT 就用 BIGINT。

如果 ID 会暴露给外部系统,最好一开始就考虑清楚,是继续用数据库自增,还是换成业务 ID、雪花 ID、号段模式。

自增 ID 顶满那一刻,MySQL 不会给你留面子。

它只会让新数据写不进去。

而且通常是在你最不想处理数据库 DDL 的时候。

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/197804