在数据库中，当整数列的auto_increment达到最大值时会发生什么？

Question

我正在实现一个数据库应用程序，将使用JavaDB和MySQL作为数据库。我的表中有一个ID列，类型为整数，并且我使用数据库的auto_increment函数来生成该值。

但是，当我拥有超过20亿（或40亿）个帖子时，整数会发生溢出并继续增长吗？还是会抛出异常，以便我可以处理？

是的，我可以将数据类型更改为long，但我怎样才能知道何时需要进行更改呢？如果我将ID列的数据类型更改为long，可能会出现获取last_inserted_id()函数的问题。

- Jonas

Answer 1

来自 MySQL 文档 §3.6.9. "使用 AUTO_INCREMENT" 的 Jim Martin 评论：

以防万一，AUTO_INCREMENT 字段 /不会循环/. 一旦达到字段大小的极限，插入操作将生成错误。（根据 Jeremy Cole）

使用 MySQL 5.1.45 进行快速测试会导致以下错误：

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

您可以在插入时测试该错误并采取适当的措施。

- outis

---

根据我的测试结果，每次达到限制后，产生的递增数是相同的（最大值）。我并没有像预期那样收到SQL错误信息。 - Victor

Answer 2

为了平静神经，考虑以下情况：

假设您有一个数据库，在用户在您的网站上执行某些事务时插入新值。

如果使用64位整数作为ID，则溢出条件如下： 如果世界人口为60亿，那么如果地球上的每个人每秒钟每天和每年都执行一次交易（不休息），则需要超过80年才能使您的ID回到原点。

也就是说，只有谷歌需要在咖啡时间偶尔考虑这个问题。

- Justin

---

抱歉，为了准确起见，需要一些超过9年的时间 :) 1分钟后将发生3600亿个交易。1小时后，将有21600亿个交易。1天内，将验证5184000亿个交易。1年内：1892160000亿个。8年后，数据库中将保存15,137,280,000亿个交易。UNSIGNED BIGINT的限制是18,446,744,000亿。 - tobia.zanarella

你将额外获得一年的0.1，因此总共大约是97年。 - lockedscope

如果我有2000个代理进行插入攻击并启动多个线程/请求怎么办？或者例如，我有一个历史上传功能（有60,000条记录），因此上传该历史记录将需要更少的请求。 - Anestis Kivranoglou

对我来说，int(10) unsigned，id列在短短2个月内就已经达到了237836414，占用了uint_max的5.54％。所以，这是一个问题。 - majidarif

@Majidarif，如果你使用BigInt，那么要使用所有可能的值就需要额外花费77560638270个月，或者大约65亿年左右，再加上几百万年。 - Franck

Answer 3

您可以通过查看最大ID来判断何时会发生溢出。您应该在任何异常甚至靠近抛出之前及时更改它。

实际上，您应该从一开始就使用足够大的数据类型进行设计。即使您从一开始就使用64位ID，您的数据库性能也不会受到影响。

- Matti Virkkunen

Answer 4

这里的答案阐述了会发生什么，但是只有一个答案说出如何检测问题（而且只有在错误发生后才能检测出来）。通常，在它们成为生产问题之前能够检测到这些问题是很有帮助的，因此我编写了一个查询以检测溢出即将发生的情况：

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

希望这能帮到某个地方的人。

- CSTobey

Answer 5

在MySQL 5.6中，3.6.9 使用AUTO_INCREMENT中提到：

使用足够大以容纳所需最大序列值的最小整数数据类型作为AUTO_INCREMENT列。当该列达到数据类型的上限时，下一次尝试生成序列号将失败。

- Jingguo Yao

Answer 6

我想分享一下我刚刚的个人经验，涉及到Nagios + Check_MK + NDOUtils。NDOUtils把所有的检查都存储在一个叫做nagios_servicechecks的表里。主键是一个自增的int signed类型。当MySQL达到这个限制时会发生什么？在我的情况下，MySQL删除了除最后一条记录以外的所有记录。现在这张表几乎是空的。每次插入新纪录时旧纪录就会被删除。不知道为什么会发生这种情况，但事实上我丢失了所有的记录。IDOUtils与Icinga（而非Nagios）一起使用，通过将int改为bigint来解决了这个问题。它没有产生错误。

- Samuel Casimiro