database 当整数列上的 auto_increment 达到数据库中的 max_value 时会发生什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2615417/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What happens when auto_increment on integer column reaches the max_value in databases?
提问by Jonas
I am implementing a database application and I will use both JavaDB and MySQL as database. I have an ID column in my tables that has integer as type and I use the databases auto_increment-function for the value.
我正在实现一个数据库应用程序，我将同时使用 JavaDB 和 MySQL 作为数据库。我的表中有一个 ID 列，它的类型为整数，我使用数据库 auto_increment-function 作为值。
But what happens when I get more than 2 (or 4) billion posts and integer is not enough? Is the integer overflowed and continues or is an exception thrown that I can handle?
但是当我收到超过 2（或 4）0 亿个帖子并且整数不够时会发生什么？整数是溢出并继续还是抛出了我可以处理的异常？
Yes, I could change to long as datatype, but how do I check when that is needed? And I think there is problem with getting the last_inserted_id()-functions if I use long as datatype for the ID-column.
是的，我可以更改为 long as 数据类型，但是如何检查何时需要？而且我认为如果我使用 long 作为 ID 列的数据类型，则获取 last_inserted_id() 函数会出现问题。
采纳答案by outis
Jim Martin's comment from §3.6.9. "Using AUTO_INCREMENT"of the MySQL documentation:
Jim Martin 来自§3.6.9的评论。MySQL 文档的“使用 AUTO_INCREMENT”：
Just in case there's any question, the AUTO_INCREMENT field /DOES NOT WRAP/. Once you hit the limit for the field size, INSERTs generate an error. (As per Jeremy Cole)
以防万一有任何问题，AUTO_INCREMENT 字段 /DOES NOT WRAP/。一旦达到字段大小的限制，INSERT 就会产生错误。（根据杰里米科尔）
A quick test with MySQL 5.1.45 results in an error of:
使用 MySQL 5.1.45 进行快速测试会导致错误：
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
ERROR 1467 (HY000)：无法从存储引擎读取自动增量值
You could test for that error on insert and take appropriate action.
您可以在插入时测试该错误并采取适当的措施。
回答by Justin
Just to calm the nerves, consider this:
只是为了镇定神经，考虑一下：
Suppose you have a database that inserts a new value for every time a user executes some sort of transaction on your website.
假设您有一个数据库，每次用户在您的网站上执行某种交易时都会插入一个新值。
With a 64 bit integer as an ID then this is the condition for overflow: With a world population of 6 billion then if every human on earth executes a transaction once per second every day and every year (without rest) it would take more than 80 years for your id to wrap around.
以 64 位整数作为 ID，这就是溢出的条件：世界人口为 60 亿，那么如果地球上的每个人每天和每年（没有休息）每秒执行一次交易，则需要超过 80几年让你的 id 环绕。
Ie, only google needs to vaguely consider this problem occasionally during a coffee break.
即，只有 google 需要在咖啡休息期间偶尔模糊地考虑这个问题。
回答by Matti Virkkunen
You will know when it's going to overflow by looking at the largest ID. You should change it well before any exception even comes close to being thrown.
通过查看最大的 ID，您将知道它何时会溢出。你应该在任何异常接近被抛出之前改变它。
In fact, you should design with a large enough datatype to begin with. Your database performance is not going to suffer even if you use a 64 bit ID from the beginning.
事实上，您应该使用足够大的数据类型开始设计。即使您从一开始就使用 64 位 ID，您的数据库性能也不会受到影响。
回答by CSTobey
The answers here state what happens, but only one answer says how to detect the problem (and then only after the error has happened). Generally, it is helpful to be able to detect these things before they become a production issue, so I wrote a query to detect when an overflow is about to happen:
此处的答案说明了会发生什么，但只有一个答案说明了如何检测问题（然后仅在错误发生之后）。通常，能够在它们成为生产问题之前检测到这些东西是有帮助的，因此我编写了一个查询来检测何时即将发生溢出：
SELECT
c.TABLE_CATALOG,
c.TABLE_SCHEMA,
c.TABLE_NAME,
c.COLUMN_NAME
FROM information_schema.COLUMNS AS c
JOIN information_schema.TABLES AS t USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
WHERE c.EXTRA LIKE '%auto_increment%'
AND t.AUTO_INCREMENT / CASE c.DATA_TYPE
WHEN 'TINYINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 255, 127)
WHEN 'SMALLINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 65535, 32767)
WHEN 'MEDIUMINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 16777215, 8388607)
WHEN 'INT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 4294967295, 2147483647)
WHEN 'BIGINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', '18446744073709551615', 9223372036854775807) # need to quote because column type defaults to unsigned.
ELSE 0
END > .9; # 10% buffer
Hope this helps someone somewhere.
希望这可以帮助某人某处。
回答by Jingguo Yao
For MySQL 5.6 , 3.6.9 Using AUTO_INCREMENTin says:
对于 MySQL 5.6 ，3.6.9 Using AUTO_INCREMENTin 说：
Use the smallest integer data type for the AUTO_INCREMENT column that is large enough to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails.
对 AUTO_INCREMENT 列使用最小的整数数据类型，该类型足够大以容纳您需要的最大序列值。当列达到数据类型的上限时，下一次尝试生成序列号失败。
回答by Samuel Casimiro
I would like to share a personal experience I just had about this. Using Nagios + Check_MK + NDOUtils. NDOUtils stores all the checks in a table called nagios_servicechecks. The primary key is a auto_increment int signed. What happens with MySQL when this limit is ranged? Well, in my case, MySQL delete all the records but the last one. The table is now almost empty. Everytime a new record is inserted the old one is deleted. Don't why this happen, but the fact is that I lost all my records. IDOUtils, used with Icinga (not Nagios), fixed this issue changing int by a bigint. It didn't generate a error.
我想分享一下我刚刚在这方面的个人经历。使用 Nagios + Check_MK + NDOUtils。NDOUtils 将所有检查存储在名为 nagios_servicechecks 的表中。主键是一个 auto_increment int 签名。当这个限制在范围内时，MySQL 会发生什么？好吧，就我而言，MySQL 删除了除最后一条之外的所有记录。桌子现在几乎是空的。每次插入新记录时，都会删除旧记录。不要问为什么会发生这种情况，但事实是我丢失了所有记录。IDOUtils，与 Icinga（不是 Nagios）一起使用，修复了这个问题，通过 bigint 改变 int。它没有产生错误。
