当整数列上的auto_increment到达数据库中的max_value时，会发生什么情况?

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列，它的类型为integer，我对值使用数据库auto_increment-函数。

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?

但是当我收到超过20亿(或40亿)的帖子和整数不够的时候会发生什么呢?是整数溢出并继续，还是抛出一个我可以处理的异常?

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 datatype，但是我如何检查何时需要它?我认为，如果我使用long作为id列的数据类型，那么得到last_inserted_id()函数是有问题的。

6 个解决方案

#1

Jim Martin's comment from §3.6.9. "Using AUTO_INCREMENT" of the MySQL documentation:

吉姆·马丁的评论§3.6.9。“使用AUTO_INCREMENT”MySQL文档:

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字段/没有包装/。一旦您达到了字段大小的限制，插入将产生一个错误。(按杰里米·科尔)

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

错误1467 (HY000):无法读取存储引擎的自动增量值。

You could test for that error on insert and take appropriate action.

您可以在insert中测试该错误，并采取适当的操作。

#2

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年环绕你的身份证。

Ie, only google needs to vaguely consider this problem occasionally during a coffee break.

也就是说，只有谷歌需要偶尔在喝咖啡的时候模糊地考虑这个问题。

#3

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，您的数据库性能也不会受到影响。

#4

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.

希望这能帮到一个人。

#5

For MySQL 5.6 , 3.6.9 Using AUTO_INCREMENT in says:

对于MySQL 5.6, 3.6.9使用AUTO_INCREMENT in表示:

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列使用最小的整数数据类型，该列足够大以容纳所需的最大序列值。当列达到数据类型的上限时，下一次生成序列号的尝试失败。

#6

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_servicecheck的表中。主键是签名的auto_increment。当这个限制在范围内时，MySQL会发生什么?在我的例子中，MySQL删除了除最后一条之外的所有记录。桌子现在几乎空了。每次插入新记录时，删除旧记录。不知道为什么会这样，但事实是我把所有的唱片都弄丢了。与Icinga(不是Nagios)一起使用的IDOUtils用bigint修正了这个问题。它没有产生错误。

#1