MySQL自动增量ID突然跳到了MAXINT，这可能是什么原因? [英] MySQL auto increment ID suddenly jumped to MAXINT, what could be a reason?

问题描述

我有一个大约2,000,000个条目的mysql表，带有一个自动递增的主键.但是，在某一时刻，自动增量值突然从原来的值(大约2,000,000)跃升至最大整数值(2,147,483,647).不用说，此表中的所有后续插入均失败.当我注意到它时，我将主键类型从int更改为bigint，因此它现在可以正常工作.

I have a mysql table with about 2,000,000 entries, with a primary key which is auto incrementing. However, at one point the auto increment value suddenly jumped from what it was (around 2,000,000) to maximum integer value (2,147,483,647). Needless to say all subsequent insertions into this table failed. When I noticed it, I changed primary key type from int to bigint and so it works fine for now.

所以我的问题是，为什么会发生这种情况?而且我有什么预防措施可以避免将来发生这种情况?

So my question is, why could this have happened? And are there any precautions which I could take to avoid this from happening in the future?

推荐答案

插入失败仍然会导致自动增量列增加.如果您的程序陷入失败的无限循环，则可能导致达到极限.

A failed insert can still cause the auto-increment column to increase. If your program went into an infinite loop of failures it could cause the limit to be reached.

还可以通过编程将自动增量设置为特定值.

It's also possible to set the auto-increment programmatically to a specific value.

ALTER TABLE yourtable AUTO_INCREMENT = 12345;

这篇关于MySQL自动增量ID突然跳到了MAXINT，这可能是什么原因?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！