☰

MySQL 自动增量字段自行重置

tag-icon • tag-icon mysql autoincrement
MySQL 自动增量字段自行重置

我们有一张 MySQL 表，其中有一个设置为 INT (11) 的自动递增字段。该表基本上存储了应用程序中正在运行的作业列表。在应用程序生命周期的任何给定时刻，该表可能包含数千个条目，也可能完全为空（即所有操作都已完成）。

该字段不是任何其他字段的外键。

自动增量似乎会随机重置为零，尽管我们实际上从未能够捕获重置。

问题变得明显，因为我们看到自动增量字段达到了 600,000 条记录左右，而过了一会儿，自动增量字段似乎在 1000 多条记录左右运行。

如果表是空的，自动增量几乎会自行重置。

这可能吗？如果可以，我该如何关闭它或更改其重置方式？

如果不是，有人可以解释为什么它会这样做吗？

谢谢！

答案1

自动增量计数器仅存储在主存储器中，而不存储在磁盘上。

http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html

因此，当服务（或服务器）重新启动时，会发生以下情况：

服务器启动后，第一次向表 t 插入数据时，InnoDB 会执行与该语句等效的语句：SELECT MAX(ai_col) FROM t FOR UPDATE;

InnoDB 将语句检索到的值加一，并将其​​分配给列和表的自动递增计数器。如果表为空，InnoDB 将使用值 1。

简而言之，MySQL 服务启动后，它不知道表的自动增量值应该是多少。因此，当您第一次插入一行时，它会找到使用自动增量的字段的最大值，将 1 加到该值，然后使用结果值。如果没有行，它将从 1 开始。

这对我们来说是个问题，因为我们使用表和 mysql 的自动递增功能在多线程环境中巧妙地管理 ID，而用户会被重定向到第三方支付网站。因此，我们必须确保第三方获得并发回给我们的 ID 是唯一的，并且会保持这种状态（当然，用户在被重定向后可能会取消交易）。

因此，我们创建一行，获取生成的自动增量值，删除该行以保持表整洁，并将该值转发到支付网站。我们最终采取以下措施来解决 InnoDB 处理 AI 值的方式问题：

$query = "INSERT INTO transactions_counter () VALUES ();";
mysql_query($query);
$transactionId = mysql_insert_id();
$previousId = $transactionId - 1;
$query = "DELETE FROM transactions_counter WHERE transactionId='$previousId';";
mysql_query($query);

这样始终会将最新生成的交易 ID 作为表中的一行保存，而不会不必要地炸毁表格。

希望这可以帮助其他可能遇到此问题的人。

编辑（2018-04-18）：

正如 Finesse 下面提到的那样，这种行为似乎已在 MySQL 8.0+ 中进行了修改。

https://dev.mysql.com/worklog/task/?id=6204

该工作日志中的措辞充其量是有错误的，但是看起来新版本中的 InnoDB 现在支持重启后持久的自动增加值。

—格雷米奥

答案2

我们遇到过这个问题，并且发现当对空表运行优化表时，自动增量值也会被重置。请参阅此 MySQL 错误报告。

作为一种解决方法，您可以执行以下操作：

ALTER TABLE a AUTO_INCREMENT=3 ENGINE=innoDB;

代替OPTIMIZE TABLE。

看来这就是 MySQL 内部所做的事情（显然没有设置自动增量值）

答案3

这只是猜测 - 如果应用程序TRUNCATE TABLE在处理完成后使用清空表，这将重置自动增量字段。以下是简要讨论关于这个问题。虽然该链接提到 InnoDB 不会在 trunc 上重置 auto_increments，但据报道这是漏洞并在几年前就已修复。

假设我的猜测是正确的，您可以从截断改为删除来解决问题。

答案4

更改表表名 ENGINE=MyISAM

对我来说很管用。我们的表总是保持很小，所以不需要 InnoDB。

相关内容